[AccessD] Report to Excel

Take a look at DoCmd.TransferSpreadsheet acExport,...

Basically you pass it the name of a Query, and for each differently named
query it will add another tab to the Excel file that is created.

Then to do your formatting you need to keep track of which worksheet need to
be formatted and you'll wind up manipulating a specific Excel.Worksheet
object and a Range object on that sheet.

HTH

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Monday, May 07, 2007 5:00 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Report to Excel


Hello All,

I found the code below in the archives from Jim Hale...Anyway to use this 
approach but to have 2 reports go to the same workbook...just different 
tabs?

Thanks,

Mark A. Matte

*********Code From Jim****************
Sub rpt_to_excel()
Dim appExcel As Excel.Application, strpathname As String Dim strpathnew As
String, strReport As String

strpathname = "C:\test.xls"
strpathnew = "C:\test2.xls"
strReport = "rptInvsummary"
DoCmd.OutputTo acOutputReport, strReport, acFormatXLS, strpathname

Set appExcel = New Excel.Application

'format your report
    With appExcel
        .Workbooks.Open strpathname, 0
        .Visible = True 'just to watch the sheet
        .Range("A1:G1").Select
        .Selection.Font.Bold = True
        .Selection.Font.Name = "Arial"
        .Selection.Font.Size = 12
        .ActiveWorkbook.SaveAs Filename:=strpathnew,FileFormat:=xlNormal
    End With
     appExcel.Quit
    Set appExcel = Nothing
End Sub

_________________________________________________________________
Download Messenger. Join the i'm Initiative. Help make a difference today. 
http://im.live.com/messenger/im/home/?source=TAGHM_APR07