Select Folder and Import from all Excel files in Folder

[Erick C]

I am having a bit of a problem with an import. I am using a code from
Ken Snell that opens a navigation window to select a folder and then
all of the first worksheets in the folder are imported. I keep
getting a run-time error 3011. It seems that there is a \ missing in
the file name that is created. In the error is shows as "
X:\UPC Analysis\SnapshotsTGT 0012 Precount Snapshot 2.16.xls"
I believe that there should be a \ in between Snapshots and TGT, but I
cannot seem to modify the code correctly to get it to work. Any help
that I can get would be greatly appreciated.
My code is:

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

strBrowseMsg = "Select the folder that contains the EXCEL files:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "PreCount_Snapshots"

strFile = Dir(strPath & "\*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

 

[Ken Snell]

My error, Erick.

I've corrected the code at my website. Thanks for catching the error.

Change this code line:
strPathFile = strPath & strFile

to this:
strPathFile = strPath & "\" & strFile

 

[Erick C]

Ken -
Thank you for the update. I will get this change in!

 

[Erick C]

Hi again Ken -
I am getting a run time error '7890' saying the file is empty, but it
is not. The first file will import but the next one will not. Is
this a matching error in the formatting in the excel spreadsheet?

 

[Ken Snell]

Hard to say what might be the problem without seeing the actual files.

Each EXCEL file must have the same layout/formatting on the sheet that is
being imported from all files. Try removing that second file from the folder
and see if the other files will import. Maybe it's a corruption problem with
that one file?
--

Ken Snell
http://www.accessmvp.com/KDSnell/




Hi again Ken -
I am getting a run time error '7890' saying the file is empty, but it
is not. The first file will import but the next one will not. Is
this a matching error in the formatting in the excel spreadsheet?