Need help in SaveAs VBScript for OpenOffice

Creating a macro - Writing a Script - Using the API

Need help in SaveAs VBScript for OpenOffice

Postby amanshow » Thu Mar 14, 2019 1:33 pm

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   Expand viewCollapse view
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
amanshow
 
Posts: 2
Joined: Thu Mar 14, 2019 11:53 am

Re: Need help in SaveAs VBScript for OpenOffice

Postby Villeroy » Thu Mar 14, 2019 7:07 pm

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   Expand viewCollapse view
args = Array()
Call ObjFile.StoreToURL("file:///c:/sample1.ods", args)

or
Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26855
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need help in SaveAs VBScript for OpenOffice

Postby JeJe » Thu Mar 14, 2019 7:22 pm

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
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 518
Joined: Wed Mar 09, 2016 2:40 pm

Re: Need help in SaveAs VBScript for OpenOffice

Postby amanshow » Fri Mar 15, 2019 3:47 am

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   Expand viewCollapse view
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   Expand viewCollapse view
.~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   Expand viewCollapse view
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   Expand viewCollapse view
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
amanshow
 
Posts: 2
Joined: Thu Mar 14, 2019 11:53 am

Re: Need help in SaveAs VBScript for OpenOffice

Postby JeJe » Fri Mar 15, 2019 11:34 am

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   Expand viewCollapse view
Dir("C:\tmp\.~lock.Untitled 10.odt#", &H2)
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 518
Joined: Wed Mar 09, 2016 2:40 pm

Re: Need help in SaveAs VBScript for OpenOffice

Postby Villeroy » Fri Mar 15, 2019 11:46 am

In StarBasic
Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26855
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need help in SaveAs VBScript for OpenOffice

Postby JeJe » Fri Mar 15, 2019 12:07 pm

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.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 518
Joined: Wed Mar 09, 2016 2:40 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 0 guests