Skip to main content
J
Jana36320836r1wz
New Participant
March 26, 2024
Question

500+ pdfs to make from an excel spreadsheet

  • March 26, 2024
  • 2 replies
  • 602 views

I have over 500 of the same document I need to email out to schools regarding students that may have enrolled at those schools. The forms are standard to my school but I want to import all the data from the Excel spreadsheet to a PDF I have created. I have matched the cells to the lines in the PDF and embedded on the Prepare Form the names to match the cells. I am just not able to figure out how to get the data from the Excel spreadsheet to create and save 500 different PDF's. I had assistance with a macro that works great but it won't save the PDF's individually it wirtes one and overwrites the next line. Can anyone help me with this please. 

    This topic has been closed for replies.

    2 replies

    C
    customdataservices
    Participating Frequently
    March 31, 2024

    You may want to take a look at...

    https://www.try67.com/tool/acrobat-mail-merge-and-email-pdf-files

     

     

      Thom Parker
      Thom Parker
      Community Expert
      March 26, 2024

      What is the script/macro you are currently using and how do you run it?

       

       

       

      Thom Parker - Software Developer at PDFScriptingUse the Acrobat JavaScript Reference early and often
      J
      Jana36320836r1wzAuthor
      New Participant
      March 31, 2024

      I am including the script below, I adapted it from something I located on YouTube. It worked when tested for the first form but did not save it and instead continued to overwrite the form on the next document going forward. I am trying to become more versed in Excel and Adobe and just struggling. I appreciate any and all assistance. 

       

      Dim PDFFldr As FileDialog Set PDFFldr = Application.FileDialog(msoFileDialogFilePicker) With PDFFldr .Title = "Select PDF file to attach" .Filters.Add "PDF Type Files", "*.pdf", 1 If .Show <> -1 Then GoTo NoSelection Sheet1.Range("G18").Value = .SelectedItems(1) End With NoSelection: End Sub Sub SavePDFFolder() Dim PDFFldr As FileDialog Set PDFFldr = Application.FileDialog(msoFileDialogFolderPicker) With PDFFldr .Title = "Select a Folder" If .Show <> -1 Then GoTo NoSel: Sheet1.Range("G20").Value = .SelectedItems(1) End With NoSel: End Sub Sub CreatePDFForms() Dim PDFTemplateFile, NewPDFName, SavePDFFolder, LastName As String Dim ApptDate As Date Dim CustRow, LastRow As Long With Sheet1 If .Range("G18").Value = Empty Or .Range("G20").Value = Empty Then MsgBox "Both PDF Template and Saved PDF Locations are required for macro to run" Exit Sub End If LastRow = .Range("E9999").End(xlUp).Row 'Last Row PDFTemplateFile = .Range("G18").Value 'Template File Name SavePDFFolder = .Range("G20").Value 'Save PDF Folder ThisWorkbook.FollowHyperlink PDFTemplateFile Application.Wait Now + 6E-05 For CustRow = 5 To LastRow LastName = .Range("E" & CustRow).Value 'Last Name ApptDate = .Range("G" & CustRow).Value 'Appt Date Application.SendKeys "{Tab}", True Application.SendKeys LastName, True Application.Wait Now + 1E-05 Application.SendKeys "{Tab}", True Application.SendKeys .Range("F" & CustRow).Value, True 'First Name Application.Wait Now + 1E-05 Application.SendKeys "{Tab}", True Application.SendKeys "{Tab}", True Application.SendKeys .Range("I" & CustRow).Value, True 'Address Application.Wait Now + 1E-05 Application.SendKeys "{Tab}", True Application.SendKeys .Range("J" & CustRow).Value, True 'City Application.Wait Now + 1E-05 Application.SendKeys "{Tab}", True Application.SendKeys .Range("K" & CustRow).Value, True 'State Application.Wait Now + 1E-05 Application.SendKeys "{Tab}", True Application.SendKeys .Range("L" & CustRow).Value, True 'Zip Application.Wait Now + 1E-05 Application.SendKeys "{Tab}", True Application.SendKeys .Range("M" & CustRow).Value, True 'Email Application.Wait Now + 1E-05 Application.SendKeys "{Tab}", True Application.SendKeys "{Tab}", True Application.SendKeys Format(.Range("N" & CustRow).Value, "###-###-####"), True 'Phone Application.Wait Now + 1E-05 Application.SendKeys "{Tab}", True Application.SendKeys "^(p)", True Application.Wait Now + 3E-05 Application.SendKeys "{Enter}", True Application.Wait Now + 7E-05 If Dir(SavePDFFolder & "\" & LastName & "_" & Format(ApptDate, "DD_MM_YYYY") & ".pdf") <> Empty Then Kill (SavePDFFolder & "\" & LastName & "_" & Format(ApptDate, "DD_MM_YYYY") & ".pdf") Application.SendKeys "%(n)", True Application.Wait Now + 2E-05 Application.SendKeys SavePDFFolder & "\" & LastName & "_" & Format(ApptDate, "DD_MM_YYYY") & ".pdf" Application.Wait Now + 3E-05 Application.SendKeys "%(s)", True Application.Wait Now + 2E-05 Next CustRow Application.SendKeys "^(q)", True Application.SendKeys "{numlock}%s", True End With End Sub  

      Thom Parker
      Thom Parker
      Community Expert
      March 31, 2024

      So the first thing to learn about coding is how to format it so that someone can actually read the code. 

      I'd  strongly suggest you start there. 

       

      Now, this looks like an Excell VBA script that uses keyboard emulation to control Acrobat. Although it's hard to tell exactly. But its not a good solution. 

       

      This type operation is called a variable data process, because it uses variable data to modify a single document, also called a mail merge. It's a common operation and there are lots of tools and scripts for it already. Other than writing your own, you may want to purchase a tool, or doing some more searching here and on google for other scripts/solutions.

       

      I'd suggest doing one of two things to create a solution

      1) Use the Acrobat IAC to control Acrobat in the Excel Script.

      https://opensource.adobe.com/dc-acrobat-sdk-docs/library/interapp/index.html

       

      2) Export the data from Excel into a CSV and write an Acrobat script to load the data. 

      You can read some about the basics of PDF data handling here:

      https://www.pdfscripting.com/public/Form-Data-Handling.cfm

       

       

       

      Thom Parker - Software Developer at PDFScriptingUse the Acrobat JavaScript Reference early and often
      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices