Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Export (A2K)

    Edited by HansV to remove <!t>[pre] and <!t>[/pre] tags that caused horizontal scrolling - please heed the warning you get when using <!t>[pre]!

    I'm using the following code to export data from a query to an EXISTING spreadsheet which has EXISTING worksheets in it. One of the worksheets name is "CLOSED".

    I want to export the data from the query to the CLOSED worksheet w/in the spreadsheet. However, it keeps creating a new spreadsheet and naming the worksheet to the name of the query.

    What needs to be modified in this code to achieve what I'm after?

    DoCmd.OutputTo acOutputQuery, "q_HC_Closed", acFormatXLS, "FatabasesCaseMgmtCMSCaseAlertHigh DollarCaseAlert High dollar_Master_Test.xls", False, ""

    Thanks in advance
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Excel Export (A2K)

    Rename the query to CLOSED before exporting it. If you wish, you can restore the original name afterwards:

    DoCmd.Rename "CLOSED", acQuery, "q_HC_Closed"
    DoCmd.OutputTo acOutputQuery, "CLOSED", acFormatXLS, "FatabasesCaseMgmtCMSCaseAlertHigh DollarCaseAlert High dollar_Master_Test.xls", False
    DoCmd.Rename "q_HC_Closed", acQuery, "CLOSED"

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Export (A2K)

    Thanks Hans your suggestion, it worked fine. However, I didn't fully explain everything, so I still have a problem. I have a total of 5 queries that must export to Excel. Open, Closed, Match, NoMatch, Pend.

    The Existing spreadsheet has these 5 corresponding worksheets, along with a Summary Page that has must remain in the Existing workbook.

    When I run the process to export to Excel, the process exports Closed to the existing workbook, but when Open is exported, it replaces the closed data, and so on. Also, the Summary page is removed.

    I want all 5 queries to export to their respective worksheet in the existing workbook leaving the remaining summary page intact. What do I need to do?

    As always, Hans, thanks for your assistance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Excel Export (A2K)

    You must rename each query to the name of the worksheet you want to export it to.
    (If you rename each query to "CLOSED", each will be exported to the "CLOSED" worksheet.)

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Export (A2K)

    I understand that and implemented that process as you suggested.

    CA_High_dollar_Master.xls has the following worksheets:
    Opened
    Closed
    Match
    NoMatch
    Pend
    Summary

    I want each query exported to CA_High_dollar_Master.xls (which houses the 5 worksheets), with each query's data exported to the corresponding worksheet w/in CA_High_dollar_Master.xls.

    Example:

    Docmd.output Opened acFormatXLS, "FatabasesCaseMgmtCMSCaseAlertHigh_DollarCA_High_dol lar_Master.xls", False
    Docmd.output Closed acFormatXLS, "FatabasesCaseMgmtCMSCaseAlertHigh_DollarCA_High_dol lar_Master.xls", False
    Docmd.output Match acFormatXLS, "FatabasesCaseMgmtCMSCaseAlertHigh_DollarCA_High_dol lar_Master.xls", False
    Docmd.output NoMatch acFormatXLS, "FatabasesCaseMgmtCMSCaseAlertHigh_DollarCA_High_dol lar_Master.xls", False
    Docmd.output Pend acFormatXLS, "FatabasesCaseMgmtCMSCaseAlertHigh_DollarCA_High_dol lar_Master.xls", False

    Notice each of these docmd's take the query data to CA_High_dollar_Master.xls and the worksheet tab IS named accordingly, however, each time it does, each output overwrites the previous output. When the process is complete, I will ONLY have PEND (not all 5) and no Summary.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Excel Export (A2K)

    Sorry, I should have seen that. You cannot use DoCmd.OutputTo to do what you want, it will create a new workbook each time. You should use DoCmd.TransferSpreadsheet instead:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Opened", "FatabasesCaseMgmtCMSCaseAlertHigh_DollarCA_High_dol lar_Master.xls", True

    etc. TransferSpreadsheet will add a new worksheet to the workbook if there is no sheet with the supplied name yet.

Posting Permissions

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