Hi I’m new to LibreOffice, and I have a excel macro that I wan to run in LibreCalc, but it doesn’t work, I get this error;
BASIC-runtimeerror
‘423’
Can’t find the property or method. Path (translated from swedish)
What should I do to get it to work?
I’m running LibreOffice 24.8.3.2 on Windows 11
Regards
Staffan
Please upload the macro code and an ODF type sample file here.
Here is the code:
Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Sub ExportEachSheet2PDF()
'Created by Yoda Learning Academy
'This VBA code will export each sheet of the workbook
'to a PDF file
'
Dim objWS As Worksheet
Dim strFileName As String
Dim strPath As String
Dim strCrntWS As String
strPath = ThisWorkbook.Path 'Assigned current file path
strCrntWS = ActiveSheet.Name 'Assigned current sheetname
Application.ScreenUpdating = False 'Disabled screen updating
For Each objWS In Worksheets
objWS.Select
strFileName = objWS.Name & ".pdf" 'Assigned worsksheet name as filename
'Following code will put '\' if it is not there
'-----------------------------------------------
If VBA.Right(strPath, 1) <> Application.PathSeparator Then
strPath = strPath & Application.PathSeparator
End If
'The following code will decide the export file type and then export it
'to the given path considering other required parameters.
'-----------------------------------------------------------------------
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=strPath & strFileName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False 'This will prevent to open the file after publishing
Next objWS
Worksheets(strCrntWS).Select 'After exporting all the worksheets, it will return to active sheet
Application.ScreenUpdating = True 'Enabled screen updating
MsgBox "All sheets have been exported as PDF.", vbInformation + vbOKOnly, "Exported as PDF"
End Sub
I can’t save it as ODF, I only have ODS and OTS. I have saved it as XLSM.
The macro is supposed to create a PDF-file of each sheet in the excel-file.
I don’t know how to upload a sample file, I have put it here, I hope you can download it:
Regards
Staffan
[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck]
ODF: (International Standard) Open Document Format
.ods (filename extension) : Open Document Spreadsheet
.ots (filename extension) : Open Template Spreadsheet
This scenario - exporting each sheet of a spreadsheet to a separate PDF file - has been discussed on this resource many times. The most detailed discussion, as far as I remember, was three years ago. I suggest abandoning the macro on VBA and using the listings from that topic.