Linking multipule workbooks in Excel to Access

[pat26]

Hi everybody,

My boss asked my to create an automated procedure to link about 200 Excel workbooks all the same form into one Access table. The workbooks are all in the same Directory.

I started with same code but I can't get it done.

DirName = "P:\My Documents\test\"
FileName = Dir(DirName + "*.*&quot
Do Until FileName = ""
'overwrites the name
Name DirName + FileName As DirName + "commodityData.xls"

'it doesn't work with just an appendquery
DoCmd.OpenQuery "qapCommodity"

Kill DirName + "commodityData.xls"
FileName = Dir

'until all the woorkbooks are done
Loop

DoCmd.Close


May be some can provide me with a better alternative or help me with same code example.

thanks very much in advance and Merry Christmas

Patrick

 

[hilbertl]

Use docmd.TransferSpreadsheet aclink

 

[pat26]

Thanks hilbertl you got me on the right track. This is what I did

Dim path As String
Dim FileName As String
Dim file As String


path = "P:\my Documents\test\"
FileName = Dir(path + "*.*&quot

Do Until FileName = ""

Name path + FileName As path + "commodityData.xls"

file = path + "commodityData.xls"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblDevelopmenttest", file, True, "A1:S7"


Kill path + "commodityData.xls"
FileName = Dir

Loop