Results 1 to 5 of 5
  1. #1
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    45

    Add records from an Access table to an existing Excel file

    Hello


    We have a linked excel spreadsheet that we would like to be able to write records to from access. Created a qy to append to the excel table, but receive error "Operation must use an updatable query".
    How do you add to an linked excel file from access?

    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you cant write directly to excel like a table, but you can export all data via docmd.transferspreadsheet.
    or
    if you dont want to delete existing records that are not in your current db, then it's a more complicated method of creating an excel object,
    open the workbook,
    move to open cell
    paste the data via copyFromRecordset,
    Save,then close the XL object.

    It's easier to just export all data at once.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    actually you can, but not by using a linked table or selecting a table to append. And may be fussy about how you are updating. not tested for an empty destination recordset for example, or for weird column headings in excel (spaces, too long, non alpha numeric characters, etc)

    create a query something like

    SELECT XL.*
    FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\myfolder\myfile.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL

    save it as say qryXL.

    note if you have a specific range then specify column/rows e,g, [sheet1$A2:B50] or [sheet1$A:H]

    now create an append query - use the query grid and select any old table and choose one of the fields to update, doesn't matter what as you will change it in the next step

    then go to the sql view, change source and destination fields to the ones in your source table and excel and change the randomly selected table to qryXL.

    Or if you know what the append query for sql looks like, just write the sql

  4. #4
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    45
    Quote Originally Posted by ranman256 View Post
    you cant write directly to excel like a table, but you can export all data via docmd.transferspreadsheet.
    or
    if you dont want to delete existing records that are not in your current db, then it's a more complicated method of creating an excel object,
    open the workbook,
    move to open cell
    paste the data via copyFromRecordset,
    Save,then close the XL object.

    It's easier to just export all data at once.

    Thank you. I believe we will export all the data.

  5. #5
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    45
    Quote Originally Posted by Ajax View Post
    actually you can, but not by using a linked table or selecting a table to append. And may be fussy about how you are updating. not tested for an empty destination recordset for example, or for weird column headings in excel (spaces, too long, non alpha numeric characters, etc)

    create a query something like

    SELECT XL.*
    FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\myfolder\myfile.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL

    save it as say qryXL.

    note if you have a specific range then specify column/rows e,g, [sheet1$A2:B50] or [sheet1$A:H]

    now create an append query - use the query grid and select any old table and choose one of the fields to update, doesn't matter what as you will change it in the next step

    then go to the sql view, change source and destination fields to the ones in your source table and excel and change the randomly selected table to qryXL.

    Or if you know what the append query for sql looks like, just write the sql


    Thank you. I believe we will export all the data

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-07-2019, 02:57 PM
  2. Replies: 1
    Last Post: 09-05-2018, 04:19 AM
  3. Replies: 11
    Last Post: 04-27-2017, 05:16 PM
  4. Import Excel file into an existing table via a macro
    By Gaby2811 in forum Import/Export Data
    Replies: 3
    Last Post: 09-23-2016, 06:12 AM
  5. Newbie Import. Update Field of Existing Records from Excel File
    By gedwards913 in forum Import/Export Data
    Replies: 8
    Last Post: 03-12-2015, 07:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums