Launch macro from command line but error

[ducpd]

i have a macro like this, when i run command line with headless it crashes.
after debugging i found it error after assigning document = ThisComponent.
the code below I have referenced in other questions, but for me it doesn’t work.

    Sub FitToPage
      On Error Resume Next
      Dim document As Object
      Dim pageStyles As Object
      Dim sheetCount as Integer
      document = ThisComponent
      pageStyles = document.StyleFamilies.getByName("PageStyles")
      sheetCount = document.Sheets.Count - 1
      For i = 0 To sheetCount
       Dim sheet As Object, style As Object
       sheet = document.Sheets(i)
       style = pageStyles.getByName(sheet.PageStyle)
       style.ScaleToPagesX = 100
      Next i
      document.storeSelf(Array())
      document.close(true)
    End Sub

[JohnSUN]

Welcome!

This code will work if the Calc spreadsheet is open at the time it is run. What command line are you using to run the macro?

[ducpd]

Hello.
I ran this command:

libreoffice  --invisible --nofirststartwizard --headless --norestore /var/www/storage/app/public/123456789.xlsx macro:///Standard.Module1.FitToPage

[ducpd]

it doesn’t seem to get it ThisComponent .
so at this line it got error: pageStyles = document.StyleFamilies.getByName("PageStyles")

[JohnSUN]

Hmm… The workbook is not yet open by the time the macro is run. It might be easier to rewrite the macro like this:

Sub FitToPage(sSourcePath As String)
... all your Dim and On Error...
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
Dim loadComponentProperties(1) As New com.sun.star.beans.PropertyValue
	loadComponentProperties(0).Name= "FilterName"
	loadComponentProperties(0).Value= "Calc MS Excel 2007 XML"
	loadComponentProperties(1).Name= "Hidden"
	loadComponentProperties(1).Value= True
	document = OpenDocument(ConvertToURL(sSourcePath),loadComponentProperties)
...

and call it like as

libreoffice  --invisible --headless --norestore macro:///Standard.Module1.FitToPage(/var/www/storage/app/public/123456789.xlsx)

[ducpd]

Here is the code after i fixed it but it still error at the line assign document. :((

    Sub FitToPage(sSourcePath As String)
        <!-- On Error Resume Next -->
        Dim document As Object
        Dim pageStyles As Object

        GlobalScope.BasicLibraries.LoadLibrary("Tools")
        Dim loadComponentProperties(1) As New com.sun.star.beans.PropertyValue
        loadComponentProperties(0).Name= "FilterName"
        loadComponentProperties(0).Value= "Calc MS Excel 2007 XML"
        loadComponentProperties(1).Name= "Hidden"
        loadComponentProperties(1).Value= True
        document = OpenDocument(ConvertToURL(sSourcePath),loadComponentProperties)

        pageStyles = document.StyleFamilies.getByName("PageStyles")
        For i = 0 To document.Sheets.Count - 1
            Dim sheet As Object, style As Object
            sheet = document.Sheets(i)
            style = pageStyles.getByName(sheet.PageStyle)
            style.ScaleToPagesX = 1
        Next i
        document.close(true)
    End Sub

[JohnSUN]

It seems to me that this time the error is in this line:

@ducpd

<!-- On Error Resume Next -->

In Basic, a comment or the word Rem at the beginning of a line, or an apostrophe. If I were the interpreter, I would be outraged because of the syntax

[ducpd]

:)) . I deleted the redundant parts but it still has the same error

[JohnSUN]

Okay, let’s do a simple experiment. Run Calc, press Ctrl + O and paste in the field “File name” all this construction /var/www/storage/app/public/123456789.xlsx

I understand that such a proposal may look insulting, but we must make sure that it is with this file that Сalс has no problems opening

[ducpd]

sorry, do you mean run it in the app?

[ducpd]

I am currently working with libreoffice in the docker workspace

[JohnSUN]

Yes, exactly! So to see for sure that the workbook has loaded without the stupid pop-ups like “There are too many columns in this spreadsheet, some information will be lost - OK?”

[ducpd]

Ok. i opened it in calc app and it works fine

[mikekaganski]

Maybe it’s not a real XLSX? Try commenting out the two lines that define the contents of loadComponentProperties(0), to rely on the automatic filter detection.

[ducpd]

it still hasn’t changed :((

[JohnSUN]

Okay, what if you add after
document = OpenDocument(...
a line
Print "document is " & IsEmpy(document) & "," & IsNull(document)
and run the command without --invisible --headless --norestore?

[ducpd]

it hangs indefinitely.
so this line doesn’t work:

document = OpenDocument(ConvertToURL(sSourcePath),loadComponentProperties())

[JohnSUN]

We wrote too much in the comments - those who will read this after us will simply get lost in this pile of letters. Let’s go talk privately. When we find a solution, we will post it here.