Import Multiple Files using VB

[ondvirg via AccessMonster.com]

I have multiple .XLS files in a directory that have the same name except for
the last character that I'd like to import into a table.

I can use this code:
DoCmd.TransferSpreadsheet acImport, 8, "Bill Direct", [SubDirectory] &
"pclI083D001.xls", True, "A4254"

to transfer a specified file name, but would like to import all files at once.
I've tried using:

DoCmd.TransferSpreadsheet acImport, 8, "Bill Direct", [SubDirectory] &
"pclI083D00" & "*", True, "A4254"

But get errors. Is there a way to code for this?

 

[Marshall Barton]

I have multiple .XLS files in a directory that have the same name except for
the last character that I'd like to import into a table.

I can use this code:
DoCmd.TransferSpreadsheet acImport, 8, "Bill Direct", [SubDirectory] &
"pclI083D001.xls", True, "A4254"

to transfer a specified file name, but would like to import all files at once.
I've tried using:

DoCmd.TransferSpreadsheet acImport, 8, "Bill Direct", [SubDirectory] &
"pclI083D00" & "*", True, "A4254"

TransferXXX does not accept wildcards in the file name.

You need to use a loop to get each file. Try something like
this air code:

fn = Dir(SubDirectory & "pclI083D00*.XLS")
Do Until fn = ""
DoCmd.TransferSpreadsheet acImport, 8, "Bill Direct", _
SubDirectory & fn, True, "A4254"
fn = Dir()
Loop

 

[Ken Snell \(MVP\)]

See Import Data from All EXCEL Files in a single Folder via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpFolderFiles

 

[ondvirg via AccessMonster.com]

Thanks Marshall, This sent me in the right direction. Works great!

I have multiple .XLS files in a directory that have the same name except for
the last character that I'd like to import into a table.

[quoted text clipped - 8 lines]

DoCmd.TransferSpreadsheet acImport, 8, "Bill Direct", [SubDirectory] &
"pclI083D00" & "*", True, "A4254"

TransferXXX does not accept wildcards in the file name.

You need to use a loop to get each file. Try something like
this air code:

fn = Dir(SubDirectory & "pclI083D00*.XLS")
Do Until fn = ""
DoCmd.TransferSpreadsheet acImport, 8, "Bill Direct", _
SubDirectory & fn, True, "A4254"
fn = Dir()
Loop