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_Li nkExternalTable s()
Set db = CurrentDb
strDir = "C:\"
strTemp = Dir(strDir & "*.csv*")

Do While Len(strTemp)

strPath = strDir & strTemp

Set tdfLink = db.CreateTableD ef(Left(strTemp , 8))

tdfLink.SourceT ableName = Left(strTemp, 8)

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

db.TableDefs.Ap pend 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!CS V.Connect = strTemp
db.TableDefs!CS V.RefreshLink
DoCmd.CopyObjec t , 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.Creat eTableDef(strTb lName)
tdfLink.SourceT ableName = strFileName
tdfLink.Connect =
"Text;DSN=CSVLi nkSpec;FMT=Deli mited;HDR=NO;IM EX=2;CharacterS et=437;DATABASE =C:\data\"
CurrentDb.Table Defs.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 "CSVLinkSpe c".

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=CSVL inkSpec;FMT=Del imited;HDR=NO;I MEX=2;Character Set=437;").

[Will]

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

Setup_Table ("tblX")
Set db = CurrentDb

strPath = "C:\Documen ts and Settings\wharmo n\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(s trPath)

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

ret = Get_Columns_and _Rows(recCnt, fldCnt, ExcelDoc)

strExcelDoc = fil

Set ExportTable = ExcelDoc.Active Sheet.Range("A7 ", "AQ" & recCnt)
Set rst = db.OpenRecordse t("tblX")

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


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