Vba to print pdf Results

Hello, first off I am a new member and this my first post. I did a search and found a few results but haven't been able to stitch them together.

So here is what I am trying to accomplish. I am using Access 2010. I import data into a table with numerous records. I then run a report that puts the records into a standard form. Currently when the form creates it lumps them into a multipage document. What I would like to do is have a code that will print to single page PDF. Whats more I would like it to name the document using the date a record entry of the persons name. Lastly save said PDFs to a designated folder.

Variables:

Table name: Phone
Record to name PDF: Agent Evaluated
Report name: Phone eval

Any help would be highly appreciated. Thank you all for your time.

Hello, first off I am a new member and this my first post. I did a search and found a few results but haven't been able to stitch them together.

So here is what I am trying to accomplish. I am using Access 2010. I import data into a table with numerous records. I then run a report that puts the records into a standard form. Currently when the form creates it lumps them into a multipage document. What I would like to do is have a code that will print to single page PDF. Whats more I would like it to name the document using the date a record entry of the persons name. Lastly save said PDFs to a designated folder.

Variables:

Table name: Phone
Record to name PDF: Agent Evaluated
Report name: Phone eval

Any help would be highly appreciated. Thank you all for your time.

Hi all,

Please help me with the issue mentioned in the subject. I have a report in an access database which has about 400 pages which I have to split and export to multiple pdf files.
In the same database I have a table with 3 columns:
- the first column contains the first page of the pdf file
- the second column contains the last page of the pdf file
- the last column contains the title of the pdf file

Eg: the report will be split in 3 pdf files:
- one that starts at page 1(first column of the table) and continues to page 112(second column of the table) and is named Document1.pdf(3rd column of the table)
- the second starts at page 113, continues to page 350 and is named Document2.pdf
- the third starts at page 351 until page 400 and is named Document3.pdf


Many thanks!

Hello everybody,

I have a program in VBA which opens and prints attachments. When a user wants to print an attachment, I want the attachment to be printed without Adobe opening in the background and the taskbar. The printing is no problem, but how do I prevent Adobe from opening?

Thank you

hi, i am trying to create a macro that automatically opens, then prints a pdf object from within a excel spreadsheet. i can open the file but i cannot manage to get it to print, can anybody help, pleeeeeease!!

Hi All,

I just got in to a another problem with my report and I hope one of you kind people can help me to solve this problem. I have created a access report for a invoice and I have placed few command buttons on the report header like Print to PDF , Print to Invoice and Print Preview. Well, first two buttons working fine and I am stuck with the third button. I want to convert the already open report (in Report view) to print preview. how can I achieve this using VBA. Please Help

I print an invoice by opening a formatted Access Report that uses some "On Load" events to select appropriate graphics and notations on the invoice (different logos and such). I currently manually print this to PDF (via PDFCreator) and file the PDF in an "Invoice" folder, giving it the Invocie Number as it's docuemnt name, e.g. 12345.pdf I can then fire an email via VBA to Outlook and pick up the PDF version of the invoice from the Invoice Folder, attaching it to the email.

THis all works fine, but I would like to automate it a little more. Instead of manually printing the PDF file from the Access Invoice document, I would like to initiate the print via VBA and have VBA tell the system what folder to save the PDF to, and then create the email and attach the PDF Invoice.

I have most of the steps already in place, but can any one help me with the VBA code that will open the Access Report (the invoice), load the "on-load event items, print it to PDF (via PDFCreator) and save the PDF in a designated folder on the C:Drive.

I have already ticked the PDFCreator item in the Tools - References section of the code window.

Hi!
I have to generate a catalogue from an access application.

This catalogue must be in a single .pdf file, and in single pdf file, but there are different reports to print.
I can use Adobe Acrobat to create pdf file, using VBA.

The problem is:
- How autosave the file xxx.pdf without dialog window ?
- How add a report to an existing file .pdf?

Do you know some link, or information that can help me?

Thanks!!!

Letizia

How do I suppress the "Save to PDF file" dialog box when sending a print job to the default printer Acrobat Distiller? I'm using Access 2000, ADO, VBA. Would someone please provide me a code sample that will allow me to print a automatically name file name to a designated path without having to deal with the above referenced dialog box?

Any alternate ideas if my first objective is not possible.

