Web Development

General discussion


Transfer Access Reports to Excel

By granite2 ·
I am trying to export multiple MSAccess reports to one excel spreadsheet. I need the data in Excel for various reasons, and due to the formatting, it has to be from a report and not a query. Here is what I wrote in VB:

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open ("c:\test2.xls")
DoCmd.OutputTo acOutputReport, "Docs",_ acFormatXLS, xlApp
xlApp.Worksheets.Select (1)
DoCmd.OutputTo acOutputReport, "test",_ acFormatXLS, xlApp2
Set xlApp = Nothing

The first report, "Docs" is transferred fine, into a worksheet called "Docs". Worksheet 1 is then added and selected, but my second report "test" is not transferred. Any help would be appreciated.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by Joe Giaquinto In reply to Transfer Access Reports t ...

Option Compare Database
Private Const strPath As String = "C:\"
'Note: Create an Excel template called Report.xlt
'with one worksheet named Sheet1; save it in C
Sub Example1()

Dim objExcel As Excel.Application
Dim objWorkbookSource1 As Excel.Workbook
Dim objWorkbookSource2 As Excel.Workbook
Dim objWorksheetSource1 As Excel.Worksheet
Dim objWorksheetSource2 As Excel.Worksheet
Dim objWorkbookTarget As Excel.Workbook
Dim objWorksheetTarget As Excel.Worksheet
Set objExcel = CreateObject("Excel.Application")

DoCmd.OutputTo acOutputReport, "Docs1", acFormatXLS, strPath + "Temp1.xls", False
DoCmd.OutputTo acOutputReport, "Test1", acFormatXLS, strPath + "Temp2.xls", False

With objExcel

.Visible = True

Set objWorkbookSource1 = .Workbooks.Open(Filename:=strPath + "Temp1.xls")
Set objWorksheetSource1 = objWorkbookSource1.Sheets("Docs1")

Set objWorkbookSource2 = .Workbooks.Open(Filename:=strPath + "Temp2.xls")
Set objWorksheetSource2 = objWorkbookSource2.Sheets("Test1")

Set objWorkbookTarget = .Workbooks.Add(strPath + "Report.xlt")
Set objWorksheetTarget = objWorkbookTarget.Sheets("Sheet1")

objWorksheetSource1.Copy After:=objWorkbookTarget.Sheets(1)
objWorksheetSource2.Copy After:=objWorkbookTarget.Sheets(2)

objWorkbookSource1.Close False
objWorkbookSource2.Close False

.DisplayAlerts = False
.DisplayAlerts = True

Set objWorksheetTarget = objWorkbookTarget.Sheets(1)

With objWorksheetTarget
End With

Set objExcel = Nothing

End With

End Sub

Collapse -

by Joe Giaquinto In reply to

Tip: Go to Tools->References to VBA and set a reference to Microsoft Excel's object library (might be 9.0, 10.0 or 11.0).

Collapse -

by granite2 In reply to

Thanks Joe, I actually had a workaround that I did which was similar, only I pasted to multiple spreadsheets, then after pasting to the main spreadsheet, I deleted the others. Thanks for this!

Collapse -

by granite2 In reply to Transfer Access Reports t ...

This question was closed by the author

Related Discussions

Related Forums