Need help in SaveAs VBScript for OpenOffice

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
amanshow
Posts: 2
Joined: Thu Mar 14, 2019 11:53 am

Need help in SaveAs VBScript for OpenOffice

Post by amanshow »

Good day.

I've searched all over the net for something that could help me but I couldn't find what I was looking for. I'm really new to vbscript coding and I was successful at finding the part where I could use vbscript to open an open office file and then modify it.

However, I badly need help in the SaveAs part. I know SaveAs only works in Excel applications but I'm wondering if there is some sort of equivalent for that for OpenOffice. I found a code (seen below) but it didn't work. The error said
Type mismatch: 'objFile.StoreToURL'
.

Code: Select all

Set oSM = CreateObject("com.sun.star.ServiceManager") 'opening open office file
Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
Set objFile = oDesk.loadComponentFromURL("file:///C:\sample.ods", "_default", 0, array())

objFile.getCellByPosition(0,1).setValue(123456) 'for sample only
objFile.getCellByPosition(6,4).setValue(77777) 'for sample only

Call ObjFile.StoreToURL("file:///c:/sample1.ods", args) 'saving ods file and then closing it after modifications
'Closing the document.
objFile.Close (True)
Set objFile = Nothing
I would really appreciate any help. Thanks.
OpenOffice 4.1.6 || Win7 64-bit
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need help in SaveAs VBScript for OpenOffice

Post by Villeroy »

The args argument has to be an empty array in this case. I don't know if VBScript has an Array function:

Code: Select all

args = Array()
Call ObjFile.StoreToURL("file:///c:/sample1.ods", args)
or

Code: Select all

Call ObjFile.StoreToURL("file:///c:/sample1.ods", Array())
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Need help in SaveAs VBScript for OpenOffice

Post by JeJe »

Does objFile.store work? If so you could copy the original document to your target location before loading and editing it for the same end result.

Edit:
But try to explicitly declare the array of propertyValue in case that's the problem.

dim arr() as new com.sun.star.beans.PropertyValue
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
amanshow
Posts: 2
Joined: Thu Mar 14, 2019 11:53 am

Re: Need help in SaveAs VBScript for OpenOffice

Post by amanshow »

Hello everyone! I would like to say thanks for helping me. I used Villeroy's technique and it worked! But I'm also gonna explore JeJe's answer.
I have a follow up question, if that's okay.
I was looking for a code that would check if the file is open and I found this

Code: Select all

Dim StrPath, StrFile, StrFileLocked
StrPath = "C:\"
StrFile = "sample.ods"
StrFileLocked = StrPath & ".~lock." & StrFile & "#"
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(StrFileLocked) Then
      MsgBox "File locked for editing by another user. Please close the file and try again.", 16
Else
      MsgBox "Opening the file now!"
End If
So I tried to mix it up with the code I posted above, the one I needed help with SaveAs but I found out that when I used it, no

Code: Select all

.~lock.sample.ods#
would appear in C: even if the file was already open.

This is how I did it. I used the code below first to open the file.

Code: Select all

Set oSM = CreateObject("com.sun.star.ServiceManager") 'opening open office file
.
.
.
Set objFile = Nothing

And then this one to check if the file is open.

Code: Select all

Dim StrPath, StrFile, StrFileLocked
StrPath = "C:\"
StrFile = "sample.ods"
StrFileLocked = StrPath & ".~lock." & StrFile & "#"
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(StrFileLocked) Then
      MsgBox "File locked for editing by another user. Please close the file and try again.", 16 'shows up when file is detected to be open
Else
      MsgBox "Opening the file now!" 'shows up when file is not open
End If



The file was open but the second message box appeared instead.

Thanks in advance. I really appreciate any help. Let me know if a follow-up question in this thread is ok or if I have to post a new one. Very much thanks.
OpenOffice 4.1.6 || Win7 64-bit
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Need help in SaveAs VBScript for OpenOffice

Post by JeJe »

This works for me, returning the file path if it exists. I have noticed in the past that that temporary file can sometimes get left behind so the method of looking for it as a test for being opened or not will be unreliable.

Code: Select all

Dir("C:\tmp\.~lock.Untitled 10.odt#", &H2)
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need help in SaveAs VBScript for OpenOffice

Post by Villeroy »

In StarBasic

Code: Select all

doc = StarDesktop.loadComponentFromURL(url, "_default", 0, Array())
returns the document directly if it is already loaded. If required, the document is loaded. If the URL is right, you get the document in any case.

The StarDesktop is some kind of root object that is predefined in StarBasic like ThisComponent. StarDesktop represents the root object of all frames. I don't know how to get hold of it in VBScript but this page about VB and UNO may help: http://www.kalitech.fr/clients/doc/VB_A ... irst_steps

The lock file may be there even when the document is not loaded. This happens when the document had been closed improperly.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Need help in SaveAs VBScript for OpenOffice

Post by JeJe »

You can enumerate the components in OO and see if the URL is loaded.

viewtopic.php?f=45&t=87507

Using the Windows api functions you could do something similar, looking for the wanted window title.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Post Reply