[Solved] Reading multiple files from the FileDialog

[zabolyx]

Thanks to MS777 and DannyB (from the OOo Forums) I have my file dialog functioning to the point of allowing multiple files to be selected but not being able to pass the selected files back.

Usually I would expect the variable to hold all the files in it as a string that needs parsed into the individual files. But alas... this is not true.

Code: Select all   Expand viewCollapse view

Function fImportLocalFile() as String 'DONE
   'this function opens a system file open dialog box and allows the
   '   user to pick a file from thier computer to open into the
   '   document for processing
   
   'stores the filedialog object
   Dim oFileDialog as Object
   'stores the returned result of the activation of the dialog box
   Dim iAccept as Integer
   'stores the returned file name/path from the file dialog box
   Dim sPath as String
   'stores the set default path for the dialog box
   Dim InitPath as String

   'stores the types of files allowed in the filedialog
   Dim sFilterNames as String
   
   'setup the filters for the types of files to allow in the dialog
   sFilterNames = "*.csv; *.txt; *.odt; *.ods; *.xls; *.xlt; *.xlsx"
   
   'create the dialog box as a Windows File Dialog
   oFileDialog = CreateUnoService("com.sun.star.ui.dialogs.SystemFilePicker")

   'set the filters for the dialog
   oFileDialog.AppendFilter("Supported files", sFilterNames)

   'set the path as blank
   InitPath = ""
   
   'add the default path to the dialog
   oFileDialog.setDisplayDirectory(InitPath)
   
   'setup the dialog to allow multiple files to be selected
   oFileDialog.setMultiSelectionMode(True)
   
   'set iAccept as the execution of the dialog
   iAccept = oFileDialog.Execute()
   
   'execute and test if dialog works
   If iAccept = 1 Then
      'set sPath as the chosen file from the dialog
      sPath = oFileDialog.Files(0)

      'set the function as sPath for returning to the previous sub
      fImportLocalFile = sPath
   'end current if statement
   End If

End Function


How can I adjust the above code to get the files bak in an array that I can then loop through and open and read the contents from?

I plan on using a loop to do the reading of the files from the array using Lbound and Ubound in a fancy For-Next loop.

And will the method work with the OOo File Picker as well? I use it to access the an FTP server to pull files from as well.

A post by DannyB has an Object read set to oFileDialog.getFiles() and I tried this as well but the editor called me an idiot.

Here is DannyB's post http://www.oooforum.org/forum/viewtopic.phtml?t=3582

[FJCC]

The method getFiles() is the right one to use. I made a couple of changes to your code. Instead of assigning just element 0 of Files to spath, I now assign the entire array to a variable FileArray. The returned array has the directory path as element 0 and the chosen files as elements 1, 2 etc unless you choose only one file. In that case, element 0 of the array contains the entire file path.

Code: Select all   Expand viewCollapse view

Function fImportLocalFile() 'as String
' FJCC: Can't define the function as returning a String because now it returns an array
   'this function opens a system file open dialog box and allows the
   '   user to pick a file from thier computer to open into the
   '   document for processing
   
   'stores the filedialog object
   Dim oFileDialog as Object
   'stores the returned result of the activation of the dialog box
   Dim iAccept as Integer
   'stores the returned file name/path from the file dialog box
   Dim sPath as String
   'stores the set default path for the dialog box
   Dim InitPath as String

   'stores the types of files allowed in the filedialog
   Dim sFilterNames as String
   
   'setup the filters for the types of files to allow in the dialog
   sFilterNames = "*.csv; *.txt; *.odt; *.ods; *.xls; *.xlt; *.xlsx"
   
   'create the dialog box as a Windows File Dialog
   oFileDialog = CreateUnoService("com.sun.star.ui.dialogs.SystemFilePicker")

   'set the filters for the dialog
   oFileDialog.AppendFilter("Supported files", sFilterNames)

   'set the path as blank
   InitPath = ""
   
   'add the default path to the dialog
   oFileDialog.setDisplayDirectory(InitPath)
   
   'setup the dialog to allow multiple files to be selected
   oFileDialog.setMultiSelectionMode(True)
   
   'set iAccept as the execution of the dialog
   iAccept = oFileDialog.Execute()
   
   'execute and test if dialog works
   If iAccept = 1 Then
      'set sPath as the chosen file from the dialog
      'sPath = oFileDialog.Files(0)
      FileArray = oFileDialog.getFiles() 'added by FJCC
      'set the function as sPath for returning to the previous sub
      fImportLocalFile = FileArray  'modified by FJCC
   'end current if statement
   End If