Steve

Hi,

Would it be possible to print a report from access in PDF format to location "c:temp" I know this can be done manually by selecting printer "Adobe PDF" but could someone help me todo this via VBA.

Idea is that I have this form and when I press the Print button, the report is generated in PDF.

Best regards,

Paul
From Holland.

Ok, so I am a very novice user of Access and I have some ability to code in VBA, but not much.

A little background: I did not create this Database, I inherited it. This database is used to generate EPA documentation for thousands of requests a year. It imports the data from an excel sheet, and after the import is done, I click Print on the form and the printer prints out the documentation. I then Fax the paperwork to where it needs to go. This is time consuming and uses tons of paper.

I have 2 issues that I would like changed:

1. How can I have it print all 4 reports at once? Right now it treats the printing as 4 different print jobs, and the printer that I use puts an extra sheet inbetween the print jobs with the user name since multiple people use the printer.

2. When I click the print button, I want the report saved as a PDF file. I don't care if it opens a Save As dialog box, in fact I think I prefer it so that I can name it what I want and put the fine where it needs to go. But I don't know how to make the code work.

Here is what I have right now:

Private Sub PrintForms_Click()
If IsNull([ImportDataList]) Then
MsgBox ("Please select a shipment.")

Else
DoCmd.OpenReport "Rpt Fax Cover", , , "[Batch] = " & [ImportDataList]
DoCmd.OpenReport "TestEPAPage1"
DoCmd.OpenReport "TestEPAPage2"
DoCmd.OpenReport "Rpt EPA Datasheet", , , "[Batch] = " & [ImportDataList]

End If
End Sub

So how do I make it so that it will put all 4 reports into 1 PDF file? I am using Access 2007, and I have downloaded and installed the Add-in from Microsoft for PDF files, but I am not sure how to put the code in for this. I tried playing around with it and I was able to get the save as box to pop up and when I gave it a test name, it wanted to print 1000s of pages of just Rpt Fax Cover, because that is where I started to play with the code.

I am not sure how the "[Batch] = " & [ImportDataList] statement works, but I know that when the data is imported the shipping data is put in a table called Import Data, and the individual units that are being shipped on that shipment are put in a table called Units Imported.


If you are confused, I'm sorry, I'm trying to make this clear while not novel length. thanks for any help, it will definately be appreciated!

HI!
sorry for my english....

I have a report with 60 pages and i need to print each page in a different file, in pdf format ( i have acrobat distiller).
The file name must be the first record placed in the header of the page..
Is it possible do it in a VBA?
Thanks a lot!!

I have code to print out to PDF, I'm able to force a unique file name and I'm sitting on the save to pdf Windows window. I'm tring to programatically tab..tab and enter to complete the save, then next in a loop. I have tried the sendkeys method without success. It seems control/focus has passed to the save window and sendkeys are not working. I can manually tab..tab and enter to complete this need...but want to control it via VBA. I'm in Access 2003 under Windows XP. Any suggestions, thanks in advance.

Hi,

I have a form with a combo box listing different tests.

I am trying to get reports based on the value of the combo box.

The reports I have created have a PDF object with the relevant field transparent. Certain items in the combo box need there own individual report whereas others use the same report.

I have the following problems and would appreciate any help?

When exiting the form how do I get it to;

1. Ask if the user wants to print the reports for the records just created?
2. Select the correct report for each test?
3. Print only the reports for the records just added?

Regards

Spike

Good Afternoon people,

I am trying to get a hand of a few things when it comes to Access and VBA. There is just one thing i can not seem to figure out.

Ive got a database which is made by a man who is no longer with us, and he made a button which opens a report, gives the printer an order to print it, and also saves the report as a pdf file.

The code beind that button is:


	Code:
	Private Sub cmdafdrtoetsN1_Click()
