Excel Export (A2K)

Topic

New Reply

Edited by HansV to remove

 and 

tags that caused horizontal scrolling – please heed the warning you get when using !

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, “F:DatabasesCaseMgmtCMSCaseAlertHigh DollarCaseAlert High dollar_Master_Test.xls”, False, “”

Thanks in advance

Reply | Quote

Replies

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, “F:DatabasesCaseMgmtCMSCaseAlertHigh DollarCaseAlert High dollar_Master_Test.xls”, False
DoCmd.Rename “q_HC_Closed”, acQuery, “CLOSED”

Reply | Quote

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.

Reply | Quote

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.)

Reply | Quote

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, “F:DatabasesCaseMgmtCMSCaseAlertHigh_DollarCA_High_dollar_Master.xls”, False
Docmd.output Closed acFormatXLS, “F:DatabasesCaseMgmtCMSCaseAlertHigh_DollarCA_High_dollar_Master.xls”, False
Docmd.output Match acFormatXLS, “F:DatabasesCaseMgmtCMSCaseAlertHigh_DollarCA_High_dollar_Master.xls”, False
Docmd.output NoMatch acFormatXLS, “F:DatabasesCaseMgmtCMSCaseAlertHigh_DollarCA_High_dollar_Master.xls”, False
Docmd.output Pend acFormatXLS, “F:DatabasesCaseMgmtCMSCaseAlertHigh_DollarCA_High_dollar_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.

Reply | Quote

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”, “F:DatabasesCaseMgmtCMSCaseAlertHigh_DollarCA_High_dollar_Master.xls”, True

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

Reply | Quote