Import all xls files from a selected folder?!

[bugsie857]

Hi all, I am a newbie to VBA and have just completed my first automated task, but I want more!

I have several xls files in a set folder that I import into Access. The code I use is below. The issue I have is that the folder will change, so I need to be able to add a "choose folder" dialoge box. Also the code below asks me to type in a persons name to import that person's xls... I would rather it just import every xls file in the selected folder.

The name of the database table is "Import_Data".
Each xls file to be imported has several tabs with a named region "Import_Data"

I hope this makes sense.
Any ideas?!

Andrew

****************************
Private Sub ORA_Import_Click()

Dim FileName As String

FileName = InputBox("Enter Name")

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import_Data", "K:\SSED\14 - 19\14-19\Common to All\Data\1 Data Sources\Providers\Wentworth Apr122\ROA\" & FileName & "", True, "Export_Data"

End Sub

[msofficems]

try below code:

Private Sub ORA_Import_Click()

Dim dirpath As String

dirpath = InputBox("Enter dir path")

Dim fso As Object: Dim fls As Object: Dim fl As Object
Set fso = CreateObject("Scripting.FileSystemObject")


Set fls = fso.GetFolder(dirpath).Files
For Each fl In fls
MsgBox fl.Path
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import_Data", fl.Path, True, "Export_Data"
Next fl

end sub

reply with result

[bugsie857]

It works!!!! Thank you!! It even pops up with a message box telling me what file is being imported. :)