Transfer Spreadsheet Method loop for Multiple, Unspecified Files (1 Viewer)

[PaulA]

Greetings!

I am relatively new to coding and would like some help with the following problem.

I want to create a loop that will utilize the "Transfer Spreadsheet" method to import data from multiple excel files in one directory to tables in my database.

The main problem is allowing importing multiple files in the same directory with the same extension (.xls) How does one allow for a "wildcard" in the Transfer Spreadsheet method so that multiple, unspecified files can be imported?

Here is some code that I started but am bogged down with:

Dim Path, FileName
FileName = Dir("C:\My Documents\Programs and Facilities\FCCC\TestData\*.xls")

DoCmd.TransferSpreadsheet acImport, 8, "DataTransfer-Groups", "C:\My Documents\Programs and Facilities\TestData\ FileName (Variable)", True, "DataTransfer-Groups!A1:G32"

Your help is much appreciated!!

 

[Fornatian]

As a pointer look at the filesearch object which will allow you to loop a directory and id certain files using an if..then..else statement. You can then include a transferspreadsheet method to the loop to do your imports.

 

[c_smithwick]

The first time you use the Dir function with a filespec it will return the first file found. If you are using it with a wildcard (as you are), the next time you call the Dir function, don't specify a filespec and it will return the next file matching the wildcard, or a zero-length string if no more matching files are found. Try the following:

Dim FileName as string

FileName = Dir("C:\My Documents\Programs and Facilities\FCCC\TestData\*.xls")

DoCmd.TransferSpreadsheet acImport, 8, "DataTransfer-Groups", FileName, True, "DataTransfer-Groups!A1:G32"
do while Len(Filename) <> 0
    FileName = Dir
    DoCmd.TransferSpreadsheet acImport, 8, "DataTransfer-Groups", FileName, True, "DataTransfer-Groups!A1:G32"
Loop