troubleshooting Question

Use VBA to export Access 2007 table to an Excel worksheet, preserving formatting and layout

Avatar of JeffEdmunds
JeffEdmundsFlag for United States of America asked on
Microsoft Access
17 Comments1 Solution8618 ViewsLast Modified:
When exporting tables and queries from Access 2007 to Excel 2007 manually (right-click the file, choose export, etc...) the Wizard gives the option to "Export data with formatting and layout" and the resulting Excel file looks decent.

I need to automate the export of many Access tables and queries to Excel, so I'm using VBA code to loop through the tables and pass each to a module which performs the exports (see attached code).

The question is this: Is there a way to add something to my current code that will preserve the formatting and layout, the same way it would if I were exporting the individual tables manually?

Public Function GenerateExcelFile(ByVal exportTable As String, ByVal useHeaders As Boolean) As Boolean
    On Error GoTo ErrorRoutine

'   Declarations
    Dim currRoutine As String
    Dim ErrNote As String
    Dim timeStamp As String
    Dim ExportFilePath As String
    Dim exportFileName As String
    Dim exportTo As String
'   Initialize
    currRoutine = "GenerateExcelFile()"
    ErrNote = ""
    GenerateExcelFile = False
    timeStamp = Format(Now(), "yyyymmdd_hhmmssAMPM")

    ExportFilePath = GetSetting().ExportFilePath
    exportFileName = exportTable & "_" & timeStamp & ".xlsx"
    exportTo = ExportFilePath & exportFileName

'   Export the query/table to Excel
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, exportTable, exportTo, useHeaders
'   If it makes it this far without error, return True
    GenerateExcelFile = True
    Call OpenLocation(ExportFilePath)
    Exit Function

    Call LogError(Err.Number, Err.Description, ErrNote, currRoutine, True)
    If Err.Number = 0 Then GoTo ExitRoutine  ' Resume without error (thrown exception), causes an error
    Resume ExitRoutine

End Function
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 17 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros