Bug 153635 - VBA Methods could handle file path separators OS-independently
Summary: VBA Methods could handle file path separators OS-independently
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All Linux (All)
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Macro-VBA
  Show dependency treegraph
 
Reported: 2023-02-15 13:24 UTC by Gabor Kelemen (allotropia)
Modified: 2024-03-24 20:44 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Modified example file to run on Linux (9.23 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2023-02-15 13:24 UTC, Gabor Kelemen (allotropia)
Details
The PDF created from attachment 181030 also has incorrect name (19.55 KB, image/png)
2023-02-15 13:44 UTC, Gabor Kelemen (allotropia)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen (allotropia) 2023-02-15 13:24:30 UTC
Created attachment 185379 [details]
Modified example file to run on Linux

This is a continuation for bug 139508 and bug 149786

attachment 170014 [details] has the following macro:

Option VBASupport 1
Public Sub printplt1_click()
Dim pdf_template As Worksheet

 Set pdf_template = ActiveSheet
Dim wb As Workbook
Set wb = ActiveWorkbook
    Dim ws As Worksheet
 
If Dir(wb.Path & "\" & "export", vbDirectory) = "" Then
                        MkDir (wb.Path & "\" & "export")
                End If
  
  pdf_template.ExportAsFixedFormat Type:=xlTypePDF, _
                            Filename:=wb.Path & "\" & "export" & "\" & "pdf" & ".pdf", _
                            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                            IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Now that ExportAsFixedFormat was implemented, this works on Windows.

However under Linux, all of Dir, MkDir and ExportAsFixedFormat fail because the paths / filenames are constructed using "\" as path separators, which is Windows-specific.

It would be nice if such path-handling methods would detect the user intention and interpret (or convert on file open?) these as the correct platform-specific path separators, i.e forward slashes.
Of course if such a path starts with "D:\" then all hope is lost, but macros with relative paths only could work platform-independently.

1. Open attachment 170014 [details]
2. Try to run the macro by clicking the button
-> This is supposed to create an export directory and a pdf.pdf from the document. 
This won't happen until you manually change all instances of the "\" to "/" in the macro editor.
I'm attaching a modified version of the attachment 170014 [details]. Now this does not run in Calc on Windows.

Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: e2d8474857e8b39efcdf21441c95ad51a8aef6e8
CPU threads: 16; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: hu-HU (hu_HU.UTF-8); UI: hu-HU
Calc: threaded
Comment 1 Gabor Kelemen (allotropia) 2023-02-15 13:44:50 UTC
Created attachment 185381 [details]
The PDF created from attachment 181030 [details] also has incorrect name

Another example is attachment 181030 [details] from 149786: the 
        Filename:=".\ExportAsPDF.pdf", _
generates under Linux a filename literally starting with .\ (changing it to ./ fixes the behavior) while under Windows the correct ExportAsPDF.pdf is the result.
Comment 2 Óvári 2023-02-20 07:45:58 UTC
What issues are covered in this issue, i.e.
1. converting `/` to `\` if run on Windows
2. converting `\` to `/` if run on GNU/Linux

Hope this issue means both options.

Thank you
Comment 3 Olivier Hallot 2024-03-24 14:20:52 UTC
Isn't the case to allow ExportAsFixedFormat and other to use of well known ConvertToURL() function as argument for Filename:= ?

Filename:=".\ExportAsPDF.pdf", _
to
Filename:=ConvertToURL(".\ExportAsPDF.pdf"), _

Ref: 
https://help.libreoffice.org/master/en-US/text/sbasic/shared/03120312.html?DbPAR=BASIC#bm_id3152801
Comment 4 Óvári 2024-03-24 20:44:18 UTC
(In reply to Olivier Hallot from comment #3)
> Isn't the case to allow ExportAsFixedFormat and other to use of well known
> ConvertToURL() function as argument for Filename:= ?
> 
> Filename:=".\ExportAsPDF.pdf", _
> to
> Filename:=ConvertToURL(".\ExportAsPDF.pdf"), _
> 
> Ref: 
> https://help.libreoffice.org/master/en-US/text/sbasic/shared/03120312.
> html?DbPAR=BASIC#bm_id3152801

Thank you for your comment.

Should VBA methods in LibreOffice Calc invoke the ConvertToURL() function automatically without the code explicitly requiring this method in the user-generated code?

This would enable the code to work if the file is opened in a spreadsheet program, such as say Microsoft Excel, where the ConvertToURL() function is not known.

Thank you