Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009

    Unanswered: Exporting reports to MS Excel... and preserving layout?

    Hi guys

    I recently got the Office 2007 SP2 patch which enabled me to export my Access reports to Excel and PDF formats... hooray for me =)

    Is there a way that I can export more than just raw data into Excel? I'll be needing to export these data on a frequent basis -- curse the powers who refuse to receive any output other than Excel -- so it'll save me a whole lotta time if I could predefine the export format once, and each time it just replicates itself.

    By "format", I'm not merely speaking of data format as in date vs numbers vs text etc... I'm speaking of headers/footers, logo and yes, even column width and table borders.
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Using Automation you can create an instance of Excel and manipulate it the way you want. You could use an Excel template that would contain all the formats, header, footer and the like, create a new Excel file from it and fill in the data extracted from your database.

    Here is an example using this technique. I don't have time to comment it right now but do not hesitate to contact me if you need more explanations about it.
    Function Export_To_Excel(RecordID As Long)
        Const strTemplate = "C:\Documents and Settings\Sinndho\Mes documents\Access\CF_Sit\CF_Sit_2\Templates\Tpl_Acknowledgement.xls"
        Const strfilename = "C:\Documents and Settings\Sinndho\Mes documents\Access\CF_Sit\CF_Sit_2\Documents\Acknowledgement.xls"
        Dim rst As DAO.Recordset
        Dim AppXL As Excel.Application
        Dim BookXL As Excel.Workbook
        Dim CellName As
        Dim BaseName As Variant
        Dim Elements As Variant
        Dim strsql As String
        strsql = "SELECT * FROM Qry_V_Billing_Documents WHERE SysCounter = " & RecordID
        Set rst = CurrentDb.OpenRecordset(strsql, dbOpenSnapshot, dbSeeChanges)
        Set AppXL = CreateObject("Excel.Application")
        AppXL.Visible = True
        AppXL.Workbooks.Open FileName:=strTemplate
        AppXL.ActiveWorkbook.SaveAs FileName:=strfilename
        On Error Resume Next
        For Each CellName In AppXL.ActiveWorkbook.Names
            Elements = Split(CellName.RefersToLocal, "!")
            Elements(0) = Replace(Elements(0), "=", "")
            Elements(1) = Replace(Elements(1), "$", "")
            BaseName = IIf(IsNumeric(Right(, 1)), Left(, Len( - 2),
            AppXL.ActiveWorkbook.Worksheets(Elements(0)).Range(Elements(1)).FormulaR1C1 = Nz(rst.Fields(BaseName), "")
        Next CellName
        Set rst = Nothing
        Set AppXL = Nothing
    End Function
    Have a nice day!

  3. #3
    Join Date
    Jul 2009
    Thanks, that's probably what I'm looking for but didn't know how/where to get it done. Haven't checked it out yet but what you said sounds fair enough and I suppose I'll be able to get it to work.

    Cheers =)
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts