File Import Loop

[Rob D]

Hi All, I have a simple loop written to import each file in the directory specified...I am working on Access 2010 and Windows 7. The problem is that this code only imports the first file and then hangs up forever...I am really not sure why this is happening...I used this code on a previous version of Access (2003) and it worked fine now I have to retool everything for 2010...PLEASE HELP

Here is my code:

Dim strPath As String
Dim strFile As String


strPath = Me.FilePath & "\"

strFile = Dir(strPath & "*.xls*")
If Len(strFile) = 0 Then
MsgBox "No Files Found"
Else
Do While Len(strFile) > 0
Debug.Print strPath & strFile

' Turn off the warnings
DoCmd.SetWarnings False

'transfers the saved attachment
DoCmd.TransferSpreadsheet acImport, 5, "XXXXXXXXX", strPath & strFile, False, "FORM!A1:U38"


Loop

End If
MsgBox "Import complete.", vbInformation, "Done"



'Let's turn the warnings back on
DoCmd.SetWarnings True

Beep

[June7]

I don't see how this code ever worked for reading multiple files in a directory. The loop is not looping through the directory. It is not even looping through a set of records to get the file names.

Google: Access VBA list files in a folder

http://www.allenbrowne.com/ser-59.html