End Function

[zabolyx]

So to properly retrieve the files and paths correctly I need to first evaluate if there was more than one file selected then build my sFile2Open variable from that..

sFile2Open is what calls the function in the first place so I'll change that to sOpenFile() and convert that into my needed variable with a loop

Code: Select all   Expand viewCollapse view

'check if the array has more than one element
If Ubound(sOpenFile())>0 Then
        'count through the files and combine with path
        For iTempCounter = 1 to Ubound(sOpenFile())
                sFile2Open = sOpenFile(0) & sOpenFile(itempCounter)
                 'here I call the needed subs to complete the opening and importing of the files
        Next iTempCounter
Else
        'set the first element as the path and file
        sFile2Open = sOpenFile(0)
        'call needed subs to complete the opening and importing of the files
End If


I think that looks good... does that sound alright?

[zabolyx]

OK so this is working great except when I cancel the file dialog. No file returns an error with the Ubound in the loop.

I'm needing to test the sFileOpen value for data to proceed with the macro or get the error.

Code: Select all   Expand viewCollapse view


Sub mImportLocalAppend 'DONE

   'this sub runs the subs needed to import the listings to the
   '   New Listings sheet at the end of the current listings from
   '   a local stored file
   
   'call sub to setup the needed variables for the document
   mSetVariables
   
   'unprotect the New Listings sheet
   oNewListingsSheet.unprotect(sPassword)

   'get the file to open from the Archive directory on the FTP server
   '   by calling the function to open the file to import
   sOpenFile = fImportLocalFile()
   
   'reset the TooManyListings flag
   bTooManyLisitngsFlag = False
   
   
   'check if the kick flag has been set and
   If bKickFlag = True Then
      'Kick the user to the calling sub
      Exit Sub
   'end the current If statement
   End If
   
   'check if the returned data has anything in it
   If sOpenFile =  "" then                                         <============= getting error here when I try to run the code. Says the variable is not set.
      'alert user no files are selected and kick them
      MsgBox "No file selected"
      'kick user from sub
      Exit Sub
   'end the current if statement
   End If
   
   'Check how many files are returned in sOpenFile
   If    Ubound(sOpenFile()) > 0 then
      'count through the array and merge the path and filename
      For iTempCounter = Lbound(sOpenFile()) + 1 to Ubound(sOpenFile())
         'setup the file path and name
         sFile2Open = sOpenFile(0) & "/" & sOpenFile(iTempCounter)
      
         'set the number of files to import
         iNumOfFiles = Ubound(sOpenFile())
      
         'call the sub that processes the files to import
         mImportLocalFileProcessor
         
         'check if the kick flag has been set and
         If bTooManyLisitngsFlag = True Then
            'Kick the user to the calling sub
            Exit Sub
         'end the current If statement
         End If
         
         
         
      'next file to open
      Next iTempCounter
   'if only one file is in the array then process that file
   Else   
      'setup the file to open
      sFile2Open = sOpenFile(0)
      
      'set number of files to open to 1
      iTempCounter = 1
      iNumOfFiles = 1

      'call the sub that processes the files to import
      mImportLocalFileProcessor


   'end the current if statement
   End If

   'protect the New Listings sheet
   oNewListingsSheet.protect(sPassword)
   
End Sub


Sub mImportLocalFileProcessor
         'this sub processes the file for importing into the system
         
         'reset the kick flag to false
         bKickFlag = False

         'call the sub to test the FTP file is formatted correctly
         mImportLocalTest

         'check if the kick flag has been set and
         If bKickFlag = True Then
            'Kick the user to the calling sub
            Exit Sub
         'end the current If statement
         End If
            
         'call sub to set the import file's columns
         mImportSetColumns
      
         'call sub to write the formula to the Import sheet for
         '   reading and importing the newlistings file into the system
         mImportFormulaWriter
         
         'check if the kick flag has been set and
         If bKickFlag = True Then
            'Kick the user to the calling sub
            Exit Sub
         'end the current If statement
         End If
      
         
         'call the sub that copies the data from the Import sheet to the
         '   New Listings sheet
         mImportNewListings
         
         'call sub to clear the Import sheet and get it prepped for the next run
         mCleanUpImport

