Export Cross tab Query results to excel

Discussion in 'Access Macros' started by WilliamJ via AccessMonster.com, May 17, 2010.

  1. I am trying to refresh an Excel spread sheet with the updated results of a
    cross tab query. I need to send the data every day to a certain tab within
    the spreadsheet. I can do the macro to export to excel, but I need it to go
    to the 2nd tab. It keeps wanting to go to the first tab which is my working
    part containing all the formulas for analysis. The second tab is for the
    data.

    Any suggestions.

    I tried using Microsoft query from within Excel to pull the data but that
    only goes to the tables whereas I need Access query results.
     
    WilliamJ via AccessMonster.com, May 17, 2010
    #1
    1. Advertisements

  2. What if you specify the sheet you want to write to in the TransferSpreadsheet
    command? It should overwrite the contents of the existing sheet.

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
    "QueryName","C:\Test.xls",, "Name Of Tab to Write To"
     
    PieterLinden via AccessMonster.com, May 18, 2010
    #2
    1. Advertisements


  3. I have tried your suggestion and it will work fine exporting to the workbook
    but when I try to specify a tab or sheet in my excel workbook it fails or
    does not do anything. It will not export to a specific tab.
     
    WilliamJ via AccessMonster.com, May 18, 2010
    #3
  4. Access exports into a new tab and gives it the name of the query instead of
    adding to an existing tab.
     
    WilliamJ via AccessMonster.com, May 18, 2010
    #4
  5. WilliamJ via AccessMonster.com

    Ken Snell Guest

    See this article -- it may give you some insights on what is happening and
    what you need to do to make it work the way you want:

    Using the Range Argument of TransferSpreadsheet when Exporting Data to an
    EXCEL File (VBA)
    http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange


    --

    Ken Snell
    http://www.accessmvp.com/KDSnell/




     
    Ken Snell, May 19, 2010
    #5
  6. Thank you very much, this does give me some insight into why it won't work.
    I will try another direction.
     
    WilliamJ via AccessMonster.com, May 19, 2010
    #6
  7. WilliamJ via AccessMonster.com

    Sarita Guest

    Hi there,

    Here a suggestion that might help. Instead of exporting the crosstab query
    into excel use Microsoft query through excel to bring in the data through
    Access. I've done this before and all you have to do is set up the colums
    that will be populated. You cannot create complicated queries because those
    should be done in Access but its a great way to bring in large datasets into
    Excel.

    Open Excel>Data>Import External Data>New Database Query

    Have fun!!!

    Sarita

     
    Sarita, Jun 3, 2010
    #7
  8. WilliamJ via AccessMonster.com

    Sarita Guest

    Sorry just realize that you went down that road.

     
    Sarita, Jun 3, 2010
    #8

  9. Thanks for thinking of my problem though.
     
    WilliamJ via AccessMonster.com, Jun 3, 2010
    #9
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.