Import from Excel (Access 2000)

Topic

New Reply

I have a function for importing an excel file into a ready table.However I want to change the names in the tables and this function does not allow me to do it. The function is :
Dim strFile As String
strFile = “C:BESpots.xls”
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel97, _
TableName:=”Limits”, _
FileName:=strFile, _
HasFieldNames:=True

For example, the first coloumn in excel is called Trucks and I want to import that coloumn in the field called Transport.How can I change my function ?

Reply | Quote

Replies

TransferSpreadsheet doesn’t let you rename columns. The easiest way to change the name of the field is to do so manually after importing the table.

If you really need to do it all in code, you could use an append query to transfer the records from the imported table to an already existing table with the correct field names, or use the code from post 305,390 to rename the field after importing the table. For the latter, you need to set a reference to Microsoft ADO Ext. 2.8 for DDL and Security in Tools | References (the number 2.8 might be different on your PC).

Reply | Quote

Much obliged, of course i would prefer to rename the field in the table, but is it possible? i mean by code, since all my commands will be automated ?

Reply | Quote

As I mentioned in my previous reply, the code from post 305,390 can be used to rename the field in the table after it has been imported.

It would be even simpler, of course, if the Excel worksheet had the correct field names. You wouldn’t have to rename it in Access.

Reply | Quote