Get External Data2

[tom_n_ape]

I am importing data from multiple Excel files into an Access table. I
can perform this task for one table using the code I pasted below of
which I found in this group. The CreateTableFromExcel() function is
designed to receive the Excel file location and name as input. How
could I modify the code below to loop this function to import multiple
Excel files? I have hundreds of files all in the same directory. I
think I need to read all file names in the directory into an array
first, then loop through the array calling CreateTableFromExcel() each
time. If you could offer any help I would greatly appreciate it.

Sub CreateTableFromExcel(strFile As String)
On Error GoTo err_CreateTableFromExcel
Dim db As Database
Dim dbExcel As Database
Dim rsExcel As Recordset
Dim rsNewTbl As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim strSql As String
Dim strData As String


Set db = CurrentDb ' points to currentdb


Set dbExcel = OpenDatabase(strFile, False, False, "Excel
5.0;HDR=YES;IMEX=2;") ' points to Excel97


Set tdf = dbExcel.TableDefs(0) ' going to work on only the first
WorkSheet in the WorkBook


' get the worksheet name for the table name and loose any
spaces!
strData = "tbl" & Mid(tdf.Name, 2, Len(tdf.Name) - 3)
Do While InStr(strData, Chr(32)) > 0
strData = Left(strData, InStr(strData, Chr(32)) - 1) &
Mid(strData, InStr(strData, Chr(32)) + 1)
Loop

.....snip....

 

[tom_n_ape]

I solved this problem with the help of some code I found in older
posts to this group. The code below will loop through all the .xls
files in the designated folder and create an Access table for each
using the function CreateTableFromExcel(string) also found in this
group.


Sub asdf()
Dim strFileName As String
'strFileName = "a" 'Need to set file name to a non-blank
value so we go into the loop.
strFileName = Dir("C:\type your folder name here\")

Do While strFileName <> ""
If strFileName <> "" Then 'A file was found
'DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel7, "MyTable", strFileName, True
CreateTableFromExcel ("C:\type your folder name here\" &
strFileName)
End If
strFileName = Dir() 'this function will loop you to the next
file in the folder"C:\type your folder name here\"
Loop
MsgBox "Done"
End Sub