Apache OpenOffice Community Forum

Posted: **Mon Sep 16, 2019 4:14 pm**

I've written a program in Autohotkey, that opens a blank spreadsheet, then fills cells with content. (and it works)
Now I want to save this spreadsheet to a name that exists in some cells with the same program.
The path => C3 (c:\temp) the filename => C4 (Test.ods)
I tried with this code

Code: Select all

sPath := oDoc.getSheets.getByName(SheetName).getCellRangeByName("C3").getString
sFileName := oDoc.getSheets.getByName(SheetName).getCellRangeByName("C4").getString

It seams to work, but when I want to use

Code: Select all

sSaveToURL := ConvertToURL(sPath & sFileName)

I got an error

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

Skipped the conversion and did the following steps .:

Code: Select all

sSaveToURL := "file:///c:/temp/Test.ods"	; Windows "c:\Temp\Test.ods"
oDoc.storeToUrl(sSaveToURL, Array(MakePropertyValue(oSM, "FilterName", "Calc8")))

or

Code: Select all

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

Both tests give the same error

Error: 0x80020005 - Typmatchningsfel.
Specifically: storeToURL

I don't know if "FilterName" and "Calc8" are correct (I have no idea what else can be in this fields)

How to save the unnamed spreadsheet to the hard drive?

Posted: **Mon Sep 16, 2019 4:21 pm**

You need a / or \ character between the path string and the file name string. Or you need
The path => C3 (c:\temp\)
instead of the
The path => C3 (c:\temp)
string.

Posted: **Mon Sep 16, 2019 4:28 pm**

Please upload your full macro code.

How you get the new, unnamed document (oDoc) by your code? By usage the Thiscomponent? Or the code will create a new empty document?

Posted: **Mon Sep 16, 2019 4:57 pm**

My test script in AutohotKey language .:

Code: Select all

; #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


; Rename the TAB
oSheet := oSheets.getByIndex(0)
oSheet.Name := "Test"

FilePath = c:\temp
FileName = Test.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

; ------------------------------------------------------------------------------------
; ------------------------------------------------------------------------------------
; Save the spreadsheet from the name in cell C3 - TestFile.ods (directory eg. C:\temp)

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)

sSaveToURL := "file:///c:/temp/Test.ods"	; Windows "c:\Temp\TestFile.ods"
; oDoc.storeToUrl(sSaveToURL, Array(MakePropertyValue(oSM, "FilterName", "Calc8")))
; oDoc.storeToURL("file:///c:/temp/Test.ods", Array())

; ------------------------------------------------------------------------------------
; ------------------------------------------------------------------------------------
MsgBox ,,, Ready!, 1
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
}

Posted: **Mon Sep 16, 2019 9:55 pm**

AutohotKey language

What is it???

Posted: **Mon Sep 16, 2019 10:02 pm**

I don't know Autohotkey but I presume that ConvertToURL is being treated as a Autohotkey function not an OO one.

All I can suggest with storeToURL is to take the simplest case, your

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

but declare the Array properly as a PropertyValue array - without the complication of an external function.

Posted: **Mon Sep 16, 2019 11:49 pm**

Zizi64 wrote:
AutohotKey language
What is it???

The ultimate automation scripting language for Windows Autohotkey
(A scripting language as approaching a programming language in functionality)

Posted: **Tue Sep 17, 2019 12:26 am**

Thanks!

JeJe wrote:I don't know Autohotkey but I presume that ConvertToURL is being treated as a Autohotkey function not an OO one.

Andrew Pitonyak use it in his dokumentation "OpenOffice.org Macros Explained"
13.4.1. Document properties from a closed document
It is easy to access the document properties from a document that is not open.
Listing 6. Read the document properties from a document that is not open.like this .:

Code: Select all

Sub loadExternalProperties
  Dim sPath$
  Dim sPathUrl
  Dim oDocProps
  sPath = ConvertToUrl("/andrew0/home/andy/MoveFigsFromFrames.odt")
  oDocProps = CreateUnoService("com.sun.star.document.DocumentProperties")
  oDocProps.loadFromMedium(sPath, Array())
  printDocProperties(oDocProps)
End Sub

In chapter 13:11 Saving a document he say .:

OOo Basic supports the functions ConvertToURL and ConvertFromURL to convert between the two notations. The XStorable interface defines object methods to save a document to a URL (see Table 8).

One way to solve ConvertToURL in AutohotKey is to use an instruction like this .:

Code: Select all

