Automatic Import of multiple CSV file in Access with

[aggar]

Hi,

I need to import 15 csv files into access every month. I have location of all those files listed in a table in the same access DB but i'm not sure how to incorporate them in a macro I have written to import one of those files:

Function Macro_TransferCsvFileTo_RatesTradeData()
On Error GoTo Macro_TransferCsvFileTo_RatesData_Err
DoCmd.TransferText acImportDelim, "Import_Spec_tbl_RatesData", "tbl_RatesData", "c:\Documents and Settings\aggaura\Desktop\WIP\XML\DB_JOH_FI_2011123 0.csv", True, ""

Macro_TransferCsvFileTo_RatesData_Exit:
Exit Function


Macro_TransferCsvFileTo_RatesData_Err:
MsgBox Error$
Resume Macro_TransferCsvFileTo_RatesData_Exit
End Function

where c:\Documents and Settings\aggaura\Desktop\WIP\XML\DB_JOH_FI_2011123 0.csv is the file name I want to be replaced by each of the file listed by me in the other access table.

Could someone help please?

Many Thanks

[June7]

I don't know macros well enough to be sure, but think you will have to use VBA. I don't know if looping code can be defined in a macro. And what you need to do is open a recordset of the table and loop through the records, read the path/filename from the field and execute the import on each.

[aggar]

Yeah - I was able to do that - see below - but I'm having to specify all the file names in a separate table - is there a way to just specify a particular directory and automatically import the filenames?

Function Macro_TransferCsvFileTo_RatesData()
On Error GoTo Macro_TransferCsvFileTo_RatesData_Err
Dim filename As String
Dim filecount As Integer
Dim loopcount As Integer
filecount = DMax("[ID]", "tbl_FileNames_Query")

For loopcount = 1 To filecount

filename = DLookup("[CompleteFileName]", "tbl_FileNames_Query", "[ID] = " & loopcount)

DoCmd.TransferText acImportDelim, "Import_Spec_tbl_RatesData", "tbl_RatesData", filename, True, ""
'DoCmd.TransferText acImportDelim, "DB_JOH_SWAPS_20111230 Import Specification", "tbl_RatesData", filename, True, ""

Next

MsgBox ("Files Imported - Check for Import Errors in DB")
Macro_TransferCsvFileTo_RatesData_Exit:
Exit Function
Macro_TransferCsvFileTo_RatesData_Err:
MsgBox Error$
Resume Macro_TransferCsvFileTo_RatesData_Exit
End Function

[June7]

You need some code that will get all the files in a directory.
Google: VBA import all files in a directory
Here is one:
http://www.eraserve.com/tutorials/MS..._All_Files.asp