End Sub



Sub mImportLocalTest 'DONE

   
   
   'check if a file name has been returned
   If sFile2Open = "" Then
      'inform the user that no file was chosen
      MsgBox "No file selected"
      'set the kick flag to kick the user from the running macro
      bKickFlag = True
      'kick user to the calling sub
      Exit Sub
   'end current if statement
   End If
   
   'set the Formula in the cell to test on the Import sheet
   '   formula to write is based on the selected file from the
   '   open file dialog
   oImportSheet.getCellRangeByName("B11").Formula = "='" & sFile2Open & "'#Sheet1.A1"
   oImportSheet.getCellRangeByName("D11").Formula = "='" & sFile2Open & "'#Sheet1.C1"
   
   'check if the cell for testing has the correct item in it
   '   doing this determines if the file is in Excel format
   If oImportSheet.getCellRangeByName("B11").String = "Metro" Then
      'set the Excel flag as false
      bExcelFlag = False
      'check if the file is in the First of the month format that
      '   Farris sends us
      If oImportSheet.getCellRangeByName("D11").String = "Property ID" Then
         'set the variable that carries this setting to the FormulaWriter sub
         iFileFormatType = 1
      'if the test cell contains Market then set for First of the month
      Else
         'set the variable that carries this setting to the FormulaWriter sub
         iFileFormatType = 2
      'end the current if statement
      End If
   'regroup and test for Excel formatted file
   Else
      'set the test cells using the excel format for testing with
      oImportSheet.getCellRangeByName("B11").Formula = "='" & sFile2Open & "'#$'NewListings(1)'.A1"
      oImportSheet.getCellRangeByName("D11").Formula = "='" & sFile2Open & "'#$'NewListings(1)'.C1"
      
      If oImportSheet.getCellRangeByName("B11").String = "Metro" Then
         'set the Excel flag as false
         bExcelFlag = True
         'check if the file is in the First of the month format that
         '   Farris sends us
         If oImportSheet.getCellRangeByName("D11").String = "Property ID" Then
            'set the variable that carries this setting to the FormulaWriter sub
            iFileFormatType = 1
         'if the test cell contains Market then set for First of the month
         Else
            'set the variable that carries this setting to the FormulaWriter sub
            iFileFormatType = 2
         'end the current if statement
         End If
         
      'end the current if statement
      End If

   'end the current if statement
   End If

   'check if the test cell written to contains the word Metro
   '   this tests if file is in the proper format
   If oImportSheet.getCellRangeByName("B11").String <> "Metro" Then
      'alert user that the file is not in the proper format
      MsgBox "Not a valid format"
      'set kick flag to true to kill the currently running macro
      bKickFlag = true
      'call sub to clear the Import sheet and get it prepped for the next run
      mCleanUpImport
      'kick the user back to the calling macro
      Exit Sub
   'end the current if statement
   End If
   
End Sub





Function fImportLocalFile() 'DONE
   'this function opens a system file open dialog box and allows the
   '   user to pick a file from thier computer to open into the
   '   document for processing
   
   'stores the filedialog object
   Dim oFileDialog as Object
   'stores the returned result of the activation of the dialog box
   Dim iAccept as Integer
   'stores the returned file name/path from the file dialog box
   Dim sPath as String
   'stores the set default path for the dialog box
   Dim InitPath as String

   'stores the types of files allowed in the filedialog
   Dim sFilterNames as String
   
   'setup the filters for the types of files to allow in the dialog
   sFilterNames = "*.csv; *.txt; *.odt; *.ods; *.xls; *.xlt; *.xlsx"
   
   'create the dialog box as a Windows File Dialog
   oFileDialog = CreateUnoService("com.sun.star.ui.dialogs.SystemFilePicker")

   'set the filters for the dialog
   oFileDialog.AppendFilter("Supported files", sFilterNames)

   'set the path as blank
   InitPath = ""
   
   'add the default path to the dialog
   oFileDialog.setDisplayDirectory(InitPath)
   
   'setup the dialog to allow multiple files to be selected
   oFileDialog.setMultiSelectionMode(True)
   
   'set iAccept as the execution of the dialog
   iAccept = oFileDialog.Execute()
   
   'execute and test if dialog works
   If iAccept = 1 Then

      'set sPath as the chosen file from the dialog
      'sPath = oFileDialog.Files(0)
      FileArray = oFileDialog.getFiles() 'added by FJCC
      'set the function as sPath for returning to the previous sub
      fImportLocalFile = FileArray  'modified by FJCC

   'end current if statement
   End If

