Link to home
Start Free TrialLog in
Avatar of czkredpst
czkredpst

asked on

Possible to send multiple Access Reports to individual tabs in an Excel SS, without VBA?

Hello,  I've got a little Macro that prints 8 reports one after the other, however my user wants to have them in one Excel Spreadsheet, with individual tabs for each report.  Is there a way to do this using the Design view of my Macro, or will I need to resort to Code?

I'm not an expert, so I'd like to avoid writing code, but it doesn't look like it is possible from reading the answers to similar questions...

Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

czkredpst,

You may be out of luck if you need a "easy", "Non-code" way to do this.

To do one report at a time might be tricky for you if you don't know coding.

To export to multiple sheets...
For that it gets rather complicated rather quickly, see here:
https://www.experts-exchange.com/questions/22687254/Export-Access-Data-to-Excel-Parsed-by-a-Specific-Field.html?anchorAnswerId=19467728#a19467728

If I were you I would just output each report to a separate Excel file.

You can always merge them later.

If this is what you can use, then the code is fairly straightforward.
All you need to do is:
List the Report names in a table named: tblReportList
(Which in and of itself can be mades easier with coding)
Create a form
Put a button on the form
Put this code on the buton's OnClick Event:
Dim rst As DAO.Recordset
Dim strReportName As String
Dim bytFileCount As Byte
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblReportList")
rst.MoveFirst

    Do While Not rst.EOF
        strReportName = rst!ReportName
        bytFileCount = bytFileCount + 1
        DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS, "c:\YourFolder\" & strReportName & ".xls"
        rst.MoveNext
    Loop

You may have to set a reference to the DAO library in you VBA editor, but the code does work.

Give it a try and let me know.
;-)

JeffCoachman
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you really need the Access reports in separate sheets in the same Excel file, then you can export the Report's underlying RecordSource (Table or query) to Excel.

However, in this case you will not have any of the report formatting.
This code would be something like this:
Dim rst As DAO.Recordset
Dim strQueryName As String
Dim bytFileCount As Byte
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblQueryList")
   
    rst.MoveFirst

    Do While Not rst.EOF
        strQueryName = rst!QueryName
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQueryName, "c:\YourFolder\Queries.xls"
        rst.MoveNext
    Loop
'Recordset Cleanup
rst.Close
Set rst = Nothing


Here again you would load the query name in a table called tblQueryList

Let me know

JeffCoachman
Avatar of czkredpst
czkredpst

ASKER

Thanks boag:
I will fiddle with the code from your second file.
The main issue is that the longest of my 8 reports takes 20 minutes for Access to think about, so to print them all the user can just click the button on my form before he heads home or at lunch.  
I can have a form button prompt the user to Output to separate Excel files, but it only allows you to do this one report at a time, so unless the user is at the computer during this whole time he'll probably forget what is going on.

thanks for the candid and good reply,

czkred
OK,

Post back after you have tested both approaches.

JeffCoachman