Web Development

Ask something
Discussion Locked

Transfer Access Reports to Excel

0 helpful
4 replies

by granite2

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.Add
xlApp.Worksheets.Select (1)
DoCmd.OutputTo acOutputReport, "test",_ acFormatXLS, xlApp2
xlApp.Workbooks.Close
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.

Grant

4 replies

by joe giaquinto

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
objWorksheetTarget.Delete
.DisplayAlerts = True

Set objWorksheetTarget = objWorkbookTarget.Sheets(1)

With objWorksheetTarget
.Activate
.Range("A1").Activate
End With

Set objExcel = Nothing

End With

End Sub

by joe giaquinto

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).

by granite2

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!

This discussion is closed to new replies.