End Function


Included are the modules that call or are called up to this error point. Is there any way to test that for Null as the watching of the sOpenFile variable shows that as what is the value when it comes to the line it errors on.

[zabolyx]

Here is the working code for those that are interested

Code: Select all   Expand viewCollapse view


Sub mImportLocalAppend 'DONE

   'this sub runs the subs needed to import the listings to the
   '   New Listings sheet at the end of the current listings from
   '   a local stored file
   
   'call sub to setup the needed variables for the document
   mSetVariables
   
   'unprotect the New Listings sheet
   oNewListingsSheet.unprotect(sPassword)

   'get the file to open from the Archive directory on the FTP server
   '   by calling the function to open the file to import
   sOpenFile = fImportLocalFile()

   'check if the Dialog returns the "Empty" message
   If sOpenFile = "Empty" Then
      'alert the user no file selected
      MsgBox "No file selected"
      'Kick the user from the sub
      Exit Sub
   'end the current If statement
   End If

   'reset the TooManyListings flag
   bTooManyLisitngsFlag = False
   
   
   'check if the kick flag has been set and
   If bKickFlag = True Then
      'Kick the user to the calling sub
      Exit Sub
   'end the current If statement
   End If
   
   'check if the returned data has anything in it
   If sOpenFile =  "" then                                         <============= getting error here when I try to run the code. Says the variable is not set.
      'alert user no files are selected and kick them
      MsgBox "No file selected"
      'kick user from sub
      Exit Sub
   'end the current if statement
   End If
   
   'Check how many files are returned in sOpenFile
   If    Ubound(sOpenFile()) > 0 then
      'count through the array and merge the path and filename
      For iTempCounter = Lbound(sOpenFile()) + 1 to Ubound(sOpenFile())
         'setup the file path and name
         sFile2Open = sOpenFile(0) & "/" & sOpenFile(iTempCounter)
     
         'set the number of files to import
         iNumOfFiles = Ubound(sOpenFile())
     
         'call the sub that processes the files to import
         mImportLocalFileProcessor
         
         'check if the kick flag has been set and
         If bTooManyLisitngsFlag = True Then
            'Kick the user to the calling sub
            Exit Sub
         'end the current If statement
         End If
         
         
         
      'next file to open
      Next iTempCounter
   'if only one file is in the array then process that file
   Else   
      'setup the file to open
      sFile2Open = sOpenFile(0)
     
      'set number of files to open to 1
      iTempCounter = 1
      iNumOfFiles = 1

      'call the sub that processes the files to import
      mImportLocalFileProcessor


   'end the current if statement
   End If

   'protect the New Listings sheet
   oNewListingsSheet.protect(sPassword)
   
End Sub


Sub mImportLocalFileProcessor
         'this sub processes the file for importing into the system
         
         'reset the kick flag to false
         bKickFlag = False

         'call the sub to test the FTP file is formatted correctly
         mImportLocalTest

         'check if the kick flag has been set and
         If bKickFlag = True Then
            'Kick the user to the calling sub
            Exit Sub
         'end the current If statement
         End If
           
         'call sub to set the import file's columns
         mImportSetColumns
     
         'call sub to write the formula to the Import sheet for
         '   reading and importing the newlistings file into the system
         mImportFormulaWriter
         
         'check if the kick flag has been set and
         If bKickFlag = True Then
            'Kick the user to the calling sub
            Exit Sub
         'end the current If statement
         End If
     
         
         'call the sub that copies the data from the Import sheet to the
         '   New Listings sheet
         mImportNewListings
         
         'call sub to clear the Import sheet and get it prepped for the next run
         mCleanUpImport

End Sub



