Use Open File Dialog using Macro in LibreOffice/OpenOffice

Advertisement

This tutorial will show how to open a file selection dialog in LibreOffice, OpenOffice using Macro.

File selection dialog is used to select single/multiple file(s) in various automation activities e.g. opening an OpenOffice, LibreOffice workbook, importing a text/csv file etc.

Create Objects

Lets define a function which can be used from any macro to open a file picker dialog.

Function open_file() As String
 ' open file picker dialog here and return the path of the chosen file
End Function

To open a file selection dialog, use FilePicker service of module com.sun.star.ui.dialogs and SimpleFileAccess service of module com.sun.star.ucb. These two service will provide necessary handles to open a file selection dialog.

Declare and create two objects to these services.

   Dim file_dialog as Object
   Dim ucb as object

   GlobalScope.BasicLibraries.LoadLibrary("Tools")
   file_dialog = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
   ucb = createUnoService("com.sun.star.ucb.SimpleFileAccess")

The object ucb a.k.a Universal Content Broker provides a way to access various contents via UCP (Universal Content Providers).

Formatting the Dialog

Before opening the file picker dialog, we need to provide some informations for the dialog. Such as, the file types/extensions (*.jpg, *.png etc) that would be available as filters. Along with file types we will set the default directory to be selected when the dialog is executed.

Define an array containing the file extensions to be loaded in type dropdown. Once defined and filled up, pass it to AddFiltersToDialog function with the dialog object.

   Dim filterNames(3) as String

   filterNames(0) = "*.*"
   filterNames(1) = "*.png"
   filterNames(2) = "*.jpg"

   AddFiltersToDialog(FilterNames(), file_dialog)

To set the default directory which would be opened by dialog, use the SetDisplayDirectory method of dialog object. The ConvertToUrl function transforms the Linux path to a file system path. Say the path /usr to be converted to file:///usr.

   init_path = ConvertToUrl("/usr")

   If ucb.Exists(init_path) Then
      file_dialog.SetDisplayDirectory(init_path)
   End If

Open the Dialog

Now its time to open the file selection dialog and return the file name. File selection dialog will open when file dialog’s Execute function is executed. It returns two value 0 and 1 based on user actions. If you select a file and press OK, it returns 1. If you press CANCEL in the dialog, it returns 0. When user selects a file or multiple files, it is returned as an array. For this example, lets select one file and return the file name.

And finally execute Dispose to release all the resources.

   open_status = file_dialog.Execute()
   If open_status = 1 Then
      file_path = file_dialog.Files(0)
      open_file = file_path
   End If
   file_dialog.Dispose()

Now call the function open_file() from another procedure and let’s show the selected file path in a message box.

Sub pick_a_file()
   Dim fName As String
   fName = open_file()
   MsgBox fName & chr(10) & ConvertFromUrl(fName)
End Sub

Run

After running the function pick_a_file() the dialog can be seen and the message box with selected file path.

File Selection Dialog Open using Macro

File Selection Dialog Open using Macro

File Open Output

File Open Output

Complete Macro


Sub pick_a_file()
   Dim fName As String
   fName = open_file()
   MsgBox fName & chr(10) & ConvertFromUrl(fName)
End Sub

Function open_file() as String

   Dim file_dialog as Object
   Dim status as Integer
   Dim file_path as String
   Dim init_path as String
   Dim ucb as object
   Dim filterNames(3) as String

   filterNames(0) = "*.*"
   filterNames(1) = "*.png"
   filterNames(2) = "*.jpg"

   GlobalScope.BasicLibraries.LoadLibrary("Tools")
   file_dialog = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
   ucb = createUnoService("com.sun.star.ucb.SimpleFileAccess")

   AddFiltersToDialog(FilterNames(), file_dialog)
   'Set your initial path here!
   init_path = ConvertToUrl("/usr")

   If ucb.Exists(init_path) Then
      file_dialog.SetDisplayDirectory(init_path)
   End If

   status = file_dialog.Execute()
   If status = 1 Then
      file_path = file_dialog.Files(0)
      open_file = file_path
   End If
   file_dialog.Dispose()

End Function

Multiple File Selection

One of reader asked in this article’s comment, how to select multiple files and get the names of selected multiple files. So, I have modified above complete macro a bit and it would return the list of file names (full path) that is selected. Only difference is the funtion is defined as Variant and use of file dialog property file_dialog.setMultiSelectionMode(True) and the method file_dialog.getSelectedFiles().

Complete macro for selecting and getting the names of multiple files is present below:

Sub pick_a_file()
   Dim fName() As Variant
   fName = open_file()
   for i = 0 to Ubound(fName)
   		str1 = str1 & fName(i) & chr(10)
   next
   MsgBox str1
End Sub
 
Function open_file() as Variant
 
   Dim file_dialog as Object
   Dim status as Integer
 
   Dim init_path as String
   Dim ucb as object
   Dim filterNames(3) as String
 
   filterNames(0) = "*.*"
   filterNames(1) = "*.png"
   filterNames(2) = "*.jpg"
 
   GlobalScope.BasicLibraries.LoadLibrary("Tools")
   file_dialog = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
   ucb = createUnoService("com.sun.star.ucb.SimpleFileAccess")
 
   AddFiltersToDialog(FilterNames(), file_dialog)
   'Set your initial path here!
   init_path = ConvertToUrl("/usr")
   
   file_dialog.setMultiSelectionMode(True)
   
   If ucb.Exists(init_path) Then
      file_dialog.SetDisplayDirectory(init_path)
   End If
 
   status = file_dialog.Execute()
   If status = 1 Then
      file_path = file_dialog.getSelectedFiles()
      open_file = file_path
   End If
   file_dialog.Dispose()
 
End Function

Function References – Used in this article

Looking for Something Else?

If you are looking for something else in LibreOffice macro tutorials, Or, wants to learn more about it, please follow below link for complete Macro Tutorials Index:

LibreOffice Macro Tutorial Index

You may also like...

  • John J Statler

    FilePicker in Windows 7 needs a fix to work with the native file manager.
    The initial path setting will fail to be set unless there is a small fix to your code.

    The fix is described in
    https://bz.apache.org/ooo/show_bug.cgi?id=123544#c18

    I changed your script to include FILESAVE_AUTOEXTENSION between FilePicker and SimpleFileAccess:

    GlobalScope.BasicLibraries.LoadLibrary(“Tools”)
    file_dialog = CreateUnoService(“com.sun.star.ui.dialogs.FilePicker”)

    Dim Dialogtyp(0)
    DialogTyp(0) = com.sun.star.ui.dialogs.TemplateDescription.FILESAVE_AUTOEXTENSION
    file_dialog.initialize(DialogTyp())

    ucb = createUnoService(“com.sun.star.ucb.SimpleFileAccess”)

    I was quite happy to find that fix, even though it took hours of research and a fair amount of frustration..

    The only other option was to go to Libre menu/tools/options/general/ and check “use Libre Office dialogues” which is not nearly as pretty as the native file manager in Windows 7 +.

    Thank you for writing this up, it save a whole project for me. Now I have everything I promised the office workers!

    Comments are so fr down debugpoint.com that i am afraid some people won’t see this. If you read this Arindam, please include the fix in your main post. Thanks.

    • Thank you John for the comment. I will put it in the main article as a separate section/or a separate article, so that people can get help from. Thanks again.

Translate »