Selecting multiple files from open dialog and splitting them into strings

[yamiusingvba]

I am currently working on a database that uses the Windows API to call an open dialog box to allow the user to select an Excel file, which I then import into the database. Currently, the user has to select one file at a time (which is working perfectly); however, they may receive anywhere from 8 to 32 files each month that need to be uploaded.

The open dialog box I use is adapted from the faq on tek-tips:
tek-tips.com/faqs.cfm?fid=2484

I modified the code I use to call the dialog box to set the flags cdlOFNExplorer (to use long file names) and cdlOFNAllowMultiselect (to allow multiple selections).

'''Adapted from tek-tips.com/faqs.cfm?fid=2484'''
' Code to call dialog box
Dim cmdlgOpenFile As New clsCommonDialog
Const clngFilterIndexAll = 5
 
' Allow multiple files to be selected, using long file names
cmdlgOpenFile.Flags = cdlOFNExplorer Or cdlOFNAllowMultiselect
 
' Set filters
cmdlgOpenFile.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
cmdlgOpenFile.FilterIndex = clngFilterIndexAll
 
' Opens the dialog box
cmdlgOpenFile.ShowOpen

My understanding is that if long file names are used, then the code will return a single string containing the path and file names delimited by a vbNull.

To test the result of selecting multiple files I used this code (which if I understand it correctly, should split the returned string into the path and individual file names.

'''Adapted from answers.yahoo.com/question/index?qid=20071203135914AA0cY8r'''
Dim strFiles() As String 'array to hold the split files
Dim intCnt As Integer ' count of array elements
Dim Cnt As Integer ' used to index through the array
Dim sep As String 'delimiter character
 
sep = Chr(0) 'define a null seperator
 
With cmdlgOpenFile
 
    Debug.Print (.fileName)
    strFiles = Split(.fileName, sep)
    intCnt = UBound(strFiles)
    For Cnt = 0 To intCnt
        Debug.Print (strFiles(Cnt))
    Next Cnt
 
End With

(Note: rather than defining my own sep variable, I have also tried using vbNull and vbNullChar as the delimiter, with identical results)

When I run the code, if I select one file, I get the intended result in the immediate window:

C:\Documents and Settings\xxxxxxxxxx\Desktop\filename1.xls
C:\Documents and Settings\xxxxxxxxxx\Desktop\filename2.xls

However, if I select multiple files, I get the following result in the immediate window:

C:\Documents and Settings\xxxxxxxxxx\Desktop
C:\Documents and Settings\xxxxxxxxxx\Desktop

It does not matter if I select two, three, or ten files; the Debug.Print only runs twice, and it only returns the path with no file names.

I also tried saving the whole string returned into a single string variable, and the using MsgBox "variableName" to test the code; however, if I select multiple files, this only returns the path as well. Additionally, I modified the code above as follows:

With cmdlgOpenFile
 
    Debug.Print (.fileName)
    strFiles = Split(.fileName, sep)
    intCnt = UBound(strFiles)
    
    Debug.Print (strFiles(0))
    Debug.Print (strFiles(1))
    Debug.Print (strFiles(2))   
 
End With

The result in the immediate window should be:

C:\Documents and Settings\xxxxxxxxxx\Desktop
filename1.xls
filename2.xls

However, what I get is an out of bounds error when I reach strFiles(1). It appears that if one file is selected the code works properly, but if more than one file is selected the code only returns the path and not the file names.

How can I get the code to return both the path and the file name for each file selected, and assign the individual file names and paths to a collection, array, or individual variables that I can then reference in the rest of my code?

Any help you can provide would be greatly appreciated...

 

[madonnac]

This is the code I use to select files:

Dim fd As Object
Set fd = Application.FileDialog(3) [COLOR=#8AE234]'MsoFileDialogOpen not available due to late binding[/color]
With fd
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xls; *.xlsx; *.xlsm; *.xlsb", 1
    .ButtonName = "Select"
    If .Show = -1 Then
        XLFilename = .SelectedItems(1) [COLOR=#8AE234]' returns an array, even with multiselect off[/color]
    Else
        Exit Sub
    End If
End With

Set fd = Nothing

if you change the .AllowMultiSelect to True, then you can use UBound(.SelectedItems) to find out how many files were chosen

 

[yamiusingvba]

Thank you for your reply.

I missed out an important aspect...I am using (i.e. stuck with) Access 2000. It is my understanding that the File Dialog object was not implemented until Access 2007. As far as I have been able to find, I am stuck with using the code above...

 

[strongm]

The problem is that although the FAQ states "This object presents exactly the same interface as the Microsoft Common Dialog 6.0 library from Visual Basic 6.0 (comdlg32.dll)", this is not quite true. In particular, as you have found, it is only capable of returning a single filename (or directory) thanks to the WOFN_tFN subroutine. This can be fixed so that the class returns the same data as the VB6 common dialog by making a very minor change as follows:

[blue]Private Sub WOFN_to_OFN(wofn As W32_OPENFILENAME)
' This sub converts from the Win32 structure to the Microsoft Access structure.
    With wofn
        FileName = Left$(.lpstrFile, InStr(.lpstrFile, vbNullChar [b][red]& vbNullChar[/red][/b]) - 1)
        FileTitle = Left$(.lpstrFileTitle, InStr(.lpstrFileTitle, vbNullChar) - 1)
        FilterIndex = .nFilterIndex
        Flags = .lngFlags
    End With
End Sub[/blue]

With this change, your split function should work as expected

 

[yamiusingvba]

Thank you very much for your reply. That would explain it. I will test this as soon as I get a chance, and let you know how it works.