Output to a specific Excel Worksheet 2

[Molby]

Hi All,

I know how to output data to an Excel workbook using:
DoCmd.OutputTo acQuery, "Qry-Report", "MicrosoftExcel(*.xls)", "C:\Output.xls", False, ""

But is it possible to output to a specific worksheet within the workbook?

Thanks,

Ian

 

[ggriffit]

There are some samples at

http://www.greggriffiths.org/webdev/both/excel/

which may help you, although the main thrust is XL via a web interface, some of the code and links may help.

 

[sxschech]

This method will allow you to save to a specific worksheet...but it will use the Table/Query name as the name of the tab, so you would have to either manually change the tab name in excel or use an excel macro (or could run code from access using automation) to change the tab names. Additionally, if the names of the Tabs, don't match the name of the table/query, then a new tab will be created. As in example below, you can load multiple queries into excel and each query's result would be saved into its own tab with the name of that tab using the name of the query.

Note: FIRSTTAB...etc would be replaced with the name of the query or table containing the source data.

DoCmd.TransferSpreadsheet acExport, , "FIRSTTAB", "C:\TEMP\MULTITAB.XLS", True
DoCmd.TransferSpreadsheet acExport, , "SECONDTAB", "C:\TEMP\MULTITAB.XLS", True
DoCmd.TransferSpreadsheet acExport, , "THIRDTAB", "C:\TEMP\MULTITAB.XLS", True

 

[tatarrila]

Molby, try this:

I found this code in tek-tips forum (Thanks to all) and I'm working with it
It works fine if you know the cell you want .If you look for last cell the code works but you must close access before open excel.I don't know why!!!

Dim objXL As Excel.Application
Dim objWKB As Excel.Workbook
Dim objSHT As Excel.Worksheet

'Create Instance of Excel
Set objXL = New Excel.Application

With objXL
Set objWKB = .Workbooks.Open("c:\filename.xls&quot
objWKB.Application.DisplayAlerts = False
objWKB.Sheets("sheet1&quot.Cells(row, 1).Value = Now()
objWKB.Application.DisplayAlerts = True
objWKB.Save
.Workbooks.Close
.Quit
End With
Set objSHT = Nothing
Set objWKB = Nothing
Set objXL = Nothing

Antonio

 

[Molby]

Thanks sxschech, that was exactly what I was after.

Ian