[AccessD] Report to Excel

Thanks Jim,

But I need to output a report...not a table or query.  I think 
DoCmd.TransferSpreadsheet will not work with a report.  The reports have a 
lot of conditional formatting that I need in the output.

Any thoughts?

Thanks,

Mark A. Matte


>From: "Jim Hewson" <JHewson at karta.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem 
>solving"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Report to Excel
>Date: Mon, 7 May 2007 16:27:24 -0500
>
>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
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
More photos, more messages, more storage—get 2GB with Windows Live Hotmail. 
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_2G_0507