Results 1 to 6 of 6
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Importing Excel (XP/SP2)

    Let's try asking this another way. I went back and found that there was duplicated info so eliminated that. I want to UPDATE the information that is in the Access table with that in the EXCEL spreadsheet.

    I have verified that the Excel file has the same headings as the table in Acccess.

    When I do a <pre>Sub ImportUnits()
    DoCmd.TransferSpreadsheet acImport, , "Units", _
    "S:Budget05ForestBudgetInProgressSAP UploadsUnitsEx.XLS", _
    True
    End Sub</pre>

    it does not upload and gives me a message about not being able to append. I want to UPDATE! <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    HELP!
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing Excel (XP/SP2)

    Sorry, but you can't do it that way. TransferSpreadsheet either imports or exports a file. If you have primary keys involved, you're not going to be able to overwrite the record containing that key. Updates generally require a query. Have you thought about linking the spreadsheet and then doing an update query?
    Charlotte

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Importing Excel (XP/SP2)

    Can I link and re-link (when I have updates) in VBA and do the update query? I want to "hide" all the manipulations from our customer. There will be 8 or 9 Excel files coming across.
    Alan

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

    Re: Importing Excel (XP/SP2)

    You don't have to relink when the contents of the Excel workbook change, as long as the filename stays the same. An update or append query based on the linked table will use the current data each time it is run.

    But if you prefer to work with different file names, you can relink by setting the Connect property of the DAO TableDef object. An example of the Connect property for a linked Excel table is:

    "Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:ExcelDatasheet.xls"

  5. #5
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Importing Excel (XP/SP2)

    Thanks, the Excel files will always be built from templates and always have the same name. It is easier to write an export to overwrite each time. Customer does not see a zillion files building up.
    Alan

  6. #6
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Importing Excel (XP/SP2)

    Yippee! It works! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <font face="Comic Sans MS">4 days of banging my head against the wall fixed with this one suggestion.

    Thank you! Thank you! Thank you! </font face=comic>
    Alan

Posting Permissions

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