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 + "*.*"
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
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 + "*.*"
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