[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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.
0
cookiejar
Asked:
cookiejar
  • 3
  • 2
1 Solution
 
danishaniCommented:
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 CoachmanCommented:
<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 CoachmanCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now