Problems saving doc and closing OO in PowerBuilder

Creating a macro - Writing a Script - Using the API

Problems saving doc and closing OO in PowerBuilder

Postby DanCooperstock » Fri Oct 16, 2020 4:06 pm

I'm trying to use OpenOffice through OLE in PowerBuilder to open an Excel doc and save it as CSV.

I'm running into two problems: the SaveAsURL (or SaveToURL) always fails with an OpenOffice IOError, and the OpenOffice application never closes (as seen on Task Manager).

Here's my code (in a function taking asFilename as its argument):

Code: Select all   Expand viewCollapse view
iole_app = CREATE OLEObject
OLEobject desktop, prop, props[], document
string fixedFilename, saveFilename
int pos

if iole_app.ConnectToNewObject("com.sun.star.ServiceManager") <> 0 THEN
    MessageBox('Test', 'Unable to start OpenOffice.',Exclamation!)
    DESTROY iole_app
    return FALSE
end if

desktop = iole_app.createInstance("com.sun.star.frame.Desktop")
prop = iole_app.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
prop.Name = "Hidden"
prop.Value = TRUE // don't want to show UI
props[1] = prop
fixedFilename = "file:///" + asFilename
gf_replace_all(fixedFilename, "\", "/") // replaces the 2nd arg with the 3rd throughout the 1st arg

document = desktop.loadComponentFromURL(fixedFilename, "_blank", 0, props)

pos = LastPos(Lower(fixedFilename), ".xls")
if pos = 0 then
    MessageBox("Test", "Cannot find .xls in filename")
    DESTROY iole_app
    return FALSE
end if
saveFilename = Mid(fixedFilename, 1, pos) + "csv"
props[1].Name = "FilterName"
props[1].Value = "scalc: Text - txt - csv (StarCalc)"
props[2] = iole_app.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
props[2].Name = "Overwrite"
props[2].Value = TRUE

try
    document.storeToURL(saveFilename, props)
catch (OLERuntimeError ex)
    MessageBox("Test", "Error " + ex.description + " saving to " + saveFilename + "~n~n: " + ex.text)
end try

document.close(TRUE)
DESTROY document
SetNull(document)

desktop.dispose()
DESTROY desktop
SetNull(desktop)

DESTROY iole_app
SetNull(iole_app)

return TRUE


I know I am doing overkill on the DESTROYs and also SetNull, but I'm trying everything. If I change the property "Hidden" used in the properties sent to loadComponentFromURL() to FALSE (or omit it, since FALSE is the default) the OO application usually (but not always) closes after the code finishes. With it set to Hidden, the OO application never closes.

But document.storeToURL (or the alternative storeAsURL) always throws a com.sun.star.task.ErrorCodeIOException, with no further helpful details. I have checked that the file URL it is supplying is OK, and I know that the directory the file is in is writeable.

Any bright ideas? Thanks.
Dan Cooperstock
OpenOffice 4.1.7 on Windows 10
DanCooperstock
 
Posts: 9
Joined: Fri Oct 16, 2020 3:57 pm

Re: Problems saving doc and closing OO in PowerBuilder

Postby JeJe » Fri Oct 16, 2020 10:03 pm

There's a thread here:

viewtopic.php?f=9&t=2551#p11726

try the property values given there.

Code: Select all   Expand viewCollapse view
  Dim oPV(1) As New com.sun.star.beans.PropertyValue

oPV(0).Name = "FilterName" : oPV(0).Value = "Text - txt - csv (StarCalc)"
  oPV(1).Name = "FilterOptions" : oPV(1).Value = Asc(";") & "," & Asc("""") & ",0,1"

document.storeToURL(saveFilename, opv())

Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1188
Joined: Wed Mar 09, 2016 2:40 pm

Re: Problems saving doc and closing OO in PowerBuilder

Postby DanCooperstock » Fri Oct 16, 2020 10:30 pm

Thanks but I get the exact same ErrorCodeIOException with the FilterOptions property added.
Dan Cooperstock
OpenOffice 4.1.7 on Windows 10
DanCooperstock
 
Posts: 9
Joined: Fri Oct 16, 2020 3:57 pm

Re: Problems saving doc and closing OO in PowerBuilder

Postby DanCooperstock » Fri Oct 16, 2020 10:50 pm

Actually I take it back, that worked - I needed to remove "scalc: " from the start of my FilterName. Also, Acs(";") at the start of the FilterOptions value is wrong for standard CSV (which is COMMA separated value), it should be Asc(",").

However, it's still leaving OpenOffice running in my Task Manager (but with no UI displayed) at least some of the time, even when the Hidden property value used when loading the Excel file is FALSE. Any suggestions for resolving that?

Thanks.
Dan Cooperstock
OpenOffice 4.1.7 on Windows 10
DanCooperstock
 
Posts: 9
Joined: Fri Oct 16, 2020 3:57 pm

Re: Problems saving doc and closing OO in PowerBuilder

Postby DanCooperstock » Fri Oct 16, 2020 10:57 pm

And there's one more problem: a date column's values were converted to the form DD-MM-YYYY, for no apparent reason. (It's certainly not the Short Date Format on my computer.)

I've read the manual and understand about how you can provide a 5th element in the FilterOptions that gives formatting info for each column, but in our use case, we have no idea what is in the Excel file ahead of time, so we really cannot create such an element. (We don't know how many columns there are, or what is in those columns. We just want a clean export of it to CSV for our program to then work with directly.)
Dan Cooperstock
OpenOffice 4.1.7 on Windows 10
DanCooperstock
 
Posts: 9
Joined: Fri Oct 16, 2020 3:57 pm

Re: Problems saving doc and closing OO in PowerBuilder

Postby DanCooperstock » Fri Oct 16, 2020 11:05 pm

DanCooperstock wrote:However, it's still leaving OpenOffice running in my Task Manager (but with no UI displayed) at least some of the time, even when the Hidden property value used when loading the Excel file is FALSE.


Correction: if Hidden is set to FALSE, it stays in Task Manager for about 25 seconds more, then disappears, after my code finishes. If Hidden is set to TRUE, it stays in Task Manager indefinitely. (And I can't do another call to OO until it's gone from Task Manager, or the initial creation of the Desktop fails.)
Dan Cooperstock
OpenOffice 4.1.7 on Windows 10
DanCooperstock
 
Posts: 9
Joined: Fri Oct 16, 2020 3:57 pm

Re: Problems saving doc and closing OO in PowerBuilder

Postby JeJe » Sat Oct 17, 2020 12:21 am

Have you tried stardesktop.terminate?

viewtopic.php?f=20&t=49434
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1188
Joined: Wed Mar 09, 2016 2:40 pm

Re: Problems saving doc and closing OO in PowerBuilder

Postby DanCooperstock » Sat Oct 17, 2020 1:06 am

Yes! desktop.terminate() does it, even with Hidden=TRUE! Thanks.

So that just leaves the problem with the format of the date column in the saved CSV file.
Dan Cooperstock
OpenOffice 4.1.7 on Windows 10
DanCooperstock
 
Posts: 9
Joined: Fri Oct 16, 2020 3:57 pm

Re: Problems saving doc and closing OO in PowerBuilder

Postby JeJe » Sat Oct 17, 2020 1:17 am

Could you change the dates to strings in your preferred format before saving?
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1188
Joined: Wed Mar 09, 2016 2:40 pm

Re: Problems saving doc and closing OO in PowerBuilder

Postby DanCooperstock » Sat Oct 17, 2020 1:35 am

JeJe wrote:Could you change the dates to strings in your preferred format before saving?


Maybe but the input files are completely unknown, so I guess we'd have to examine each cell in the spreadsheet (and how do we find the right ranges of rows and columns to examine?), have a way to determine that Calc thinks a cell is a date, and have code to convert that to a desired string. I'm finding it really hard to find things in the docs I've found so I think I'd need a lot of pointers to do all that successfully!
Dan Cooperstock
OpenOffice 4.1.7 on Windows 10
DanCooperstock
 
Posts: 9
Joined: Fri Oct 16, 2020 3:57 pm

Re: Problems saving doc and closing OO in PowerBuilder

Postby JeJe » Sat Oct 17, 2020 1:35 pm

I don't know spreadsheets very well, so maybe someone else can help here. If there's no way of setting this date output, another way would be to look for all the DD-MM-YYYY strings in the saved CSV file and modify those.
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1188
Joined: Wed Mar 09, 2016 2:40 pm

Re: Problems saving doc and closing OO in PowerBuilder

Postby JeJe » Sat Oct 17, 2020 2:11 pm

Another suggestion is try LibreOffice - it may handle the output differently.
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1188
Joined: Wed Mar 09, 2016 2:40 pm

Re: Problems saving doc and closing OO in PowerBuilder

Postby ms777 » Sat Oct 17, 2020 6:53 pm

... you may want to have look at https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=103331. Maybe the output filter option string is the same as the input filter. I did not check this, though
ms777
Volunteer
 
Posts: 137
Joined: Mon Oct 08, 2007 1:33 am

Re: Problems saving doc and closing OO in PowerBuilder

Postby DanCooperstock » Sat Oct 17, 2020 10:18 pm

ms777 wrote:... you may want to have look at https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=103331. Maybe the output filter option string is the same as the input filter. I did not check this, though


Thanks, but that just gives the FilterName, which I already have working. My only remaining problem is the output date format in the CSV file.
Dan Cooperstock
OpenOffice 4.1.7 on Windows 10
DanCooperstock
 
Posts: 9
Joined: Fri Oct 16, 2020 3:57 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests