asked on

multiple access reports to one excel workbook

I am using access 2003 and excel 2003. I am trying to export several access reports to one excel workbook using visual basic code. I have tested with 2 so far and when it runs the workbook only has one report, the last one. Is there a way to have multiple reports export to one excel workbook?

Public Sub Master()
DoCmd.OutputTo acOutputReport, "TAX", acFormatXLS, "c:\Master.xls"
DoCmd.OutputTo acOutputReport, "SPEND", acFormatXLS, "c:\Master.xls"
End Sub

TextReport

Use the transferSpreadsheet rather than the OutputTo

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Tax", "c:\Master.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Spend", "c:\Master.xls", True

Cheers, Andrew

pgmtkl

ASKER

When i do the transferspreadsheet it does not keep the report formatting. Is there a way to export reports?

TextReport

The OutputTo always overwrites the existing file so the only alternative is to use TransferSpreadsheet or ....

Create a Main Report and include both reports in one ... but that would create 1 sheet and not 2.

The only other way would be use OutputTo to seperate files and then using VBA combine the spreadsheets.

Cheers, Andrew

pgmtkl

ASKER

When you output yo separate files how do you use VBA to combine spreadsheets? I am not familirar with this vb.

TextReport

The following function will either import the specified sheet from strXLSFROM to strXLSTO

strSheetName The name of the sheet to copy or "" to copy all sheets.
strXLSFROM the full path and filename the sheet is in
strXLSTO the full path and filename to copy the sheet(s) to

?ImportSpreadsheet("SheetName","c:\test.xls","c:\aj.xls")
?ImportSpreadsheet("","c:\test.xls","c:\aj.xls")

Cheers, Andrew

Function ImportSpreadsheet(strSheetName As String, strXLSFROM As String, strXLSTO As String)
Dim objExcel As Object      ' New Excel.Application
Dim objWorkBookFrom As Object   ' Excel.Workbook
Dim objWorkBookTo As Object   ' Excel.Workbook
Dim objSheet As Object      ' Excel Sheet
Dim cnt As Long
 
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkBookFrom = objExcel.Workbooks.Open(strXLSFROM)
    Set objWorkBookTo = objExcel.Workbooks.Open(strXLSTO)
    
    If strSheetName <> "" Then
       objWorkBookFrom.Sheets(strSheetName).Copy After:=objWorkBookTo.Sheets(objWorkBookTo.Sheets.Count)
    Else
       For cnt = 1 To objWorkBookFrom.Sheets.Count
           objWorkBookFrom.Sheets(cnt).Copy After:=objWorkBookTo.Sheets(objWorkBookTo.Sheets.Count)
       Next cnt
    End If
    
    objWorkBookFrom.Close
    objWorkBookTo.Save
    objWorkBookTo.Close
    objExcel.Quit
    
    MsgBox "Import Completed"
    
    ImportSpreadsheet = True
End Function

Open in new window

pgmtkl

ASKER

This code, is it in excel? is there a way to export multiple access reports using the docmd transfer to a excel template?

ASKER CERTIFIED SOLUTION

TextReport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pgmtkl

ASKER

ok thanks. that seems to work. what if i wanted to add more sheets. would i add another docmd. line? also do i have to change function code?

TextReport

Yes and another ImportSpreadsheet, you could keep repeating the 2 lines, modifying the name of the report you want to output but you can keep the same file name.

DoCmd.OutputTo acOutputReport, "SPEND", acFormatXLS, "c:\Master1.xls"
ImportSpreadsheet "", "c:\Master1.xls", "c:\Master.xls"

Cheers, Andrew

pgmtkl

ASKER

ok thanks. i understand. if i want it to stop running after it exports where do i add that statement?

TextReport

Sorry I don't inderstand.

pgmtkl

ASKER

to automate hitting ok for message box- does that go in spreadsheet function?

SOLUTION

TextReport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pgmtkl

ASKER

thanks. this works but i have an additional question. Is there a way to export multiple access queries to a formatted excel workbook. The reports works ok but then i loose the heading format that is in excel. ex: export tax, spend, and finance queries to a excel workbook template with sheets tax,spend, and finance. Is there a way to do this and keep the first 3 rows as headers?

TextReport

Given enough time you can write it to do almost anything, I suggest you raise another question for this as it is a separate issue.
Cheers, Andrew