Exporting To Excel

[B F Cole]

I have several tables that I want to export to Excel and have them exist on
separate worksheets within the same workbook. I'm using this code which
creates the Excel workbook. How do I cause the second output query to
create the second sheet in the same Workbook. instead of overwriting
theWorkbook and having only the last output as a worksheet?

sExcelFile = "DataTables.xls"
stDocName = "qryTable1"

' First Table to output
On Error Resume Next
DoCmd.OutputTo acOutPutQuery, stDocName, acFormatXLS, sExcelFile, 0
If Err Then
iAnswer = MsgBox("Error detected on Output 1",vbCritical)
End If

' Second Table to output
stDocName = "qryTable2"
On Error Resume Next
DoCmd.OutputTo acOutPutQuery, stDocName, acFormatXLS, sExcelFile, 0
If Err Then
iAnswer = MsgBox("Error detected on Output 2",vbCritical)
End If

etc, for more tables

Thanks for your suggestions. They have always solved a problem quickly.
Bill

 

[Guest]

Use Docmd.TransferSpreadsheet instead of the OutputTo. It has a range
argument that will allow you to specifiy a sheet within workbook. See VBA
Help for details. Help says the Range arguement does not work with acExport.
That is not correct, it does work.

 

[B F Cole]

Klatauu,

Thanks, have coded and tested it. Does exactly what I want to do...

Bill

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George