General discussion

  • Creator
    Topic
  • #2296849

    Transfer Access Reports to Excel

    Locked

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

All Comments

  • Author
    Replies
    • #2671987

      Reply To: Transfer Access Reports to Excel

      by joe giaquinto ·

      In reply to Transfer Access Reports to Excel

      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

      • #2671986

        Reply To: Transfer Access Reports to Excel

        by joe giaquinto ·

        In reply to Reply To: Transfer Access Reports to Excel

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

      • #2671111

        Reply To: Transfer Access Reports to Excel

        by granite2 ·

        In reply to Reply To: Transfer Access Reports to Excel

        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!

    • #2671110

      Reply To: Transfer Access Reports to Excel

      by granite2 ·

      In reply to Transfer Access Reports to Excel

      This question was closed by the author

Viewing 1 reply thread