FileNameURL := "File:///" StrReplace(StrReplace(File, "\", "/",, Limit := -1), " ", "%20",, Limit := -1)

(I think you can do the same with RegExReplace)
~~- - - - - - - - -~~

JeJe wrote:All I can suggest with storeToURL is to take the simplest case, your
oDoc.storeToURL("file:///c:/temp/Test.ods", Array())...

Finally I managed to save an open spreadsheet with AHK (AutohotKey)

Code: Select all

FileNameOUT := "c:\temp\Test.ods"
sSaveToURL := "File:///" StrReplace(StrReplace(FileNameOut, "\", "/",, Limit := -1), " ", "%20",, Limit := -1)
oDoc.storeAsURL(sSaveToURL, Array)

But may have more questions now than before I started trying different suggestions.
like .: Whats the differ between storeAsURL(work) and storeToURL(doesn't work)
Why does this not work?

Code: Select all

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

is the Array another array than? .:

Code: Select all

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

How to get information about "FilterName" and "Calc8"?

Posted: **Tue Sep 17, 2019 1:53 am**

The filterlist is here:

https://wiki.openoffice.org/wiki/Framew ... st_OOo_3_0

xstorable:

https://www.openoffice.org/api/docs/com ... rable.html

Edit:

Yes OOBasic's Array() is a variant array too:

https://wiki.openoffice.org/wiki/Docume ... ide/Arrays

Posted: **Tue Sep 17, 2019 7:04 am**

I don't know Autohotkey but I presume that ConvertToURL is being treated as a Autohotkey function not an OO one.
Andrew Pitonyak use it in his dokumentation "OpenOffice.org Macros Explained"

Yes, but those examples can run in the StarBasic IDE, and they are not called from a third party environment like the Autohotkey...
The ConvertToURL() API function works for me fine - called from a Basic function/sub launched in the StarBasic IDE.

("StarBasic" is the original name of the Basic language and IDE of the OpenOffice.org, Oracle OpenOffice, Apache OpenOffice, LibreOffice)

Posted: **Wed Sep 25, 2019 9:08 pm**

Thanks!
It wasn't as difficult as I thought when I got all the parts in place.

For information, I send with a solution that works (for me)
This program does the following (Language: Autohotkey):

Open a new spreadsheet.
Give the first tab a new name (Test)
Change the width on column C
The desired file name is written into two cells
The file name and path is write / read from these cells
The spreadsheet is saved with this file name

(with some error handling

)

Code: Select all

; #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 CreateNewCalc	; Create a NEW spreadsheet


; Rename the TAB
oSheet := oSheets.getByIndex(0)
oSheet.Name := "Test"
; SheetName := oSheets.getByIndex(0).Name

FilePath = c:\temp
FileName = Test.ods

; Adjust the column width
Column = C
oColumns := oSheet.getColumns()
oColumn := oColumns.getByName( Column )	; 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

; ------------------------------------------------------------------------------------
; ------------------------------------------------------------------------------------
; Save the spreadsheet from the name in cell C3 - TestFile.ods (directory eg. C:\temp)


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

If !sPath	; If path is missing - add ScriptDir path.
	sPath := A_ScriptDir
If !( SubStr(sPath, StrLen(sPath)-1, 1) = "\" )	; If "\" is missing after path - add "\"
	sPath := sPath "\"

If !sFileName	; If filename is missing
{	; Close the spreadsheet (don't save)
	oDoc.Close(True)
	oDoc := ""
	MsgBox 16, Row %A_LineNumber% -> %A_ScriptName%, Filename is missing! `n`nThis program ends!
	ExitApp
}
	
FileNameOut := sPath sFileName	; create the filename
If FileExist( FileNameOut ) ; If the resultfile exist - try to remove
{	FileDelete %FileNameOut%
	If ErrorLevel
	{	SplitPath FileNameOut, OutFileName, OutDir, OutExtension, OutNameNoExt, OutDrive
		MsgBox 64, Row %A_LineNumber% -> %A_ScriptName%,
		( LTrim Join
		%	"The file exist, and could not be removed! `n`n
			Filename .: " OutFileName "`n
			Path .: "OutDir "`n`n
			(maybe is open with another program?)
			This program ends! `n`n"
		)
		
		; Close the spreadsheet (don't save)
		oDoc.Close(True)
		oDoc := ""
		MsgBox ,,, Program ends!, 1
		ExitApp
	}
}

; sSaveToURL := FileURL( FileNameOut )
; MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, % "sPath .: " sPath "`nsFileName .: " sFileName "`n`n sSaveToURL .: " sSaveToURL
; oDoc.storeAsURL(sSaveToURL, Array)
oDoc.storeAsURL(FileURL(FileNameOut), Array)

MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%,
( LTrim
	- A new spreadsheet has been opened.
	- The first tab has been given a new name (%SheetName%)
	- Column %Column% has given a different width.
	- The desired file name has been entered into two cells
	- The file name is read from these cells
	- The spreadsheet has been saved with this file name
	
	(the spreadsheet closes after "OK")
)


; Close the created spreadsheet
oDoc.Close(True)
oDoc := ""

MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, Ready!, 1
ExitApp



; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CreateNewCalc:
	; 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


; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
; Version .: 9 sept 2019
MakePropertyValue(oSM, cName, uValue)
{	; From .: Function MakePropertyValue(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
}


; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
; Version .: 9 dec 2018
FileURL( File )
; FileURL() Converts filepath to file URI - By SKAN on D1CA @ goo.gl/UqXL6n
; https://www.autohotkey.com/boards/viewtopic.php?f=6&t=59805
; 
{	Local v, INTERNET_MAX_URL_LENGTH := 2048   
	VarSetCapacity(v,4200,0)
	DllCall( "Shlwapi.dll" ( SubStr(File,1,5)="file:" ? "\PathCreateFromUrl" : "\UrlCreateFromPath" )
			, "Str",File, "Str",v, "UIntP",INTERNET_MAX_URL_LENGTH, "UInt",0 )
	Return v
}


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

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

Apache OpenOffice Community Forum

[Solved] Save a ods spreadsheet with API - complicated?

[Solved] Save a ods spreadsheet with API - complicated?

Re: Save a ods spreadsheet with API - complicated?

Re: Save a ods spreadsheet with API - complicated?

Re: Save a ods spreadsheet with API - complicated?

Re: Save a ods spreadsheet with API - complicated?

Re: Save a ods spreadsheet with API - complicated?

Re: Save a ods spreadsheet with API - complicated?

Re: Save a ods spreadsheet with API - complicated?

Re: Save a ods spreadsheet with API - complicated?

Re: Save a ods spreadsheet with API - complicated?

Re: Save a ods spreadsheet with API - complicated?