On Error GoTo Err_cmdafdrtoetsN1_Click
    
    Dim prt As Printer
    Dim i As Long
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim strNaam As String
    Dim strSubPad As String
    Dim strPad As String
    Dim strRapport As String
    Set db = CurrentDb
    Set rst = db.OpenRecordset("contact", dbOpenDynaset)
    strRapport = "raptoetsingN1"
    Set prt = Application.Printer
    Application.Printer = Printers("PDFCreator")
    'patch which will be used to save the .pfd    
    '(Folder current database)
    'strPad = CurrentProject.Path
    PDF_Start
    strNaam = rst!opdrachtnr
    strSubPad = Left(strNaam, 2)
    strPad = "G:" & strSubPad & "" & strNaam & "" & "Acrobat"
    strNaam = "LS_Asfalt_Toetsing_(N1)" & strNaam
            If AfdrukkenRapport(strRapport, strNaam, strPad) = 1 Then
            rst.MoveNext
        Else
        End If
    PDF_Stop
    Application.Printer = prt
    DoCmd.OpenReport strRapport, acViewNormal
Exit_cmdafdrtoetsN1_Click:
    Exit Sub
Err_cmdafdrtoetsN1_Click:
    MsgBox "Fout: " & Err.Number & vbNewLine & Err.Description, vbExclamation, "cmdafdrtoetsN1_Click"
    Resume Exit_cmdafdrtoetsN1_Click
End Sub

What i am trying to do is to split the 2 functions and make one button to print it, and one button to save it as a pdf. But i cant seem to understand what happends in this code.

Is there anyone who is so kind to explain it a little bit so i can split the thing?

Cheers!

Hello.

I am relatively new to Access VBA and cannot find an answer to my problem. I am using Access 2007 with the add-in that allows printing directly to a pdf file. When I print my report manually to a pdf file, the resulting pdf file is 60KB. When I use VBA to programatically print the same report using DoCmd.OutputTo acOutputReport, the resulting pdf file is over 300KB - 5 times larger! I will be printing over 15,000 reports and emailing them, so this causes multiple issues.

Below is the code that I am using:

DoCmd.OutputTo acOutputReport, "MyReport", acFormatPDF, strPath & "MyReport" & strLoc & ".pdf"

I have set the Acrobat preferences conversion settings to "smallest file size". I have also gone to the Advanced settings in the preferences dialog box and changed the resolution, but that didn't help. I have also tried adding the Output Quality parameter and tried acExportQualityScreen vs. acExportQualityPrint, but there is virtually no difference in the resulting file size.

I feel like there must be a setting somewhere that I am missing, but I just cannot figure out what's going on. If anyone has any tips, I would greatly appreciate it!

Hi all,

I have created a function that I feel is worth sharing although it isn't quite doing what I expect. Here's what I have in my database:
I have a long list of Tests each with an assoicated PDF file Each of these tests has a specific ident but can belong to a specific section i.e. 1a, 1b, 1c.... 5e etc. Here's what the code is supposed to do:
Group all the tests by section and merge them all into a PDF with the title of that seciton i.e. 1a - TAXI.pdf Once the test PDF's are inserted rename the bookmarks to remove ".pdf" from any of the bookmarks Save and Close the respective test section PDF's Group all of the test section PDF's and merge them into one big pdf i.e. Validation Tests.pdf Again, once the section PDF's are inserted rename the bookmarks to remove ".pdf" from any of the bookmarks All works fine apart from, when the bookmarks are renamed (utilising the PDBookmark method) and the document is saved the respective bookmarks lose there appropriate action i.e. Goto page 10 etc.

I've tried saving the file directly after changing each bookmark but this doesn't make any difference (and is a very slow process!).

I know that alot of the code here could be cleaned up with sub functions but here is what I've got.


	Code:
	Private Sub Merge_Click()

Dim objCAcroPDDocDestination As Acrobat.AcroPDDoc
Dim objCAcroPDDocSource As Acrobat.AcroPDDoc
Dim objCAcroPDBookmark As CAcroPDBookmark
Dim fso As Scripting.FileSystemObject
Dim strSection As String, strFolder As String, strCreateFolder As String, strFolderSpec As String, strMsg As String
Dim strSectionLast As String, strMyFile As String, strPDFSave(50) As String, strPDFSaveFile(50) As String, strPDFFile(1000)
Dim PDFHyper As String, PDFIdent As String, TESTFileSpec As String, strLeft As String, strLeftLast As String
Dim strBMTitle As String, strMsgFail As String
Dim f As File
Dim fldr As Folder
Dim c As Control, d As Control
Dim chkFolder As Boolean, chkInsert As Boolean, chkRenameBM As Boolean, chkBMTitle As Boolean
Dim introw As Integer, k As Integer, l As Integer, m As Integer, n As Integer, intTotal As Integer
    
Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")
Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")
Set objCAcroPDBookmark = CreateObject("AcroExch.PDBookmark")
Set fso = New Scripting.FileSystemObject
Set c = Me.ViewQTGTEST
Set c = Me.ViewQTGTEST
Set d = [Forms]![Select Programme]![SelectProg]
k = 0
l = 0
m = 0
intTotal = 0
    
'Check batch folder exists. If not create
strFolder = d.Column(2) & "PDF"
chkFolder = fso.FolderExists(strFolder)
'Debug.Print strFolder, chkFolder

If c.Enabled = False Then
    MsgBox "Please select a format (PDF or TEST) to View Tests.", vbInformation + vbOKOnly, _
            "Select Format"
    Exit Sub
End If

If (c.ItemsSelected.COUNT = 0) Then
    
    Msg = "No tests selected. Do you want to merge complete suite of test PDF's?"
    Response = MsgBox(Msg, vbInformation + vbOKCancel, "Merge Complete Set of Tests?")

    If Response = vbOK Then
        
        DoCmd.Hourglass True
        'Select all tests
        Call SelAll_Click
        
        'Create appropriate folder
        If chkFolder = False Then
            fso.CreateFolder (strFolder)
            'Debug.Print strCreateFolder
        End If
        
        'Get test PDF information
        If (Nz(c.Column(2, introw), "") = "") Then
            MsgBox "There is no associated folder supplied with the test: " & c.Column(0, introw), vbInformation + vbOKOnly,
_
                        "No folder supplied"
        Else
            For introw = 0 To c.ListCount - 1
                        
                    If c.Selected(introw) Then

                        strFolderSpec = GetFolderSpec(introw) & c.Column(2, introw) & ""
                        TESTFileSpec = strFolderSpec & c.Column(0, introw) & ".test"
                        PDFIdent = GetPDFIdentFromFile(TESTFileSpec)
                        If Nz(PDFIdent, "") = "" Then
                            PDFIdent = c.Column(0, introw)
                        End If
                        
                        'Calculate Section based on Ident
                        strLeft = Left(PDFIdent, 2)
                        If strLeft = "" Then
                            strSection = "N/A"
                        ElseIf strLeft = "1a" Then
                            strSection = "1a - TAXI"
                        ElseIf strLeft = "1b" Then
                            strSection = "1b - TAKE-OFF"
                        ElseIf strLeft = "1c" Then
                            strSection = "1c - CLIMB"
                        ElseIf strLeft = "1d" Then
                            strSection = "1d - CRUISE_DESCENT"
                        ElseIf strLeft = "1e" Then
                            strSection = "1e - STOPPING"
                        ElseIf strLeft = "1f" Then
                            strSection = "1f - ENGINES"
                        ElseIf strLeft = "2a" Then
                            strSection = "2a - STATIC CONTROLS"
                        ElseIf strLeft = "2b" Then
                            strSection = "2b - DYNAMIC CONTROLS"
                        ElseIf strLeft = "2c" Then
                            strSection = "2c - LONGITUDINAL"
                        ElseIf strLeft = "2d" Then
                            strSection = "2d - LATERAL"
                        ElseIf strLeft = "2e" Then
                            strSection = "2e - LANDING"
                        ElseIf strLeft = "2f" Then
                            strSection = "2f - GROUND EFFECT"
                        ElseIf strLeft = "2g" Then
                            strSection = "2g - WINDSHEAR"
                        ElseIf strLeft = "2h" Then
                            strSection = "2h - PROTECTION"
                        ElseIf strLeft = "3a" Then
                            strSection = "3a - FREQUENCY RESPONSE"
                        ElseIf strLeft = "3b" Then
                            strSection = "3b - LEG BALANCE"
                        ElseIf strLeft = "3c" Then
                            strSection = "3c - TURN AROUND CHECK"
                        ElseIf strLeft = "3d" Then
                            strSection = "3d - MOTION EFFECTS"
                        ElseIf strLeft = "3e" Then
                            strSection = "3e - MOTION REPEATABILITY"
                        ElseIf strLeft = "3f" Then
                            strSection = "3f - MOTION CUEING"
                        ElseIf strLeft = "3g" Then
                            strSection = "3g - MOTION VIBRATIONS"
                        ElseIf strLeft = "4a" Then
                            strSection = "4a - VISUAL RESPONSE"
                        ElseIf strLeft = "4b" Then
                            strSection = "4b - VISUAL SCENE QUALITY"
                        ElseIf strLeft = "4c" Then
                            strSection = "4c - VISUAL GROUND SEGMENT"
                        ElseIf strLeft = "4d" Then
                            strSection = "4d - VISUAL SYSTEM"
                        ElseIf strLeft = "4e" Then
                            strSection = "4e - VISUAL SYSTEM"
                        ElseIf strLeft = "4f" Then
                            strSection = "4f - VISUAL SYSTEM"
                        ElseIf strLeft = "4g" Then
                            strSection = "4g - VISUAL SYSTEM"
                        ElseIf strLeft = "5a" Then
                            strSection = "5a - TURBO-JET AEROPLANES"
                        ElseIf strLeft = "5b" Then
                            strSection = "5b - PROPELLER AEROPLANES"
                        ElseIf strLeft = "5c" Then
                            strSection = "5c - SPECIAL CASES"
                        ElseIf strLeft = "5d" Then
                            strSection = "5d - BACKGROUND NOISE"
                        ElseIf strLeft = "5e" Then
                            strSection = "5e - FREQUENCY RESPONSE"
                        Else
                            strSection = "N/A"
                        End If
                        
                        If strLeftLast = "" Then
                            'Initialise array counter
                            k = 1
                            'Capture number of files created
                            m = k
                            'Initialise/Open Destination file
                            objCAcroPDDocDestination.Open strFolder & "Template.pdf"
                            
                        ElseIf Not (strLeft = strLeftLast) Then   'Check if section has changed
                            'Capture individual file counter
                            n = l
                            'Debug.Print strLeft, strLeftLast
                            'Save file name to array
                            strPDFSave(k) = strFolder & "" & strSectionLast & ".pdf"
                            'Delete First Blank page
                            objCAcroPDDocDestination.DeletePages 0, 0
                            'Loop through all bookmarks referencing the TEST names
                            'of the merged files and remove ".pdf" from bookmark
                            For l = 1 To n
                                chkBMTitle = objCAcroPDBookmark.GetByTitle(objCAcroPDDocDestination, strPDFFile(l))
                                'Debug.Print strBMTitle, chkBMTitle
            
                                'Search for ".pdf" and Replace if found
                                If chkBMTitle = True Then
                                'If FindIt(strBMTitle, ".pdf") Then
                                    chkRenameBM = objCAcroPDBookmark.SetTitle(Replace(strPDFFile(l), ".pdf", ""))
                    
                                    If chkRenameBM = True Then
                                        'Debug.Print "SUCCESS!"
                                    Else
                                        'Debug.Print "FAILED!"
                                    End If
                                End If
                            Next l
                            'Save/Close Destination file
                            objCAcroPDDocDestination.Save 1, strPDFSave(k)
                            objCAcroPDDocDestination.Close
                            'Update/Open Destination file, to which to add PDF's
                            objCAcroPDDocDestination.Open strFolder & "Template.pdf"
                            'Increment counter
                            k = k + 1
                            'Capture number of files created
                            m = k
                            'Reset individual file counter
                            l = 0
                        ElseIf introw = c.ListCount - 1 Then
                            'Save file name to array
                            strPDFSave(k) = strFolder & "" & strSectionLast & ".pdf"
                            'Delete First Blank page and Save/Close Destination file
                            objCAcroPDDocDestination.DeletePages 0, 0
                            'Loop through all bookmarks referencing the TEST names
                            'of the merged files and remove ".pdf" from bookmark
                            For l = 1 To n
                                chkBMTitle = objCAcroPDBookmark.GetByTitle(objCAcroPDDocDestination, strPDFFile(l))
                                'Debug.Print strBMTitle, chkBMTitle
            
                                'Search for ".pdf" and Replace if found
                                If chkBMTitle = True Then
                                'If FindIt(strBMTitle, ".pdf") Then
                                    chkRenameBM = objCAcroPDBookmark.SetTitle(Replace(strPDFFile(l), ".pdf", ""))
                    
                                    If chkRenameBM = True Then
                                        'Debug.Print "SUCCESS!"
                                    Else
                                        'Debug.Print "FAILED!"
                                    End If
                                End If
                            Next l
                            'Save/Close Destination file
                            objCAcroPDDocDestination.Save 1, strPDFSave(k)
                            objCAcroPDDocDestination.Close
                            'Capture number of files created
                            m = k
                        End If
                        
                        Set fldr = fso.GetFolder(strFolderSpec)
                        
                        For Each f In fldr.Files
                            
                            If (Right(f.Name, 3) = "pdf") Then
                                'Open the source document that will be added to the destination
                                objCAcroPDDocSource.Open (f)
                                chkInsert = objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1,
objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, True)
                                
                                If chkInsert = True Then
                                    'Increment number of files captured and file name
                                    intTotal = intTotal + 1
                                    l = l + 1
                                    strPDFFile(l) = f.Name
                                    'Debug.Print "SUCCESS!"
                                Else
                                    strMsgFail = strMsgFail & vbCrLf & f.Name
                                    'Debug.Print "FAILED!"
                                End If
                                
                                Debug.Print l, strPDFFile(l), f, f.Name, strFolderSpec
                                'Close source document (no longer needed)
                                objCAcroPDDocSource.Close
                                Exit For
                            End If
                            
                        Next f
                        
                        'Debug.Print fldr, strLeft, strLeftLast, strSection, strSectionLast
                        'Capture Previous data
                        strLeftLast = strLeft
                        strSectionLast = strSection
                        
                    End If
                    
            Next introw
            
            ''''''' Combine all Section PDF's '''''''
            'Initialise/Open Destination file
            objCAcroPDDocDestination.Open strFolder & "Template.pdf"
            Set fldr = fso.GetFolder(strFolder)
                        
            For Each f In fldr.Files
                For k = 1 To m
                    strPDFSaveFile(k) = Replace(strPDFSave(k), strFolder & "", "")
                    'Debug.Print f.Name, strPDFSaveFile(k)
                    If (f.Name = strPDFSaveFile(k)) Then
                        'Open the source document to add to destination
                        objCAcroPDDocSource.Open (f)
                        chkInsert = objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1,
objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 1)
                        If chkInsert = True Then
                            'Debug.Print "SUCCESS!"
                            strMsg = strMsg & vbCrLf & strPDFSaveFile(k)
                        Else
                            'Debug.Print "FAILED!"
                            strMsgFail = strMsgFail & vbCrLf & strPDFSaveFile(k)
                        End If
                                
                        'Remove ".pdf" from bookmarks
                        chkBMTitle = objCAcroPDBookmark.GetByTitle(objCAcroPDDocDestination, strPDFSaveFile(k))
                        'Debug.Print strBMTitle, chkBMTitle
                        
                        'Search for ".pdf" and Replace if found
                        If chkBMTitle = True Then
                        'If FindIt(strBMTitle, ".pdf") Then
                            chkRenameBM = objCAcroPDBookmark.SetTitle(Replace(strPDFSaveFile(k), ".pdf", ""))
                            If chkRenameBM = True Then
                                'Debug.Print "SUCCESS!"
                            Else
                                'Debug.Print "FAILED!"
                            End If
                        End If
                        'Close source document (no longer needed)
                        objCAcroPDDocSource.Close
                        Exit For
                    End If
                Next k
            Next f
            
            'Delete First Blank page and Save/Close Destination file
            objCAcroPDDocDestination.DeletePages 0, 0
            objCAcroPDDocDestination.Save 1, strFolder & "Validation Tests.pdf"
            objCAcroPDDocDestination.Close
            
            'Check all files have been merged succesfully
            If intTotal = c.ListCount - 1 Then
                Msg = "The following files have succesfully been created and merged in to the following PDF:" & vbCrLf &
vbCrLf & _
                        strFolder & "Validation Tests.pdf" & vbCrLf & strMsg & vbCrLf & vbCrLf & _
                        "Would you like to view the created file?"
                Response = MsgBox(Msg, vbInformation + vbYesNo, "Files Created Succesfully!")
            
                If Response = vbYes Then
                    OpenPDFDocument (strFolder & "Validation Tests.pdf")
                End If
            Else
                Msg = "The following files have NOT been succesfully merged in to the respective PDF(s):" & vbCrLf & _
                        strMsgFail & vbCrLf & vbCrLf & "Please apply corrective action!" & vbCrLf & vbCrLf & _
                        "However, all other files have succesfully been created and merged into the following PDF:" & vbCrLf
