[AccessD] Report to Excel

I usually export the data then format each spreadsheet.
Since each spreadsheet is standardized, that is each column might have several criterion for subtotals and then a total for all each column.  So I might have several subtotal cells then a total cell.

I can insert rows; format the subtotal with different colors and bold font, and change the header row to ensure it prints on each page.  I can also modify the page setup features.  I can copy and paste sections from one worksheet to another.  And if needed, I could put in conditional formatting.

Most of these print out on an 11X17 inch page.  Even then the print can be a little small.  This one export has 17 spreadsheets, of which 7 are formatted.

I export the data to Excel because even after I format the workbook and use "pretty" colors (I use several) the end users re-sort the data several times to fit their needs.

Initially, it took a lot of time to set the format for each worksheet.  The VP of Business Development or one of his minions was spending up to 4 hours a day formatting the data.  It now takes about a minute.

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: Tuesday, May 08, 2007 10:25 AM
To: accessd at databaseadvisors.com
Subject: Re: [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