Troubles with xlsx to pdf export script

[APic]

Hi all, i’m totally new to libreOffice. I put together a script that should open a xlsx, hide a specific sheet, fit worksheets to page and export to pdf. If i manually open xlsx and run script on “ThisDocument” script works perfectly. If run from command line i get the following error on last line:

Error saving document C:\testFolder\LibreOffice\Report.xlsx:
General error. General I/O error.

From my understanding “ExportDirectToPDF” is trying to save source document with changes before exporting. I can’t understand why it fails to save and why it try saving only if source document is opened programmatically.
Thanks for helping.

This is the script:

Sub ExportToPDF(inputArg as string, outputArg as string)

   	dim document as object
   	dim dispatcher as object
   
   	inputFile = "file:///" + inputArg
   	outputFile = "file:///"+outputArg
   	MsgBox(inputFile)
	MsgBox(outputFile)

	dim args2(1) as new com.sun.star.beans.PropertyValue
	args2(0).Name = "MacroExecutionMode"
	args2(0).Value = 4
	args2(1).Name = "Hidden"
	args2(1).Value = False

	component = StarDesktop.loadComponentFromURL(inputFile, "_default",0,args2)

	document = component.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
  
    sheets = component.getSheets()
    sheet = sheets.getByName("SheetToUnexport")
    sheet.isVisible = False
    for each sheet in sheets.ElementNames
       sheet = sheets.getByName(sheet)
       sheet.AutomaticPrintArea = True
    next
    
    styleFamilies = component.StyleFamilies
	pageStyles = styleFamilies.getByName("PageStyles")
	numStyles = pageStyles.Count 

	For count = 0 To numStyles - 1
		defaultStyle = pageStyles(count)
		defaultStyle.ScaleToPagesX = 1
		rem defaultStyle.ScaleToPagesY = 1
	Next count
   
    dim args1(1) as new com.sun.star.beans.PropertyValue
    args1(0).Name = "URL"
    args1(0).Value = outputFile
    
    dispatcher.executeDispatch(document, ".uno:ExportDirectToPDF", "", 0, args1())
    
End Sub

`

This is the command line:

c:\testFolder\LibreOffice>LibreOfficePortable.exe
-headless "macro:///Standard.Module1.ExportToPDF(C:\testFolder\LibreOffice\Report.xlsx,C:\testFolder\LibreOffice\report.pdf)

[anon73440385]

If run from command line i get the following error on last line:

… and you omit to mention your command line, which may be at the heart of the problem (may be you just got no permission to save files at drive C:\ (no subdir).

[APic]

Updated question with command line. I’m using libreOffice portable and files are in subdirs of C:.

[anon73440385]

And were is the file, which contains the macro being called?

ibreoffice6.2  --help | grep macro
   {file} {macro:///Library.Module.MacroName}

May be you want:

LibreOfficePortable.exe --headless Report.xlsx "macro:///Standard.Module1.ExportToPDF(C:\testFolder\LibreOffice\Report.xlsx,C:\testFolder\LibreOffice\report.pdf)"

[JohnSUN]

Please, try change inputFile = "file:///" + inputArg to

inputFile = ConvertToURL(inputArg) (and for outputFile too).

[APic]

I used libreOffice macro editor. Macro seems to be called correctly, file is opened, MsgBoxes pop up and i can debug it with breakpoints. Also tried your command line, same result.

[APic]

@JohnSUN thanks. That did the trick. I’m too young on this forum to upvote, but again, many thanks

[mikekaganski]

May be you want:

LibreOfficePortable.exe --headless Report.xlsx "macro:///Standard...

That is only needed if the macro operates on already opened document, or contained in the document. But in this case, the problem is likely caught by @JohnSUN.

[APic]

Following JohnSUN comment, the solution was just:

inputFile = ConvertToURL(inputArg)
outputFile = ConvertToURL(outputArg)

instead of:

inputFile = "file:///" + inputArg
outputFile = "file:///"+outputArg

[jimk]

Thank you for posting this as the answer. It looks like we have to wait a week to mark it as accepted.