Using VBA to Link a Directory of CSV Files

[Butaambala]

Hello,

I am attempting to write a procedure that will loop through a directory
of CSV files, linking each of them to a database. I have tested the
following code for XLS files, and it works fine, but receive errors
when I change it to link CSVs.

Sub mcrAllPermut_LinkExternalTables()
Set db = CurrentDb
strDir = "C:\"
strTemp = Dir(strDir & "*.csv*")

Do While Len(strTemp)

strPath = strDir & strTemp

Set tdfLink = db.CreateTableDef(Left(strTemp, 8))

tdfLink.SourceTableName = Left(strTemp, 8)

tdfLink.Connect = "Text;DATABASE=" & strPath

db.TableDefs.Append tdfLink

strTemp = Dir$()
Loop
End Sub

I receive Run-time error '3044': 'C;\<filename>.csv' is not a valid
path.
I have also attempted to manually link one file, then change the
..Connect of that linked table, then .RefreshLink, then .CopyObject to
complete this task, but receive Run-time error '3170': Could not find
installable ISAM.

Here's the code:
Set db = CurrentDb
strDir = "C:\"
strTemp = Dir(strDir & "*.csv*")

Do While Len(strTemp)
'CSV is the name of the table that I linked manually to a .csv file in
the Directory
db.TableDefs!CSV.Connect = strTemp
db.TableDefs!CSV.RefreshLink
DoCmd.CopyObject , Left(strTemp, 8), acTable, "CSV"

Debug.Print strTemp
strTemp = Dir$()
Loop
Your help would be greatly appreciated!
Ben.

[Butaambala]

For anybody interested, I discovered a solution. Syntax failed in my
second approach. The following works:

Sub mcrLinkCSV()
Set db = CurrentDb
strDir = "C:\data\"
strTemp = Dir(strDir & "*.csv*")

Do While Len(strTemp)
strFileName = strTemp
strTblName = Left(strTemp, 8)
Set tdfLink = CurrentDb.CreateTableDef(strTblName)
tdfLink.SourceTableName = strFileName
tdfLink.Connect =
"Text;DSN=CSVLinkSpec;FMT=Delimited;HDR=NO;IMEX=2; CharacterSet=437;DATABASE=C:\data\"
CurrentDb.TableDefs.Append tdfLink
Debug.Print strTemp
strTemp = Dir$()
Loop
End Sub
NOTE: Prior to this, I linked to one of the files using Get External
Data > Link Tables, specified the import parameters, then saved that
import profile (using the Advanced... button) as "CSVLinkSpec".

HTH

[Butaambala]

Another important NOTE: After I linked to a CSV file manually through
Get External Data > Link Table, I opened MSysObjects to get the
..Connect string
("Text;DSN=CSVLinkSpec;FMT=Delimited;HDR=NO;IMEX=2 ;CharacterSet=437;").

[Will]

I have done similar with Excel files recently. Here's the code: The key is the Set fol = fso.GetFolder(strPath) line.

Setup_Table ("tblX")
Set db = CurrentDb

strPath = "C:\Documents and Settings\wharmon\Desktop\Rapid ILL for Will\Backup05-06\"

' Fill the newly created table above with row and column data from the
' spreadsheet.
' Procedure: Spreadsheets for a year are (or other time frame) are put
' in a single folder. That folder cannot contain any other files.
' The contents of the folder are processed sequentially opening each
' excel file and importing its contents.

Set fol = fso.GetFolder(strPath)

For Each fil In fol.Files
Set ExcelDoc = ExcelApp.Workbooks.Open(fil)

ret = Get_Columns_and_Rows(recCnt, fldCnt, ExcelDoc)

strExcelDoc = fil

Set ExportTable = ExcelDoc.ActiveSheet.Range("A7", "AQ" & recCnt)
Set rst = db.OpenRecordset("tblX")

' For as many rows (records) as exist in the spreadsheet
For intRow = 1 To recCnt
rst.AddNew
rst!fldLender = ExportTable.Cells(intRow, 1)
rst.Update
Next intRow
ExcelApp.Workbooks.Close
Next fil


I'm rarely on these pages, so I am not likely to see your replies. Good luck.