output to, transferspreadsheet, or excel automation?

[Guest]

which would work best for wanting to export data to excel, but to either
create a new worksheet in an existing workbook, or as many new sheets as are
needed?

here is what i have thus far....

For Each vrtSelectedItem In .SelectedItems
If opgModelsEx = 1 Then 'ARCOM
Set qdf = CurrentDb.QueryDefs("qryExportARIn")
qdf("iglobalId") = iExID
qdf.Execute
DoCmd.OutputTo acOutputTable, "temp", acFormatXLS, vrtSelectedItem
' DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"temp", vrtSelectedItem
DoCmd.DeleteObject acTable, "temp"
Set qdf = CurrentDb.QueryDefs("qryExportAROut")
qdf("iglobalId") = iExID
qdf.Execute
DoCmd.OutputTo acOutputTable, "temp", acFormatXLS, vrtSelectedItem

' DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"temp", vrtSelectedItem
DoCmd.DeleteObject acTable, "temp"

both outputto and transferspreadsheet work, but replace all sheets in the
workbook with the new sheet. the difference is that outputto just keeps
overwriting the original sheet, so i never get two. whereas
transferspreadsheet at least gives me two sheets.

anyway, is there a way to append do an exisiting file? without a whole lot
more effort? didn't want to go thru the excel automation, but i can if that
is the only way i suppose.

thanks!!

 

[pietlinden]

which would work best for wanting to export data to excel, but to either
create a new worksheet in an existing workbook, or as many new sheets as are
needed?

here is what i have thus far....

For Each vrtSelectedItem In .SelectedItems
If opgModelsEx = 1 Then 'ARCOM
Set qdf = CurrentDb.QueryDefs("qryExportARIn")
qdf("iglobalId") = iExID
qdf.Execute
DoCmd.OutputTo acOutputTable, "temp", acFormatXLS, vrtSelectedItem
' DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"temp", vrtSelectedItem
DoCmd.DeleteObject acTable, "temp"
Set qdf = CurrentDb.QueryDefs("qryExportAROut")
qdf("iglobalId") = iExID
qdf.Execute
DoCmd.OutputTo acOutputTable, "temp", acFormatXLS, vrtSelectedItem

' DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"temp", vrtSelectedItem
DoCmd.DeleteObject acTable, "temp"

both outputto and transferspreadsheet work, but replace all sheets in the
workbook with the new sheet. the difference is that outputto just keeps
overwriting the original sheet, so i never get two. whereas
transferspreadsheet at least gives me two sheets.

anyway, is there a way to append do an exisiting file? without a whole lot
more effort? didn't want to go thru the excel automation, but i can if that
is the only way i suppose.

thanks!!

you can use a little Excel automation to add a new sheet to your Excel
workbook and then use CopyFromRecordset to send the data.

 

[Guest]

Use the Range argument of the TransferSpreadsheet. You can specify the
worksheet name and a specific range of cells.