Hello the following code works great for importing Excel sheets into an Access table. The only issue is, the InputPath contain around 29 subfolders that sit within the Testing folder. Ideally I would like to alter this code slightly so it looks in those subfolders. Many thanks Public Function Import_Multi_Excel_Files() Dim InputFile As String Dim InputPath As String InputPath = "S:\Change Impacts\Testing\" InputFile = Dir(InputPath & "*.xls") Do While InputFile <> "" DoCmd.TransferSpreadsheet acImport, , "entertablename", InputPath & InputFile, True '< The true is for column headers InputFile = Dir Loop

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

VBA - Important Excel sheets into Access Table

Thread starter p4nny
Start date Jun 7, 2017
Tags

vba vba access vba access excel

p4nny

Board Regular

Jun 7, 2017

Hello

the following code works great for importing Excel sheets into an Access table.

The only issue is, the InputPath contain around 29 subfolders that sit within the Testing folder.

Ideally I would like to alter this code slightly so it looks in those subfolders.

Many thanks

Code:

Public Function Import_Multi_Excel_Files()
 
Dim InputFile As String
Dim InputPath As String
 
InputPath = "S:\Change Impacts\Testing[URL="file:///\\enter%20path%20here\"]\[/URL]"
InputFile = Dir(InputPath & "*.xls")
 
Do While InputFile <> ""
 
    DoCmd.TransferSpreadsheet acImport, , "[I]entertablename[/I]", InputPath & InputFile, True '< The true is for column headers
    InputFile = Dir
Loop