& vbCrLf & _
                        strFolder & "Validation Tests.pdf" & vbCrLf & strMsg & vbCrLf & vbCrLf & _
                        "Would you like to view the created file?"
                Response = MsgBox(Msg, vbExclamation + vbYesNo, "Files Created with Errors!")
            
                If Response = vbYes Then
                    OpenPDFDocument (strFolder & "Validation Tests.pdf")
                End If
            End If
            
            Call ClearList_Click

        End If
    
    Else
        End
    
    End If
    
ElseIf (c.ItemsSelected.COUNT > 1) Then
        
        DoCmd.Hourglass True
        
        'Create appropriate folder
        If chkFolder = False Then
            fso.CreateFolder (strFolder)
            'Debug.Print strCreateFolder
        End If
        
        'Get test PDF information
        If (Nz(c.Column(2, introw), "") = "") Then
            MsgBox "There is no associated folder supplied with the test: " & c.Column(0, introw), vbInformation + vbOKOnly,
_
                        "No folder supplied"
        Else
            strMyFile = InputBox("Please Enter File Name to Save Merged PDF.", "PDF File Name")
            
            'Check validity of input
            If (Nz(strMyFile, "") = "") Then
                
                MsgBox "Please enter a file name!", vbExclamation + vbOKOnly, "Invalid File Name!"
                DoCmd.Hourglass False
                Call ClearList_Click
                Exit Sub
                
            ElseIf (Right(strMyFile, 4) = ".pdf") Then
            
                strMyFile = Replace(strMyFile, ".pdf", "")
                
            End If
            
            Debug.Print strMyFile
            
            For introw = 0 To c.ListCount - 1
                        
                    If c.Selected(introw) Then

                        strFolderSpec = GetFolderSpec(introw) & c.Column(2, introw) & ""
                        TESTFileSpec = strFolderSpec & c.Column(0, introw) & ".test"
                        PDFIdent = GetPDFIdentFromFile(TESTFileSpec)
                        If Nz(PDFIdent, "") = "" Then
                            PDFIdent = c.Column(0, introw)
                        End If
                        
                        'Initialise/Open Destination file
                        objCAcroPDDocDestination.Open strFolder & "Template.pdf"
                        
                        Set fldr = fso.GetFolder(strFolderSpec)
                        
                        For Each f In fldr.Files
                                
                            If (Right(f.Name, 3) = "pdf") Then
                                'Increment counter
                                l = l + 1
                                'Open the source document that will be added to the destination
                                objCAcroPDDocSource.Open (f)
                                chkInsert = objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1,
objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, True)
                                strPDFFile(l) = f.Name
                                
                                If chkInsert = True Then
                                    intTotal = intTotal + 1
                                    'Debug.Print "SUCCESS!"
                                    strMsg = strMsg & vbCrLf & strPDFFile(l)
                                Else
                                    'Debug.Print "FAILED!"
                                    strMsgFail = strMsgFail & vbCrLf & strPDFFile(l)
                                End If
                                
                                'Close source document (no longer needed)
                                objCAcroPDDocSource.Close
                                Exit For
                            End If
                    
                        Next f
                        
                    End If
                    
                    If introw = c.ListCount - 1 Then
                        'Delete First Blank page and Save/Close Destination file
                        objCAcroPDDocDestination.DeletePages 0, 0
                        objCAcroPDDocDestination.Save 1, strFolder & "" & strMyFile & ".pdf"
                        objCAcroPDDocDestination.Close
                        Exit For
                    End If
                    
            Next introw
            
            'Check all files have been merged succesfully
            If intTotal = l Then
                Msg = "The following files have succesfully been created and merged in to the following PDF:" & vbCrLf &
