0

I am looking to print pdf files with vba but want to add a text box with information that changes based on a list in excel. I have three columns in the excel file with information such as file location for the material pdf, number of copies and the order number that it needs to run on.

Currently I have to hand write all order numbers on the papers after my vba prints the pdf files. Is there a way to add lines of code to my code that will add a text box or watermark that will have the order number on each pdf. Order number can change based on the pdf file being rpinted. Thank you! Code below:

Sub Button4_Click()
Dim Msg As String, Ans As Variant

   Msg = "Would you like to print all of the BOMs"

   Ans = MsgBox(Msg, vbYesNo)

   Select Case Ans

   Case vbYes

    'NOTE:
    '/s=don't show splash screen
    '/n=new instance
    '/h=minimized window
    '/t=print to default printer; or use /t <filename> <printername> <drivername> <portname>

    'CHECK YOUR ADOBE READER VERSION, AND USE CORRECT PATH..
    zProg = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"

    zLastRow = [M65536].End(xlUp).Row           'find last row in column [M]; e.g. 15
    temp = "M1:M" & zLastRow                    'e.g. "M1:M15"

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each cell In Range(temp)                'loop through all entries in range
    zFile = cell.Value                          'fetch filename from cell
    r = cell.Row
    zPrintCopies = Cells(r, "N")
    If zPrintCopies = "" Then
    zPrintCopies = 1
    Cells(r, "B") = 1
    End If

    If zFile Like "*.pdf" Then                  'check it is a pdf file type
    .LeftHeader = Format(Now(), "dd/mm/yy")

    For i = 1 To zPrintCopies
    Shell (zProg & " /n /h /t " & zFile)        'execute command to print  the pdf document
    Next i
    End If                                      'end of test for pdf file type

    Next                                        'process next file in list
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Case vbNo
GoTo Quit:
End Select

Quit:
End Sub
dn23
  • 1
  • 3
  • have you done some research? – ashleedawg Mar 14 '18 at 18:12
  • Yes, have been looking for the best options for the last couple of hours that work within vba for adobe reader – dn23 Mar 14 '18 at 18:15
  • This site has a requirement that, prior to posting questions, a considerable amount of research has been done, and that solutions have been attempted in such a way that evidence of them can be shared in the question. Please see "[mcve]" as well as "[help-on-topic]" – ashleedawg Mar 14 '18 at 18:19
  • As for PDF's they can be a tricky thing to integrate into other applications, especially when it comes to modifying them. You may require a copy of Adobe Acrobat (the **Full** version ; not "Reader") in order to get the VBA libraries you'll need,. – ashleedawg Mar 14 '18 at 18:20
  • If I have gotten as far as I did in the current code, then I have done my research, been doing vba for the last 1 year. First time I haven't been able to figure it out myself, can not find other information that is similar enough to my question. I know adobe reader has a comment section and will allow a text box to be added and type in. Is there a way to trigger that using vba code? – dn23 Mar 14 '18 at 18:26
  • I see you're using command-line switches to print the document. Do you have a list (or link to a list ) of all of the command-line options? Perhaps there's something in there we can manipulate. – ashleedawg Mar 14 '18 at 18:34
  • This is the link which helped me find my original commands: https://stackoverflow.com/questions/619158/adobe-reader-command-line-reference – dn23 Mar 14 '18 at 18:50

1 Answers1

0

Here are a couple options (although any of them will take some work):

1. Manipulate the PDF file programmatically

In my experience, PDF files aren't fun to work with at the best of times -- and even more so when there's a need to actually modify the file. This is mostly because Adobe is so secretive with their proprietary software.

There is a capability of annotating the files to add what you need to (see here) but that could involve manually modifying each file.

With the purchase of Adobe Acrobat (the full version; not Reader) I believe you get a library (Acrobat.tlb I believe) that can help you manipulate the file with VBA. It's worth checking the VBA references to see if your company already has the license, in which case something like this might work.

2. Add annotations or watermarks manually

Depending on the volume of files you need to process, this could be an option. There are various freebies online like this and this.

3. Programmatically with your Printer Settings (via Windows, etc)

Many, if not most, printers have the option of adding a header/footer or even watermark to "all" printed pages. (My cheap Brother printer at home does.)

Some might have the option of including the filename as a heading. With mine, I would set the printer to include a bitmap as a watermark, and then create a .bmp file programmatically before each print job with whatever information I needed to include. Bitmaps are surprisingly simple to encode/decode with VBA. (Some ideas here and here.)

ashleedawg
  • 20,365
  • 9
  • 72
  • 105