ACCESS 2003 - automate exporting ACCESS Report to EXCEL and maintaining the report's format.

How to automate exporting an ACCESS Report to EXCEL and maintaining the report's format.
cookiejarAsked:
Who is Participating?
 
danishaniConnect With a Mentor Commented:
Below a thread to look into, with some idea's;
http://www.pcreview.co.uk/forums/export-access-report-excel-keep-format-t3992097.html

You can use code as well to Format you Excel sheet in Access, see example below;
Format Excell out Access

Sub XLOut()
    Dim xlBook As Excel.Workbook
    Dim Mydb As Database
    Dim myRS As Recordset
    Dim myField As Field
    Dim i As Integer
    Dim j As Integer
    Dim xlApp As New Excel.Application
    
    Set xlBook = xlApp.Workbooks.Add

    Set Mydb = CurrentDb
    Set myRS = Mydb.OpenRecordset("qryCustomerSummary")
    
    With xlBook.Application
        With .ActiveSheet
        i = 1
        myRS.MoveFirst
        For Each myField In myRS.Fields
            With .Cells(1, i)
                .Value = myField.Name
                '.HorizontalAlignment = xlGeneral
                '.VerticalAlignment = xlBottom
                '.WrapText = True
                '.Orientation = 90
                .Font.Bold = True
                .Font.Underline = True
                With .Interior
                    .ColorIndex = 6
                    .Pattern = xlSolid
                End With
            End With
            i = i + 1
        Next myField
        
        myRS.MoveFirst
        j = 2
        Do While Not myRS.EOF
            i = 1
            For Each myField In myRS.Fields
                .Cells(j, i) = myField.Value
                i = i + 1
            Next myField
            myRS.MoveNext
            j = j + 1
        Loop
        
        With .Cells(j, i - 1)
            .Formula = "=sum(e2:e" & CStr(j - 1) & ")"
            With .Borders(xlEdgeTop)
                .LineStyle = xlDouble
                .Weight = xlThick
                .ColorIndex = xlAutomatic
            End With
            
        End With
        
        myRS.Close
        
        For j = i To 1 Step -1
            .Columns(j).EntireColumn.AutoFit
        Next j
        .Cells.EntireRow.AutoFit
           
        End With
        .Columns(5).NumberFormat = "$#,##0.00"
        
        .Visible = True
    End With
End Sub

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
<How to automate exporting an ACCESS Report to EXCEL and maintaining the report's format.>

Exporting Access Reports to Excel, never was, ...nor will it ever be, ...Perfect.

Access Reports and Excel worksheets are two totally different platforms.

Unless your report is very simple, you may never save the Format "exactly"

Furthermore "Format" means different things to different people (Fonts, Groupings, Layout, Style, Design, Structure, ...etc)
So you will have to be specific as to what "Format" means to you.

Basic code to do this is as follows:
DoCmd.OutputTo acOutputReport, "YourReportName", acFormatXLS, "C:\YourFolder\YourExcelFile.xls"


JeffCoachman

0
 
Jeffrey CoachmanMIS LiasonCommented:
See the OutputTo *Action* in the help files for more tips on making the Excel file as close to the Access Report as possible, under the "Remarks" section
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
cookiejarAuthor Commented:
On this  DoCmd.OutputTo acOutputReport, "rptMergeRpt", acFormatXLS, , False,  I get no current record message.  What would cause this?
0
 
danishaniCommented:

On this  DoCmd.OutputTo acOutputReport, "rptMergeRpt", acFormatXLS, , False,  I get no current record message.  What would cause this?

You havent specified the location, where you want to store your file and filename.
When you open the Report by itself is there any records shown?

HTH,
Daniel
0
 
Jeffrey CoachmanMIS LiasonCommented:
<On this  DoCmd.OutputTo acOutputReport, "rptMergeRpt", acFormatXLS, , False,  I get no current record message. >
Did you investigate this in the help files as I suggested?
All the info is there, including sample code
I also posted sample code in my post above...(35703691)

See here as well:
http://support.microsoft.com/kb/888635

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.