[AccessD] Report to Excel

Mark,
I do this frequently.  In one export I end up with 15 worksheets and manipulate the date in Excel via Access.

I would use TransferSpreadsheet instead of what you have.
Also, put   Set appExcel = New Excel.Application  before the TransferSpreadsheet command.

If you use something like:

    DoCmd.TransferSpreadsheet acExport, , strReport1, strPathName, True
    DoCmd.TransferSpreadsheet acExport, , strReport2, strPathName, True

It will export to separate spreadsheets.

HTH

Jim 
jhewson at karta.com
 

-----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 4: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