Access export report with column formatting

[frank12345]

Hello all,

I am trying to export an access report i had created with the exportwithformatting macro; the problem is the export happens but the excel file that it exports into does not seem to keep the column formatting from my query.

I framed a code but am still having issues: any suggestions on the below code is greatly appreciated.

Public Sub Workbook_receiveRCFormats()
 
  Dim acOutputReport As Object
  Dim acTableRow As Object
  Dim acOutputReport As String
  Dim xlFileName As String
 
  Dim acFormat As acFormat
 
  Dim Index As Long
 
  Dim NoItems As Long
  Dim I As Long
 
 
  Set acContainer = ThisWorkbook.Container
  Set acReport = acContainer.Reports("RC_FORMATS").Report
  For Each acReportRow In acReport.Rows
    Index = acReportRow("INDEX")
    NoItems = acRCFormats.Count
    If Index <= NoItems Then
      Set acRCFormat = acRCFormats.Item(Index)
    Else
      For I = NoItems + 1 To Index
        Set acRCFormat = New acRCFormat
        acRCFormats.Add acRCFormat
      Next I
    End If
    acRCFormat.setFormat acReportRow
  Next acReportRow
 
    acOutReportData = "rpt-FINANCE"
    xlFileName = "C:\Documents and Settings.xls"
 
    DoCmd.OutputTo acOutputReport, outReportData, acFormatXLS, xlFileName
 
End Sub

Thanks
Frank

[zmbd]

frank12345:

If I understand correctly, you want to keep the report's formating when it goes to excel, yes?

MS in the wisdom took that away... also for word. Only in the snapshop and pdf versions will keep your formating.

You might first attempt the transferspreadsheet method. This will require the use of a stored query; however, it can take parameters upon the call. There are several posts on site about this... basic information: DoCmd.TransferSpreadsheet Method (Office 2010)

The only other option is using automation. Please read thru the following link. It starts with Outlook; however, goes on to some basic Excel stuff.
Application Automation
It is a lot of work... each record has to be read out and then the column formatting can be set etc....

Read thru both and see which way you want to go.

It maybe that a combination of the two is the way to go.