CODEWITHSUNDEEP
excelms-accessvba
Coder014
Coder014

Reputation: 41

Export of a report in Access 2013 VBA to Excel works wrong, different from built-in export

Was wondering if anyone has encountered this problem. I'm working on an Access 2013 database which produces a couple of reports. These reports need to be exported into Excel files. I do this in VBA by using 

DoCmd.OutputTo acOutputReport, "ReportName", acFormatXLS, "fileName.xls"

The files are created, however, their contents don't look as expected, especially when compared to the resulting XLSX files obtained by running the built-in Access' "External Data"->"Export to Excel spreadsheet" function.
For example, in one of the files, the rows of the table obtained by running report's row source query do not advance, resulting in multiple repetitions of the same row. In the other one the formatting is wrong.
Again, when exporting using the Access built-in "Export to Excel" function, the results are correct.
Question: is there an alternate method of exporting reports to Excel? Am I doing something wrong?
Thanks!

EDIT: just to clarify: the formatting is only a secondary problem. The data itself is saved wrong. The main problem is that starting with a certain row, the rest of the rows are only copies of that row, they do not advance.

Upvotes: 0

Views: 5252

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8414

There is no "good" way to do it in Access, unfortunately. You can either export it to PDF and make it look like the report does in Access, or you can export it using acOutputReport and then run some code to reformat it in Excel.

DoCmd.OutputTo acOutputReport, "ReportName", acFormatXLS, "fileName.xls" 
Call ModifyExportedExcelFileFormats("filename.xls", "SheetName")

And then:

Public Sub ModifyExportedExcelFileFormats(sFile As String, sSheet As String)
On Error GoTo Proc_Error

Dim xlApp As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)

   With xlApp
      .Application.Sheets(sSheet).Select
      .Application.Rows("1:1").Select
      .Application.Selection.Font.Bold = True
      .Application.range("A1").Select
      .Application.Selection.AutoFilter
      .Application.Cells.Select
      .Application.Selection.Columns.AutoFit
      .Application.range("A1").Select
      .Application.Activeworkbook.Save
      .Application.Activeworkbook.Close
      .Quit
   End With

Exit_Proc:
Set xlApp = Nothing
Set xlSheet = Nothing
Exit Sub

Upvotes: 0

Related Questions