Sub mImportLocalTest 'DONE

   
   
   'check if a file name has been returned
   If sFile2Open = "" Then
      'inform the user that no file was chosen
      MsgBox "No file selected"
      'set the kick flag to kick the user from the running macro
      bKickFlag = True
      'kick user to the calling sub
      Exit Sub
   'end current if statement
   End If
   
   'set the Formula in the cell to test on the Import sheet
   '   formula to write is based on the selected file from the
   '   open file dialog
   oImportSheet.getCellRangeByName("B11").Formula = "='" & sFile2Open & "'#Sheet1.A1"
   oImportSheet.getCellRangeByName("D11").Formula = "='" & sFile2Open & "'#Sheet1.C1"
   
   'check if the cell for testing has the correct item in it
   '   doing this determines if the file is in Excel format
   If oImportSheet.getCellRangeByName("B11").String = "Metro" Then
      'set the Excel flag as false
      bExcelFlag = False
      'check if the file is in the First of the month format that
      '   Farris sends us
      If oImportSheet.getCellRangeByName("D11").String = "Property ID" Then
         'set the variable that carries this setting to the FormulaWriter sub
         iFileFormatType = 1
      'if the test cell contains Market then set for First of the month
      Else
         'set the variable that carries this setting to the FormulaWriter sub
         iFileFormatType = 2
      'end the current if statement
      End If
   'regroup and test for Excel formatted file
   Else
      'set the test cells using the excel format for testing with
      oImportSheet.getCellRangeByName("B11").Formula = "='" & sFile2Open & "'#$'NewListings(1)'.A1"
      oImportSheet.getCellRangeByName("D11").Formula = "='" & sFile2Open & "'#$'NewListings(1)'.C1"
     
      If oImportSheet.getCellRangeByName("B11").String = "Metro" Then
         'set the Excel flag as false
         bExcelFlag = True
         'check if the file is in the First of the month format that
         '   Farris sends us
         If oImportSheet.getCellRangeByName("D11").String = "Property ID" Then
            'set the variable that carries this setting to the FormulaWriter sub
            iFileFormatType = 1
         'if the test cell contains Market then set for First of the month
         Else
            'set the variable that carries this setting to the FormulaWriter sub
            iFileFormatType = 2
         'end the current if statement
         End If
         
      'end the current if statement
      End If

   'end the current if statement
   End If

   'check if the test cell written to contains the word Metro
   '   this tests if file is in the proper format
   If oImportSheet.getCellRangeByName("B11").String <> "Metro" Then
      'alert user that the file is not in the proper format
      MsgBox "Not a valid format"
      'set kick flag to true to kill the currently running macro
      bKickFlag = true
      'call sub to clear the Import sheet and get it prepped for the next run
      mCleanUpImport
      'kick the user back to the calling macro
      Exit Sub
   'end the current if statement
   End If
   
End Sub





Function fImportLocalFile() 'DONE
   'this function opens a system file open dialog box and allows the
   '   user to pick a file from thier computer to open into the
   '   document for processing
   
   'stores the filedialog object
   Dim oFileDialog as Object
   'stores the returned result of the activation of the dialog box
   Dim iAccept as Integer
   'stores the returned file name/path from the file dialog box
   Dim sPath as String
   'stores the set default path for the dialog box
   Dim InitPath as String

   'stores the types of files allowed in the filedialog
   Dim sFilterNames as String
   
   'setup the filters for the types of files to allow in the dialog
   sFilterNames = "*.csv; *.txt; *.odt; *.ods; *.xls; *.xlt; *.xlsx"
   
   'create the dialog box as a Windows File Dialog
   oFileDialog = CreateUnoService("com.sun.star.ui.dialogs.SystemFilePicker")

   'set the filters for the dialog
   oFileDialog.AppendFilter("Supported files", sFilterNames)

   'set the path as blank
   InitPath = ""
   
   'add the default path to the dialog
   oFileDialog.setDisplayDirectory(InitPath)
   
   'setup the dialog to allow multiple files to be selected
   oFileDialog.setMultiSelectionMode(True)
   
   'set iAccept as the execution of the dialog
   iAccept = oFileDialog.Execute()
   
   'execute and test if dialog works
   If iAccept = 1 Then

      'set FileArray as the chosen file from the dialog
      FileArray = oFileDialog.getFiles() 'added by FJCC
      'set the function as FileArray for returning to the previous sub
      fImportLocalFile = FileArray  'modified by FJCC

   'check if the Dialog returns 0
   ElseIf iAccept = 0 Then

      'set the function to "Empty" for evaluation
      fImportLocalFile = "Empty"

   'end current if statement
   End If

End Function


By checking if the dialog has been canceled (returns 0) then forcing the message into the out going function data you can easily test for and divert the program as needed. I'm sure there is a better way of doing this, but this works at the moment. Any suggestions feel free to add them.