Unanswered: Exporting reports to MS Excel... and preserving layout?
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
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 Excel.name
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
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(CellName.name, 1)), Left(CellName.name, Len(CellName.name) - 2), CellName.name)
AppXL.ActiveWorkbook.Worksheets(Elements(0)).Range(Elements(1)).FormulaR1C1 = Nz(rst.Fields(BaseName), "")
Set rst = Nothing
Set AppXL = Nothing