Help converting VBscript

[WFC]

I have a VBscript I am trying to convert to AutoIt. Please excuse the code, I am don't really know VBscript although I have been able to get it to work and it looks nothing like the original anymore. It will convert a spreadsheet (XLS, SCX, ODS) to an HTML table.

expandcollapse popup

Dim StrFname, cURL

If Wscript.Arguments.Count < 1 Then 'this checks that filename is specified 
    Wscript.Echo "usage: spreadsheet <Filename.sxc, .xls, .ods>" 
    Wscript.Quit(1) 
End If 
StrFname = Wscript.Arguments(0) 
'change the path to openoffice url format 
StrFname = Replace(StrFname, ":" , "|") 
StrFname = Replace(StrFname, "\" , "/") 
cURL = "file:///" & Replace(StrFname, " ", "%20")

' main 
Dim objServiceManager 
Dim objDesktop 
Dim args(0) 
Dim oDoc 
Dim oCursor
Dim oStart
Dim oEnd
Dim oRange
Dim oSheet
Dim oData
Dim x, y, row, path
Dim fname

Set objServiceManager= CreateObject("com.sun.star.ServiceManager") 
Set Stardesktop= objServiceManager.createInstance("com.sun.star.frame.Desktop") 
Set args(0) = MakePropertyValue("Hidden", True) 
Set oDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0, args ) 
Set oSheet = oDoc.CurrentController.ActiveSheet
call UsedRange(oSheet)
oData = oRange.getdataarray()
Set filesys = CreateObject("Scripting.FileSystemObject")
fname = Wscript.Arguments(0)
fname = left(fname, len(fname) - 3) & "tbl"
Set filetxt = filesys.CreateTextFile(fname, True)
filetxt.WriteLine("<TABLE COLS=" & oEnd.EndColumn +1 & " BORDER=1>")
For x = 0 To oEnd.EndRow
    row = oData(x)
    temp = "<TR>"
    For y = 0 To oEnd.EndColumn
        temp = temp & "<TD>" & row(y) & "</TD>"
    Next
    filetxt.WriteLine(temp & "</TR>")
Next
filetxt.WriteLine("</TABLE>")
filetxt.Close
call oDoc.close( True )
Set oDoc = nothing 
Set Stardesktop = nothing 
Set objServiceManager = nothing 
Set oData = nothing
wscript.quit 

Function MakePropertyValue(cName, uValue) 
Dim oStruct 
Set oStruct = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue") 
oStruct.Name = cName 
oStruct.Value = uValue 
Set MakePropertyValue = oStruct 
End Function 

Function UsedRange(mSheet)
Set oCursor = mSheet.createCursor()
oCursor.gotoStartOfUsedArea(False)
Set oStart = oCursor.getRangeAddress()
oCursor.gotoEndOfUsedArea(False)
Set oEnd = oCursor.getRangeAddress()
Set oRange = mSheet.getCellRangeByPosition(oStart.EndColumn, oStart.EndRow, oEnd.EndColumn, OEnd.EndRow)
End Function

Here is a part of it in AutoIt:

expandcollapse popup

Func ConvertOOo($fn)
    Local $args, $oDoc, $cURL, $cURL, $oSave, $x, $y, $temp, $row
    
    If $fn = "" Then
        MsgBox(0, "", "No Filename Specified")
        Return
    EndIf
    $temp = StringRight($fn, 3)
    If $temp = "SXC" Or $temp = "XLS" Or $temp = "ODS" Then
    Else
        MsgBox(0, "", "Must be an XLS, SXC or ODS file. Exiting")
        Return
    EndIf
    $ServiceManager  = ObjCreate("com.sun.star.ServiceManager")
    $Desktop  = $ServiceManager.createInstance("com.sun.star.frame.Desktop")
    $cURL = Convert2URL($fn)
    $args = _ArrayCreate(MakePropertyValue("Hidden", Int(-1)))
    $oDoc = $Desktop.loadComponentFromURL( $cURL, "_blank", 0, $args)
    $oSheet=$oDoc.CurrentController.ActiveSheet 
    UsedRange($oSheet)
    $mydata = $Range.getdataarray()
    $temp = StringLen($fn)
    $fn = StringLeft($fn, $temp  -4) & ".tbl"
    $outh = FileOpen($fn, 2)
    FileWriteLine($outh, "<TABLE COLS=" & $oEnd.EndColumn +1 & " BORDER=1>" & @CRLF)
    For $x = 0 To $oEnd.EndRow; for the 1st row through the last row
        $row = $mydata[$x]
        $temp = "<TR>"
        For $y = 0 To $oEnd.EndColumn; first column through the last column
            $temp = $temp & "<TD>" & $row[$y] & "</TD>"; $row[$y] is the cell data
        Next
        FileWriteLine($outh, $temp & "</TR>" & @CRLF); complete row, write it
    Next
    FileWriteLine($outh, "</TABLE>" & @CRLF); write tag
    FileClose($outh) ; and close the file
    $oDoc.close(True); close soffice -- True works here but not for hidden!
EndFunc;=== ConvertOOo

Func MakePropertyValue( $cName, $uValue)
    Local $Pstruc
    $Pstruc = $ServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
    $Pstruc.Name = $cName
    $Pstruc.Value = ($uValue)
    Return $Pstruc
EndFunc;===> MakePropertyValue

While this will run from AutoIt, it won't run hidden. I think it has something to do with $args. In VBscript apparently True = -1. In AutoIt it is 1. I use -1 in my AutoIt routine but it doesn't hide it. The VBscript doesn't appear to this non VBscript programmer to be an array however I have seen other scripts where it is. Using _ArrayCreate is the only way I have been able to get it to work at all. Can one of you AutoIt experts who also knows VBscript help me with this please? Is it really true that Arrays in AutoIt only use string values? So would it really convert -1 to "-1"? I think the MakePropertyValue function works because I use it in another script to pass "filtername", "Text - txt - (StarCalc)" and it works. But there I am not trying to pass a True.

Thanks for any help.

WFC

Edited March 14, 2006 by WFC