[AccessD] Report to Excel_

Thanks A.D.,

I actually got the reports to export exactly like I need.  The results are 
in 2 files.  I'm trying to put them in the same workbook.  The below code 
works every other time,  but excel gets stuck in memory and I have to go 
kill it.  The times it does not work I get the error "RPC Server not 
available" .  The error occurs on the line where I copy the sheet.

Any thoughts?

Thanks,

Mark

****************CODE*****************
Set appExcel = New Excel.Application

    With appExcel
        .Workbooks.Open "c:\test", 0
        .Workbooks.Open "C:\test1.xls", 0
         Sheets("rptTest").Copy before:=Workbooks("test.xls").Sheets(1)

        .ActiveWorkbook.Save
        .ActiveWorkbook.Close
        .ActiveWorkbook.Save
        .ActiveWorkbook.Close
    End With
     appExcel.Quit
     Set appExcel = Nothing

>From: "A.D.TEJPAL" <adtp at hotmail.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: Tue, 8 May 2007 23:12:29 +0530
>
>Mark,
>
>     Reports sent directly from Access to Excel are known to suffer from 
>the following shortcomings, making the outcome rather un-acceptable:
>     (a) Substantial loss of alignment and formatting.
>     (b) Labels and lines are lost.
>     (c) Unwanted depiction of actual names of controls, cluttering up the 
>overall display.
>     (d) Group footers do not get transferred properly. There is partial or 
>total loss of content.
>     (e) Back colors (if any) of controls and sections are lost.
>
>     Chronic limitations outlined above, can be overcome  by routing the 
>report via Word and performing final dressing up and formatting within 
>Excel application, which happens to be well suited to perform such a task. 
>With this, the Excel report attains a look basically as good as that of a 
>normal Access report.
>
>     My sample db named Reports_AccessToExcelAndWord, demonstrates export 
>of Access reports to Excel, with proper alignment and  formatting, 
>including all group headers/footers positioned appropriately, along with 
>lines and back colors. Final version of Excel report also gets saved in 
>word document format.
>
>     This sample db (in Access 2000 file format) is available at Rogers 
>Access Library (other developers library). Link - 
>http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D.
>
>     References - Excel 9.0 Object Library, Word 9.0 Object Library, DAO 
>3.6, Microsoft Scripting RunTime.
>
>     If you wish to pursue further as per the approach outlined above, you 
>might like to first try out the sample db as it is (without making any 
>modification) and confirm whether it works smoothly at your end.
>
>Best wishes,
>A.D.Tejpal
>
>   ----- Original Message -----
>   From: Mark A Matte
>   To: accessd at databaseadvisors.com
>   Sent: Tuesday, May 08, 2007 22:25
>   Subject: Re: [AccessD] Report to Excel_
>
>
>   Thanks Jim,
>
>   I need to export a report because of the conditional formatting.
>   TransferSpreadsheet won't work with reports.
>
>   Any thoughts?
>
>   Thanks,
>
>   Mark A. Matte
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
PC Magazine’s 2007 editors’ choice for best Web mail—award-winning Windows 
Live Hotmail. 
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_pcmag_0507