macro executed when document is loaded

Question

After losing files from a HD, data rescue gave me back most of them, but with abstract names (such as file000123.xlsx). I need to rename them using cell values (client name, invoice ref).

I could make a Basic macro that works for this, if I open the files and start the macro myself for each one of those files. As I have thousands of files to rename, I need that macro to execute on its own, either when documents are loaded, otherwise on a selected folder.

I assigned my macro to the "document loaded" event via the "tools-Customize-Events" menu. Then, I get a "wrong property value" error on the 1st line, the one defining the function.

Is my way of doing wrong ? Do I have to modify the macro for it to work there ?

Context :

• This macro is in "My macros", not within the documents.
• using libreOffice 7 on Linux
• working on .xlsx files

Thanks for any help, my libreOffice Basic is even poorer than my English.. B-)

My code :

function getFullRep(sPath As String) As String
Dim cpt As Integer
Dim buf As String
Const SLASH = "/"
   buf = ""
   for cpt = Len(sPath) to 1 step -1
      if Mid(sPath, cpt, 1) = SLASH then
          buf = Left(sPath, cpt)
         exit for
       end if
   next
   getFullRep = buf
end function
 
Sub Main
 
Dim oDoc as Object
Dim sG2 as String
Dim sO2 as String
Dim sO as String
Dim sPathBackupFolder as String
 
Dim filespec As string
Dim laDate As String
Dim myfilename As String
Dim oFeuille As Object
Dim sNomFeuille As String
 
oDoc = ThisComponent
 
sPathBackupFolder = getFullRep (oDoc.location)
 
oFeuille = oDoc.getCurrentController().getActiveSheet()
 
sNomFeuille = oFeuille.getName()
laDate = Format(Now(),"YYMMDDhhmmss")
 
If oFeuille.GetCellRangeByName("G14").String <>"" Then
sG2 = oFeuille.GetCellRangeByName("G14").String
sO2 = oFeuille.GetCellRangeByName("A15").String
    else
sG2 = oFeuille.GetCellRangeByName("G15").String
sO2 = oFeuille.GetCellRangeByName("A16").String
    End If
sO = Replace(sO2, "Facture N°", "")
 
'Chemin et nom de fichier composé
myfilename = sPathBackupFolder+sG2+" - "+sO+" - "+laDate+".ods"
 
'Enregistrer sous
dim document   as object
dim dispatcher as object
 
' ----------------------------------------------------------------------
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
 
' ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = ConvertToUrl(myfilename) 'On converti le chemin
args1(1).Name = "FilterName"
args1(1).Value = "calc8"
 
dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())
oDoc.store
oDoc.close(True)
End Sub

Answer 1

You are getting the error because the document has not completely opened yet. Instead, attach to the event "View Created", and make sure to save to "LibreOffice" (as opposed to individual document).

By the way, it would be a good idea to give your sub a more appropriate name than "Main".

Hope that helps!