Link to home
Start Free TrialLog in
Avatar of Thomas Zucker-Scharff
Thomas Zucker-ScharffFlag for United States of America

asked on

Another Access question - printing

The Access database I have been working with has about 50+ reports and growing.  In the past I have printed each report separately to a PDF for the perusal of our Director.  It dawned on me that there must be a way to print all those reports by using a Macro or VBA.  So far no luck, although several websites intimated that it was fairly easy (one just ended up opening all my reports and not printing anything - the technique must only work in Access 2007 - https://www.techrepublic.com/blog/microsoft-office/create-a-macro-for-printing-multiple-access-reports/).  

The long and short of it is does anyone have a relatively easy way to print static reports from Access 2016/365?  (I have several reports that require some input, but I can print those out manually)
Avatar of Andrew Porter
Andrew Porter
Flag of United States of America image

There is a possible solution here that talks about adding a unique qualifier to the report name, and then iterating through them with a loop.
https://bytes.com/topic/access/answers/883267-how-print-multiple-reports-time

Dim obj As AccessObject
Const con_ID_PREFIX As String = "rptPer"
 
For Each obj In Application.CurrentProject.AllReports
  If Left$(obj.Name, 6) = con_ID_PREFIX Then
    'Print Report if it contains the Prefix
    DoCmd.OpenReport obj.Name, acViewNormal      
  End If
Next obj 

Open in new window

It is fairly easy.

Forget about the macro. Create a simple loop in VBA the loops the collection of names of the reports to print.
In this, print each report:

DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath, False, , , acExportQualityPrint

Open in new window

Avatar of Thomas Zucker-Scharff

ASKER

@Gustav,

I am afraid you are way beyond me.  Is that VBA (looks like it)?  

@ Andrew Trying to use your suggestion now.  I put in the code as a private sub and named it Print_All().  I then created a macro calling that function and put it in my custom ribbon.  I'm trying to call it now.  The code is:


Private Sub Print_Reports()

Dim obj As AccessObject
Const con_ID_PREFIX As String = "AECC"
 
For Each obj In Application.CurrentProject.AllReports
  If Left$(obj.Name, 6) = con_ID_PREFIX Then
    'Print Report if it contains the Prefix
    DoCmd.OpenReport obj.Name, acViewNormal
  End If
Next obj

End Sub

Open in new window


I then created a macro that calls the code
User generated image

And created an entry in my ribbon that calls the macro.
          <button id="Print_All" label="Print All AECC Reports" imageMso="PrintDialogAccess" onAction="Print_Reports" visible="true"/>

Open in new window


I just saw as I was posting that the onAction entry in my ribbon calls the wrong name, so I changed it to calling printreports instead of Print_Reports.

I just tried and got the following errors (without parentheses in the Macro):
User generated image
With Parentheses in the Macro:

User generated imagecan you tell what I'm doing wrong? (or do you need more info)

It is not very complicated:

Public Function PrintReports()

    Dim ReportNames As Variant

    Dim ReportName  As String
    Dim FullPath    As String
    Dim Index       as Integer

    ReportNames = Array("Report1", "Report2", "Report3", "ReportX")

    For Index = LBound(ReportNames) To UBound(ReportNames)
        ReportName = ReportNames(Index)
        FullPath = "C:\Test\Reports\" & ReportName & ".pdf"
        DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath, False, , , acExportQualityPrint
    Next

End Function

Open in new window

Also, macros can't call subfunctions, only functions.
@Gustav,

I am now trying your suggestion.  I'm running into a few problems.  I created a new Public Sub by using the insert procedure in the VBA editor.  I named it and pasted your code there.  I changed ReportName to the first report I want to print and put it in parentheses (it has spaces).  I didn't change anything else, although it looks like I should change "FullPath."  If I change Fullpath to the directory I want to print to it fails (maybe becase there are already files with similar names in that directory).  If I don't change fullpath, it asks where I want to sae the file and then if I give it the same name as a file already there it asks if I want to replace it. It then saves the report.  Can I change parameters to save it automatically without my interaction?  Also once I do this, do I need a line for each report (there are about 40 and growing)?
Again, a sub won't do.

Copy the code as listed.
Replace the list of report names in the array with those of yours.
Replace "C:\Test\Reports\" with the path of yours (or create this folder)
That's it.
Doing that now - Thanks
I have a problem, the line which defines the array with the report names

 ReportNames = Array("Report1", "Report2", "Report3", "ReportX")

Open in new window

gets truncated at 1024 characters and I'm not nearly finished with listing my reports.  Is there a way to list them differently (or better loop through them, since they all start with "AECC")?

ReportNames = Array("AECC 1 page - All Members by email", "AECC 1 page - Current Members Alphabeticcaly/ORCID/program", "AECC 1 page - Members / Program / Phone", "AECC 1 page - Members Alphabetically w Email & program descr", "AECC 1 page - Program Leaders", "AECC 1 page - pubs report with program descriptions", "AECC 1 page - report for publication coding", "AECC 1 page - report for publication coding large", "AECC 1 page - Verification With ORCID", "AECC 1 page All Members by Program w/ Email & Membership status", "AECC 2 page - Alphabetical listing of Members", "AECC Administration Listing", "AECC All Current Members by Membership Status", "AECC All Current Members with email", "AECC All Members Alphabetically with Email and Membership status", "AECC Alphabetical listing of Membership", "AECC Alphabetical listing of Membership with Member count", "AECC Associate Directors", "AECC Associate Members", "AECC By Program - Name/Email/Status", "AECC By Program with Secondary Program", "AECC Clinical Memb")

Open in new window

The last report name is truncated from AECC Clinical Members. and that is only the first 21 and 1/2 reports out of 61 reports.

I have several reports that ask for input, do those have to be done differently?
Is there any way to rename the reports with a number in front of them - so the first report would be 001 - <name of report>? (I was going to use Rename Master for this post-report generation, but it would be better if it was done automatically)
Then you can simply loop the collection of reports:

Public Function PrintReports()
    
    Dim ReportName  As String
    Dim Item        As Variant
    
    For Each Item In CurrentProject.AllReports
        ReportName = Item.Name
        If Left(ReportName, 4) = "AECC" Then
            FullPath = "C:\Test\Reports\" & ReportName & ".pdf"
            DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath, False, , , acExportQualityPrint
        End If
    Next
    
End Function

Open in new window

Thanks Gustav.  I am running it now and added the bolded items.  I had added "Today" in the hopes I could add a date to the path 
F:\tzucker\Documents\Membersdb reports\<today's date>\

Open in new window

but that just generated an error, so I removed that declaration in the fullpath line.

Public Function PrintReports()
    
    Dim ReportName  As String
    Dim Item        As Variant
    Dim Sequence    As Double
    Dim Today       As Date
    
    Sequence = 0
    For Each Item In CurrentProject.AllReports
        ReportName = Item.Name
        Sequence = Sequence + 1
        If Left(ReportName, 4) = "AECC" Then
            FullPath = "F:\tzucker\Documents\Membersdb reports\" & "00" & Sequence & " - " & ReportName & ".pdf"
            DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath, False, , , acExportQualityPrint
        End If
    Next
    
End Function

Open in new window




I got that the output action was canceled (after generating the 1st, fifth, and seventh reports) and debug says the error is in the Do.Cmd line (see the code I posted above)
New Code:

Public Function PrintReports()
    
    Dim ReportName   As String
    Dim Item         As Variant
    Dim Sequence     As Double
    Dim Today        As Date
    Dim msg$
    
    On Error GoTo errHandler
    Sequence = 1
    For Each Item In CurrentProject.AllReports
        ReportName = Item.Name
        If Left(ReportName, 4) = "AECC" Then
            msg$ = "Starting to print " & Sequence
            FullPath = "F:\tzucker\Documents\Membersdb reports\" & "00" & Sequence & " - " & ReportName & ".pdf"
            DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath, False, , , acExportQualityPrint
            Sequence = Sequence + 1
            msg$ = "Ending print of " & Sequence
        End If
    Next
    
errHandler:
    msg$ = Err.Number & " " & Err.Description
    
End Function

Open in new window

This time it printer the first 3 rreports and quit graciously (no error).  Although I am not sure what order it is using.  It seems to skip around, maybe the order they were created?

These are all the current reports that should be printed:

“AECC 1 page - All Members by email”, “AECC 1 page - Current Members Alphabetically/ORCID/program”, “AECC 1 page - Members / Program / Phone”, “AECC 1 page - Members Alphabetically w Email & program descr”, “AECC 1 page - Program Leaders”, “AECC 1 page - pubs report with program descriptions”, “AECC 1 page - report for publication coding”, “AECC 1 page - report for publication coding large”, “AECC 1 page - Verification With ORCID”, “AECC 1 page All Members by Program w/ Email & Membership status”, “AECC 2 page - Alphabetical listing of Members”, “AECC Administration Listing”, “AECC All Current Members by Membership Status”, “AECC All Current Members with email”, “AECC All Members Alphabetically with Email and Membership status”, “AECC Alphabetical listing of Membership”, “AECC Alphabetical listing of Membership with Member count”, “AECC Associate Directors”, “AECC Associate Members”, “AECC By Program - Name/Email/Status”, “AECC By Program with Secondary Program”, “AECC Clinical Members”, “AECC Computer Information Report”, “AECC Computer Information Report - staff”, “AECC Einstein ID (from webpage)”, “AECC Emails”, “AECC For mailing or data Export”, “AECC Labels for Member Mailings”, “AECC List by Programs Members Only”, “AECC Member list for Facility usage”, “AECC Member Name/Email/Program”, “AECC Members and Assoc Members by Program”, “AECC Members with Location”, “AECC Membership Audit 2021”, “AECC Membership Audit 2021 - Associate Members only”, “AECC Name/ email / picture report - current members”, “AECC Name/ email / picture report - full database”, “AECC Non-Compliant Publications”, “AECC Non-Compliant Publications - One PI”, “AECC Non-Compliant Publications using the CCSG”, “AECC ORCID Report”, “AECC Pictures - all current and Associate Members”, “AECC PICTURES - Members Alphabetically sorted”, “AECC Pictures - Members Programmatically sorted”, “AECC Primary & Secondary programs with status”, “AECC Program Change - BCC”, “AECC Program Listing”, “AECC Review Dates with Program”, “AECC Single Program listing”, “AECC Single Program Listing - book format”, “AECC Staff Members Listing”

Open in new window


It printed/saved aecc associate members, aecc membership audit 2021, and aecc primary and secondary programs with status.

I'm trying it now with your original code.
 Action gets canceled with the same error as before (after printing 3 reports)



ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark 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
When I run this nothing prints/saves and the function quits.

The printing starts:
User generated imagethen it generates an error:
User generated imageAnd the debug action from the dialog indicates that it is the DoCmd.OutputTo line.

The immediate window has three instances of "AECC Associate Members" - I am assuming that is coming from the debug.print reportname line
I discovered it was failing because it couldn't create the directory.  I created the directory and ran it and it saved 3 pdfs and tried to save a fourth.  IT number the first three 001, 005, 007, the fourth it tried to number 008 and never saved it.

I tried to run that last report manually and it generated an error.

User generated image




I used this code to let it create the directory if it doesn't exist

    If Len(Dir(Path, vbDirectory)) = 0 Then
        MkDir (Path)
    End If

Open in new window

Adapted that from this page: https://www.techonthenet.com/access/functions/file/mkdir.php

It still quits when it gets to that offending report.  I am going to change the name of the report and see what happens.


It is still numbering weirdly that may have something to do with where the number increment is placed - not sure.  It creates the directory and then starts creating the reports.  It saved the first four starting with 001, 005, 008, 009 and then it tried to save the next one as starting with 0010, but it didn't save it and crashed.  When I print that report manually, it prints fine.
As I thought, the placement of the incrementor solved the numbering problem, my current code is:

Public Function PrintReports()
    
    Dim ReportName  As String
    Dim Item        As Variant
    Dim FullPath    As String
    Dim Path        As String
    Dim Sequence    As Integer
    Dim namevalue   As String

    Path = "F:\tzucker\Documents\Membersdb reports\" & Format(Date, "yyyy-mm-dd") & "\"
    
    If Len(Dir(Path, vbDirectory)) = 0 Then
        MkDir (Path)
    End If
    
    Sequence = 1
    For Each Item In CurrentProject.AllReports
        ReportName = Item.Name
        If Left(ReportName, 4) = "AECC" Then
            namevalue = "00" & Sequence & " - "
            FullPath = Path & namevalue & ReportName & ".pdf"
            Debug.Print FullPath
            DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath, False, , , acExportQualityPrint
            Sequence = Sequence + 1
        End If
    Next
    
End Function

Open in new window


It still only outputs the first 3 reports and stalls on number four, which can be printed manually.

This is the output from the immediate window:

F:\tzucker\Documents\Membersdb reports\2021-08-05\001 - AECC Membership Audit 2021.pdf
F:\tzucker\Documents\Membersdb reports\2021-08-05\002 - AECC Primary & Secondary programs with status.pdf
F:\tzucker\Documents\Membersdb reports\2021-08-05\003 - AECC All Members Alphabetically with Email and Membership status.pdf
F:\tzucker\Documents\Membersdb reports\2021-08-05\004 - AECC 1 page All Members by Program w/ Email & Membership status.pdf


Open in new window

I added the code:

NewString = Right(ReportNamem Len(ReportName) - 5)

Open in new window

So that the output removed the characters "AECC " from the beginning of every report.

I just ran it again and it crapped out when trying to print the "1 page All Members by Program w/ Email & Membership status" report (number 005 in its sequence - what ever that is).
The filename must be valid, thus - among other characters - it can't hold a slash. Try using:

FullPath = Path & namevalue & Replace(ReportName, "/", "_") & ".pdf"

Open in new window

Thanks!  Running it now.

I used the following change:

            ReportName = Replace(ReportName, "/", "_")
            NewReportName = namevalue & Right(ReportName, Len(ReportName) - 5)
            FullPath = Path & NewReportName & ".pdf"

Open in new window

It still failed to print the report that has the / in it.  


Addendum:  I changed the name of the report that was causing the problem to AECC 1 page all members by program with email & status from AECC 1 page all members by program w/ Membership email & status.  The report saved now it has stopped at the next report name "AECC 1 page - Current Members Alphabetically/ORCID/program", the script renames it to "1 page - Current Members Alphabetically_ORCID_program."   Does another change have to be made in the code, or do I need to go through my report names and change anyone that has a "/" in it?

I am trying to change this next reports name and see what happens

Then rename it to something simple or, for a test, exclude it - by renaming it for a while.
It keeps crashing on the reports that have a slash in their name (even though it says in the debug output that it has been replaced.  Maybe the value ReportName needs to be replaced in the DoCMD.Output line?
yep it crashed on the next report with a slash - there aren't that many so I'm changing their names.

Thanks to everyone who helped!!!!

I changed the code and it works ( I added the code to autonumber it while padding it as well as code to check if a file exists and skip it if it does - both things eventually worked and now the script below will generate all reports that start with the first 4 characters noted (in this case "AECC").  There are a few glitches I haven't figured out yet:

  • I can't figure out what order the reports are being printed in, the script prints them in the same order each time, just not sure where that comes from
  • Interactive reports still require interaction, but ones that I would normally run more than once only run once
  • I originally had this note in the script:
' -----------------------------------------------------------------------
' IMPORTANT!! - run Rename Master after running this with either
'   access reports renaming batch
'   or by doing the following:
'       13 add by position - add " - " (without quotes) to the beginning (character 1)
'       15 add a counter padded to 3 digits to the beginning
' then rename
' -----------------------------------------------------------------------
  • I have taken it out since the script now numbers the reports (previously I sorted them alphabetically and then numbered them using the script, which gave me more control of how they were numbered.

Public Function PrintReports()
    
    Dim ReportName      As String
    Dim Item            As Variant
    Dim FullPath        As String
    Dim Path            As String
    Dim Sequence        As Integer
    Dim namevalue       As String
    Dim NewReportName   As String
    Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
    
    'designate the directory to save the report output into
    Path = "F:\tzucker\Documents\Membersdb reports\" & Format(Date, "yyyy-mm-dd") & "\"
    
    'Make a new direcotry if it does not exist
    If Len(Dir(Path, vbDirectory)) = 0 Then
        MkDir (Path)
    End If


    Sequence = 1
    For Each Item In CurrentProject.AllReports
        ReportName = Item.Name
        If Left(ReportName, 4) = "AECC" Then
            namevalue = Format(Sequence, "000") & " - "
'           remove "/" from reportname replacing with "_"
            ReportName = Replace(ReportName, "/", "_")
'           removes "AECC " from beginning of report name and adds numbering (001 - 100) and " - "
            NewReportName = namevalue & Right(ReportName, Len(ReportName) - 5)
            FullPath = Path & NewReportName & ".pdf"
            ' Check to see if the file exists, if so go to the nextfile, if not continue (all fso statements came from an answer to another question on stackoverflow.com)
            If fso.FileExists(FullPath) Then GoTo NextFile 
            DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath, False, , , acExportQualityPrint
'           increment counter by 1
            Sequence = Sequence + 1
        End If
NextFile:
    Next


End Function

Open in new window








Thanks for the feedback.
Have a nice weekend!
I found that the reports were not printing in the same sequence each time - I found this out when I put the code to check for files that already exist and tested it to find that it was not skipping the file I had created because it had a different name (001 - Administration listing.pdf was not the same as 001 - Membership Audit 2021.pdf, so it created the new file), so I changed the code to read:

Public Function PrintReports()
    
    Dim ReportName      As String
    Dim Item            As Variant
    Dim FullPath        As String
    Dim Path            As String
    Dim Sequence        As Integer
    Dim namevalue       As String
    Dim NewReportName   As String
    Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
    
    'designate the directory to save the report output into
    Path = "F:\tzucker\Documents\Membersdb reports\" & Format(Date, "yyyy-mm-dd") & "\"
    
    'Make a new direcotry if it does not exist
    If Len(Dir(Path, vbDirectory)) = 0 Then
        MkDir (Path)
    End If

'   Sequence = 1 (used with below statements to use an incremental numbering in front of file name)
    For Each Item In CurrentProject.AllReports
        ReportName = Item.Name
        If Left(ReportName, 4) = "AECC" Then
'           namevalue = Format(Sequence, "000") & " - "
'           remove "/" from reportname replacing with "_"
            ReportName = Replace(ReportName, "/", "_")
'           removes "AECC " from beginning of report name and adds numbering (001 - 100) and " - "
'           NewReportName = namevalue & Right(ReportName, Len(ReportName) - 5)
            NewReportName = Right(ReportName, Len(ReportName) - 5)
            FullPath = Path & NewReportName & ".pdf"
            ' Check to see if the file exists, if so go to the nextfile, if not continue
            If fso.FileExists(FullPath) Then GoTo NextFile
            DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath, False, , , acExportQualityPrint
'           increment counter by 1
'           Sequence = Sequence + 1
        End If
NextFile:
    Next


End Function

Open in new window

which removes the sequential numbering from the front of the file name.  I then use rename master to append a sequence (001 - 100) followed by " - " (without the quotes - so the first file starts 001 - ) after alphabetizing the reports.