alternative to TransferSpreadsheet

[Paul Dennis]

I am currently using TransferSpreadsheet to import data from an excel file,
however I need to import the data from multiple xls files rather than just
one at a time.

If I use TransferSpreadsheet it imports the data, however if I use the
command a second time it will overwrite the first lot of data. Is there
another command which will import the data and append to the specified table?

 

[Klatuu]

If you want to import multiple spreadsheets into one table, my preferred
approach is to Link to the spreadsheet rather than import it. Then use an
append query to copy the data from the linked spreadsheet table to the Access
table.

You can even automate the process if you have all the spreadsheets you want
to import in one folder using the Dir function. This can also include
filtering by name.
There is a good example of using the Dir function in VBA Help.

 

[Paul Dennis]

This would work as all my forms are in one directory. Already using the DIR
command, hence...

What would the syntax be to link the table from a module?

 

[Klatuu]

It is the standard TransferSpreadsheet, but you use acLink rather than
acImport. Everything else is the same.

One other thing. After you have completed the TransferSpreadsheet and run
the Append query, you will need to destroy the link. You do that with the
DeleteObject method:

DoCmd.DeleteObject acTabke, "LinkedTableName"

Use the name you gave as the table name in hte TransferSpreadsheet, not the
name of the spreadsheet file.

 

[Paul Dennis]

Tried it but it's not working, i.e. it's working the first time through the
loop but not the second. I noticed that the link table is not being created
the second time. Any ideas - code below.


Do While Len(strCurrFile) > 0
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "Imported
SIR", strCurrName, True, "ReqInput!"
DoCmd.OpenQuery ("ImpQ - SIR")
DoCmd.DeleteObject acTabke, "Imported SIR"
Name strCurrFolder & strCurrFile As strNewFolder & strCurrFile
strCurrFile = Dir()
Loop

 

[Klatuu]

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "Imported
SIR", strCurrName, True, "ReqInput!"

You are trying to import strCurrName, but you are assigning the value of the
file name to strCurrFile
strCurrFile = Dir()

If it is importing correctly the first time, then you are assigning the file
name and path correctly the first time. Note that Dir Function does not
return the full path. It returns only the file name, so you have to prefix
it with the path for the TransferSpreadsheet.

Assuming strPath = "C:\SomeDirectory\SomeSubDir"
Then after the call to Dir and before the TransferSpreadsheet:
strCurrName = strPath & "\" & strCurrFile