Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import from Excel (Access 2000)

    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 ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import from Excel (Access 2000)

    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>post 305,390</post:> 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).

  3. #3
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import from Excel (Access 2000)

    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 ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import from Excel (Access 2000)

    As I mentioned in my previous reply, the code from <post#=305,390>post 305,390</post#> 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •