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 this post (address header has been removed since I am new to forum, you can also Google "How do select a file using the a dialog box" and the result would be near the top):
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 [COLOR=#810081]tek-tips.com/faqs.cfm?fid=2484[/COLOR]'''
' 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 mulitple files I used this code (which if I understand it correctly, should split the returned string into individual file names.

'''Adapted from answers.yahoo.com/question/index?qid=20071203135914AA0cY8r(http header removed)'''
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

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

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

However, if I select multiple files, I get the following resultin 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 return 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.

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

So this is where I am:

Dim Me As Object
Set Me = CreateObject("Human")
Me.:banghead: = True

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

 

[Isskint]

Why are using Windows API when there is a very competent object called FileDialog? With FileDialog you can specify multiselect and then loop through the returned files one at a time to 'extract' file path, file name etc.

 

[yamiusingvba]

I apologize. I left out an important fact that I am forced to use MS Access 2000. If I understand it correctly, the FileDialog object was not added until later versions.

I am wondering if my problem is caused by the vbNull delimiter....

Why are using Windows API when there is a very competent object called FileDialog? With FileDialog you can specify multiselect and then loop through the returned files one at a time to 'extract' file path, file name etc.