vbCrLf & _
                        strFolder & "" & strMyFile & ".pdf" & vbCrLf & strMsg & vbCrLf & vbCrLf & _
                        "Would you like to view the created file?"
                Response = MsgBox(Msg, vbInformation + vbYesNo, "Files Created Succesfully!")
            
                If Response = vbYes Then
                    OpenPDFDocument (strFolder & "" & strMyFile & ".pdf")
                End If
            Else
                Msg = "The following files have NOT been succesfully merged in to the respective PDF(s):" & vbCrLf & _
                        strMsgFail & vbCrLf & vbCrLf & "Please apply corrective action!" & vbCrLf & vbCrLf & _
                        "However, all other files have succesfully been merged into the following PDF:" & vbCrLf & vbCrLf & _
                        strFolder & "" & strMyFile & ".pdf" & vbCrLf & strMsg & vbCrLf & vbCrLf & _
                        "Would you like to view the created file?"
                Response = MsgBox(Msg, vbExclamation + vbYesNo, "Files Created with Errors!")
            
                If Response = vbYes Then
                    OpenPDFDocument (strFolder & "" & strMyFile & ".pdf")
                End If
            End If
            
        End If
        
        Call ClearList_Click
        
ElseIf (c.ItemsSelected.COUNT = 1) Then
    
    MsgBox "A single test cannot be merged!" & vbCrLf & "Please select more than one test.", _
            vbExclamation + vbOKOnly, "Insufficient Test Selection!"
    Call ClearList_Click
    Exit Sub
End If

Set f = Nothing
Set fldr = Nothing
Set fso = Nothing
Set objCAcroPDDocSource = Nothing
Set objCAcroPDDocDestination = Nothing

DoCmd.Hourglass False

End Sub

Thanks in advance!

I created a database on my personal laptop that has Access 2010. There are several reports that are generated via a parameter query and with the use of macros, they are then emailed in PDF format when I click a button on the main form I use. However, the version of Access I have at work is 2003 so these buttons no longer work. I created new buttons to preview the report so that I can save as a PDF and email to those who require them. There are a lot more steps doing it this way and I know there has to be a better way.

Below is the VBA code generated by Access.


	Code:
	Private Sub ReportCS_Click()
On Error GoTo Err_ReportCS_Click

    Dim stDocName As String

    stDocName = "rpt_LTL Shipments for CS"
    DoCmd.OpenReport stDocName, acPreview

Exit_ReportCS_Click:
    Exit Sub

Err_ReportCS_Click:
    MsgBox Err.Description
    Resume Exit_ReportCS_Click
    
End Sub

This is a shipping report and when I click the button, I enter the date in as "1/27" and the report shows the shipping for that day. Now I need to right click and print to a PDF format and the name always default to the name of the report in Access. What I would like it to do is always save in the same folder on my hard drive and rename it to "CS - Shipping Report 2012.01.27.pdf" whereas the date would change to the one entered in the parameter.

Is this something that would be easy to code? Or can anyone suggest other options?

Hopefully I explained this well enough.

Hi,

I only know very basic but if someone could come up with a small code I'm sure i can tweak it to do what i need.

I have a form called "form 1"

I save each record as it looks like when you print the current record as a pdf (i currently use cutepdf to save current form)

I have 5000 + records and would like to create a vba code on a button that once clicked it will print each record to a pdf and automatically save it as my primary key fields (which will be say "name") and once saved in a folder of my choosing will the print the 2nd record and so on until all 5000 are done.

In the end i would like to have all records on the form saved as pdf's with their individual names in a folder.



I have seen a few people try and ask this question and im not getting much help so i thought i would try here. If you can help i would be grateful.

Thank you,

I haven't had a lot of exposure to Access and VBA for around ten years, so I'm practically a newbie. I remember a little bit of VBA, and I know some powershell and vbscript. I apologize in advance for my ignorance and stumbling.

I need to print a series of weekly reports for different departments. I don't want to maintain a different report for each department, so I'd like to be able to change the report header and query source for one report using VBA.

Here's how I was thinking of doing it:
Get the name of the first department from a table. Get the appropriate query name from the department name. They are all named "qryDepartment". Open the report (rptWeekly) in design mode. Change the report record source to qryDepartment. Change the report header (Label26) to "Department Weekly Report". Export the report to PDF. Go back to #1 for the next dept. Except I can't remember how to do most of this. I can export the report to PDF, but how do I loop through the department names table, and how do I change the record source and header before exporting it?


If there's a better way to do this, I'm open to that too.


Thank you!


Not finding an answer? Try a Google search.