Import multiple Excel spreadsheets into a single Access table

Question

Hello,

The following code does in fact import records contained in the Excel files contained within a given folder.  The Excel files are all named using the following naming convention:

• ExportProd1
• ExportProd2
• ExportProd3, etc. (all the way to 'ExportProd75')

  However, it gets caught in a vicious loop where it repeatedly imports only records from the 'ExportProd1', 'ExportProd10', 'ExportProd11', 'ExportProd12', and 'ExportProd13' Excel files.  My total record count from all 75 files is around 2.4 million.  The looping activity caused the total to grow in excell of 11 million before the database crashed.

Here is the code.  Why is it looping like that instead of going through all 75 files within the folder in order?  All files are uniquely named:

Option Compare Database

Function Impo_allExcel()

Dim myfileDim mypathmypath = "C:\PDooley_Projects\CompanyName\Customer_Integration\Oracle_export"Domyfile = Dir(mypath & "*.xls")'this will import ALL the excel files (one at a time, but automatically) in this folder. Make sure that's what you want.DoCmd.TransferSpreadsheet acImport, 8, "Contacts_AVDC_NEW", mypath & myfilemyfile = DirLoop Until myfile = ""

End Function

I do hope I can adjust this code to prevent this looping, otherwise, I will have to import every single Excel file into my Access database one at a time.

Thank you.

---

Answer 1

Try this modification.

Function Impo_allExcel()

Dim myfile

Dim mypath

mypath = "C:\PDooley_Projects\CompanyName\Customer_Integration\Oracle_export"

ChDir(MyPath)

myfile = Dir()

Do While MyFile <> ""

  If MyFile Like "*.xls" THEN

     'this will import ALL the excel files

     '(one at a time, but automatically) in this folder.

     ' Make sure that's what you want.

    DoCmd.TransferSpreadsheet acImport, 8, "Contacts_AVDC_NEW", mypath & myfile

  End If

  myfile = Dir()

Wend

End Function

Answer 2

Hi Pat

The problem is that the first Dir (the one with the argument) should not be inside the loop.

Calling Dir( path ) creates a new "context" and returns the first file matching the given specification.

Calling Dir with no argument returns the next file in the current context.

However, you are creating a new context every time around the loop, thus resetting the search and returning the same file repeatedly.  So move the first Dir() call up before the "Do".

Best wishes,

Graham

Answer 3

Hi,

What about if I want a specific sheet from a workbook? So I have a workbook created each day that has a base sheet from which there are other sheets with pivot tables. I want to take only the base sheet with the raw data from all the workbooks and bring that into a table in Access. Any advice will be appreciated.

Thanks and Regards,

M