Please help - I want to loop through all workbooks in a folder and import to access

[yjones]

Hello -

I am having a real tough time trying to figure out this code. I am trying to import a massive amount of excel workbooks in a file into an access database. I am able to figure out how to import one workbook into the database using the transferspreadsheet method, but I want to be able to use some loop code so that the vb module in access goes through many spreadsheets in a particular file. I tried putting the following code together, but it gives me a 'compiled error - user defined type not defined' error. I'm not very familiar with vb, so i was hoping someone knew what i am doing wrong or if this is totally wrong, help me with the proper coding. Thank you very much in advance for helping!

Sub allfolderfiles()
Dim wb As workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Documents and Settings"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
DoCmd.TransferSpreadsheet acImport, 8, "newmetlife", "C:\Documents and Settings\*.xls", True, "range"
wb.Close
TheFile = Dir
Loop
End Sub

[Krzemo]

1) DoCmd.TransferSpreadsheet acImport, 8, "newmetlife", "C:\Documents and Settings\*.xls", True, "range" is totally unrelated to other code.
2) To use Excell.Workbook U have to set project reference to Excel COM library.
3) Your code simply open and than close a workbook through Excel.Workbook object. Nothing more happens.

Best regards,
Krzemo.

PS: Is it an Access VBA?

[jacander]

Is this one excel file with several worksheets?
never had a need to do this one.

or

Is this several excel files with one worksheet?
This one is very easy to do.

[CODE]

Dim strfile As String

Do
strfile = Dir("c:\MyPath\*.xls")
If strfile <> "" Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TableName", "c:\MyPath\" & strfile, True
'anything else you want to do at this point
FileCopy "c:\MyPath\" & strfile, "c:\MyPath\Completed\" & strfile
Kill "c:\MyPath\" & strfile
End If
Loop Until strfile = ""