Results 1 to 6 of 6
  1. #1
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91

    Export table to Spreadsheet, replace data in named TABLE, not range or Sheet Name

    Hello,



    I am trying to expand my automation of exported tables into formatted rpoerts. I would prefer to keep an existing table name in Excel, but have it completely replaced (all data) with new data each time this export runs.

    What part, if any, of the actransferspreadsheet method makes this possible? I have replaced a sheet name with a table name, but it just creates that name as a new sheet.

    Example:
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_BUILD_REPORT", "\\redactedservername\Programs\(..Scorecard\Cycle time\RAW DATA\" & "PASTE_DATA_INTO_REPORT", True, "Test_Table"
    Do I need to first reach out and delete all rows in said table and call it with a different method?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    transferspreadsheet simply writes to the sheet, starting in A1.
    theres no altering this.
    if the existing sheet has 100 rows, then you export 50 new rows, the result will be 50 new rows and 50 old data rows for 100 rows.

    XL tables are defined as R rows, and C columns. How do you know your data will have that exact size?
    you could using VB , open the XL sheet,
    erase the data,
    load the new data
    and redefine the table dimension.

    Is this what you want?

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

  4. #4
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Quote Originally Posted by ranman256 View Post
    transferspreadsheet simply writes to the sheet, starting in A1.
    theres no altering this.
    if the existing sheet has 100 rows, then you export 50 new rows, the result will be 50 new rows and 50 old data rows for 100 rows.

    XL tables are defined as R rows, and C columns. How do you know your data will have that exact size?
    you could using VB , open the XL sheet,
    erase the data,
    load the new data
    and redefine the table dimension.

    Is this what you want?
    You are understanding my need correctly. Unfortunately, it sounds like a direct means of having more functionality out of an Excel table rather than a range or having data always have same dimensional size, is not going to work. Researching the post below...

  5. #5
    mindea is offline Novice
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    11
    What I usually do is create an extra Access database outside of my main .accdb called something like reports.accdb. From my main accdb I run TransferDatabase to export my data as a table to reports.accdb. When I design my Excel workbook, I "attach" (Get Data | From Database | Microsoft Access) the desired table(s) from the reports.accdb. Then I build pivot tables and charts using that data. Each time Excel opens, I can "refresh" the data, which requeries the table in reports.accdb.
    This process works great. I usually do this with an Excel .xltm template, then I have code in Access to automatically export data to reports.accdb, launch the Excel template, refresh it with the latest data, save it as a xlsx file, attach it to an email and send it to a recipient. I use this method to send out monthly salesman reports to each of 30 salespeople.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have used the technique at that link and it worked well.
    If you're making charts out of the data, base them on a dynamic named range. Possibly the same goes for pivot tables as well, but it's been a long time since I played with those.
    You say you're making reports, but we don't know what they contain. A simple report is probably better in Access.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Automate Export of a table to an Excel Spreadsheet
    By RayMilhon in forum Programming
    Replies: 3
    Last Post: 01-23-2018, 08:08 PM
  2. Replies: 6
    Last Post: 11-16-2017, 06:53 PM
  3. Replies: 2
    Last Post: 12-12-2014, 04:42 PM
  4. Replies: 3
    Last Post: 08-15-2012, 04:15 PM
  5. Replies: 4
    Last Post: 06-10-2012, 02:29 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