Format Excel Rows and Columns on Export

Posted on 2009-04-27
Last Modified: 2013-11-28
I am using the below code to export my data to excel. I'd like to be able to set the Excel row height and column width. Any ideas how to go about this?
Case 3  ' if Export report is selected then export to Excel

                   DoCmd.OpenReport strDoc, acViewPreview, , strWhere, acIcon

                   Reports(strDoc).FilterOn = True

                   RunCommand acCmdOutputToExcel

                   DoCmd.close acReport, strDoc

Open in new window

Question by:dglover
    LVL 18

    Expert Comment

    The only way that I know to do this is to build the .xls file via automation using vba.
    LVL 18

    Expert Comment

    Your formatting vba can be obtained by recording a macro while you're formatting an excel file. The vba in the macro can be modified and used in Excel automation in an Access module.
    LVL 18

    Accepted Solution

    The attached code snippet will build a formatted excel file with freeze set on second row with an autofilter, icon in top row with height and width set, etc
    Public Sub BuildSQLPaymentRecordsetForXL()
    Dim TSM As Double
    Dim ThsDay As String
    Dim ThisDay As String
    Dim intMaxCol As Integer
    Dim intMaxRow As Long
    Dim objXL As Object
    Dim objWkb As Object
    Dim objSht As Object
    Dim db As dao.Database
    Dim rs As dao.Recordset
            TSM = Format([Timer], 0)
            ThisDay = Date$
            ThsDay = Right([ThisDay], 4) & Left([ThisDay], 2) & Mid([ThisDay], 4, 2)
            ThsDay = ThsDay & "_" & TSM
    Set db = CurrentDb
                Set rs = db.OpenRecordset("SELECT * from qrsExcelFromPassThru;", dbOpenDynaset)
            intMaxCol = rs.Fields.Count
                If rs.RecordCount = 0 Then GoTo NoRows
                If rs.RecordCount > 0 Then
                   Set objXL = CreateObject("Excel.Application")
                   With objXL
                       .Visible = False
                       Set objWkb = .Workbooks.Add
                       Set objSht = objWkb.Worksheets(1)
                           With objSht
                              .range(.cells(3, 1), .cells(intMaxRow, _
                              intMaxCol)).CopyFromRecordset rs
                           End With
                   End With
                End If
              objSht.cells(2, 1).formular1c1 = "VendorName"
              objSht.cells(2, 2).select
              objSht.cells(2, 2).formular1c1 = "PaymentDate"
              objSht.cells(2, 3).select
              objSht.cells(2, 3).formular1c1 = "InvoiceNumber"
              objSht.cells(2, 4).select
              objSht.cells(2, 4).formular1c1 = "InvoiceAmt"
              objSht.cells(2, 5).select
              objSht.cells(2, 5).formular1c1 = "InvoiceDate"
              objSht.cells(2, 6).select
              objSht.cells(2, 6).formular1c1 = "PaymentNumber"
              objSht.cells(2, 7).select
              objSht.cells(2, 7).formular1c1 = "ERP"
              objSht.cells(2, 8).select
              objSht.cells(2, 8).formular1c1 = "Location"
              objSht.cells(2, 9).select
              objSht.cells(2, 9).formular1c1 = "VendorNo"
              objSht.cells(2, 10).select
              objSht.cells(2, 10).formular1c1 = "PaymentAmt"
              objSht.cells(2, 11).select
              objSht.cells(2, 11).formular1c1 = "AmtPaid"
              objSht.cells(2, 12).select
              objSht.cells(2, 12).formular1c1 = "AccountName"
              objSht.cells(2, 13).select
              objSht.cells(2, 13).formular1c1 = "PaySite"
              objSht.cells(2, 14).select
              objSht.cells(2, 14).formular1c1 = "PayAddress"
              objSht.cells(2, 15).select
              objSht.cells(2, 15).formular1c1 = "VoidDate"
              objSht.cells(2, 16).select
              objSht.cells(2, 16).formular1c1 = "Curr"
                objSht.Rows("1:1").RowHeight = 51
                objSht.Columns("A:A").ColumnWidth = 17.57
                objSht.Columns("A:A").ColumnWidth = 9.71
                objSht.Columns("A:A").ColumnWidth = 9.43
                objSht.Columns("D:D").NumberFormat = "#,##0.00"
                objSht.Columns("J:J").NumberFormat = "#,##0.00"
                objSht.Columns("K:K").NumberFormat = "#,##0.00"
                objSht.cells(1, 2).formular1c1 = "Payments"
                With objSht.cells(1, 2).Characters(start:=1, Length:=8).Font
                    .Name = "Arial"
                    .FontStyle = "Bold"
                    .Size = 14
                    .Strikethrough = False
                    .Superscript = False
                    .Subscript = False
                    .OutlineFont = False
                    .Shadow = False
                    '.Underline = xlUnderlineStyleNone
                    '.ColorIndex = xlAutomatic
                End With
                objSht.Rows("2:2").Interior.ColorIndex = 19
                'objSht.Rows("2:2").Interior.Pattern = solid
                objXL.ActiveWindow.FreezePanes = True
             With objXL
                .Sheets("Sheet1").Name = "Payments"
            End With
             objWkb.SaveAs ("Payments_" & ThsDay)
             Set objSht = Nothing
             Set objWkb = Nothing
             GoTo DestroyObjects
             MsgBox ("No data returned")
        Set objXL = Nothing
        Set rs = Nothing
        Set db = Nothing
        DoCmd.SetWarnings False
    End Sub

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now