Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Save a Spreadsheet - use API

I have an AHK program that creates a spreadsheet. (and it works for me) Now I want to save it - but don't know how.

; #Warn   ; Enable warnings to assist with detecting common errors.
SetBatchLines -1
;#SingleInstance   force
;#NoEnv
SendMode Input ; Recommended for new scripts due to its superior speed and reliability. 
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.

Gosub OpenNewCalc   ; Create a NEW spreadsheet

oSheet := oSheets.getByIndex(0)
oSheet.Name := "Test"

FilePath = c:\temp
FileName = TestFile.ods

; Adjust the column width
oColumns := oSheet.getColumns()
oColumn := oColumns.getByName( "C" )    ; Get a specific column.
oColumn.Width := 3000 ; 3 cm / 30mm - Change width of column.

; - - - - - - - - - - - - - - - - - - - - - - - - - 
oCell := oSheet.getCellRangeByName( "B3" )
oCell.setString( "Path .:" )
oCell.CharFontName := Arial
oCell.CharHeight := "10"

oCell := oSheet.getCellRangeByName( "C3" )
oCell.setString( FilePath )
oCell.CharFontName := "arial-rounded-mt-bold"
oCell.HoriJustify := 2
oCell.CharHeight := "12"

; - - - - - - - - - - - - - - - - - - - - - - - - - 
oCell := oSheet.getCellRangeByName( "B4" )
oCell.setString( "Name .:" )
oCell.CharFontName := Arial
oCell.CharHeight := "10"

oCell := oSheet.getCellRangeByName( "C4" )
oCell.setString( FileName )
oCell.CharFontName := "arial-rounded-mt-bold"
oCell.HoriJustify := 2
oCell.CharHeight := "12"
; = = = = = = = = Ready with the spreadsheet

; How to save this?

ExitApp




; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
OpenNewCalc:
    ; Create the structure so that I know the values are all zero.
    ; Init - CreateUnoService - initialize COM object in AHK?
    oSM := ComObjCreate("com.sun.star.ServiceManager")              ; This line is mandatory with AHK for OOo API
    oDesk := oSM.createInstance("com.sun.star.frame.Desktop")   ; Create the first and most important service

    Array := ComObjArray(VT_VARIANT:=12, 2)
    Array[1] := MakePropertyValue(oSM, "hidden", ComObject(0xB,true))
    sURL := "private:factory/scalc"
    oDoc := oDesk.loadComponentFromURL(sURL, "_blank", 0, Array)

    ; https://www.openoffice.org/api/docs/common/ref/com/sun/star/table/BorderLine.html
    oBorder := oSM.Bridge_GetStruct("com.sun.star.table.BorderLine")

    ; Name on the first tab
    oSheets := oDoc.getSheets()
    SheetName := oSheets.getByIndex(0).Name

    oFormats := oDoc.getNumberFormats() ; Set formatting for dates and currency
    oLocale := oSM.Bridge_GetStruct("com.sun.star.lang.Locale") ;  Same as createUnoStruct( "com.sun.star.lang.Locale" )

    ; http://www.chemie.fu-berlin.de/diverse/doc/ISO_3166.html
    ; https://docs.oracle.com/cd/E13214_01/wli/docs92/xref/xqisocodes.html
    ; SWEDEN SE SWE 752
    ; oLocale.Language := "SWE"
    ; oLocale.Country := "SE"
    oLocale.Language := "sv"
    oLocale.Country := "SE"
Return

; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
; Function MakePropertyValue(cName, uValue)
; Ver 9 sept 2019
MakePropertyValue(oSM, cName, uValue)
{   ; oSM - Defined in Subroutine  OpenNewCalc/CreateCalc
    ; Like this .: oSM := ComObjCreate("com.sun.star.ServiceManager")

    oPropertyValue := oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")

    If cName
        oPropertyValue.Name := cName

    If uValue
        oPropertyValue.Value := uValue

    Return oPropertyValue
}

; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ESC::
{   ; Close the spreadsheet (don't save)
    oDoc.Close(True)
    oDoc := ""

    MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, ESC - Program ends!, 1
    ExitApp
}

First, I thought to save with content from cells C3 and C4 as follows .:

SheetName := oSheets.getByIndex(0).Name
sPath := oDoc.getSheets.getByName(SheetName).getCellRangeByName("C3").getString
sFileName := oDoc.getSheets.getByName(SheetName).getCellRangeByName("C4").getString
sSaveToURL := ConvertToURL(sPath & sFileName)

But I got an Error .:

Error: Call to nonexistent function. Specifically: ConvertToURL(sPath & sFileName)

Then I tried to enter the path and file name (Hope I have the correct structure?) like this .:

sSaveToURL := "file:///c:/temp/TestFile.txt"

To save the Spreadsheet I have tried .:

oDoc.storeToURL("file:///c:/temp/TestFile.ods", Array())

and

oDoc.storeToUrl(sSaveToURL, Array(MakePropertyValue(oSM, "FilterName", "Calc8")))

(I don't know if the "FilterName" and "Calc8" is correct) But in both cases i got the following error message .:

Error: 0x800405E9 - Source: [automation bridge] Description: [automation bridge] UnoConversionUtilities<t>::dispatchExObject2Sequence Conversion of dispatch object to Sequence failed! HelpFile: (null) HelpContext: 0

Specifically: storeToURL

How can I save an open Spreadsheet to a file with an external program (eg. Autohotkey) If I get any tip in java or VB I can try to translate.- ..