Excel First

Excel Solutions

Services

No Comments to Services

  1. tajmirEt says:

    What’s Going down i’m new to this, I stumbled upon this I’ve found It absolutely helpful and it has aided me out loads. I hope to give a contribution & help other customers like its helped me. Good job. tajmirEt

  2. Mynda says:

    A very thorough and easy to follow post, Catalin.

    Cheers,

    Mynda

  3. Eduard says:

    This is exactly what I have been looking for all weekend and finally found it.

    But unfortunately I’m not able to try it as it starts the VBA editor and prompts an error message when opening the file.

    The error message is as followed:

    “Compile error:
    The code in this Project must be updated for use on 64-bit systems.
    Please review and update Declare statements and then mark them with the PtrSafe attribute.”

    • Hi Eduard,
      You have to replace the function declarations:
      Replace these 2 lines:

      Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
      Private Declare Function CloseClipboard Lib "user32" () As Long

      With this:

      #If VBA7 Then
      Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
      Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
      #Else
      Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
      Private Declare Function CloseClipboard Lib "user32" () As Long
      #End If

      Or, you can download the sample workbook again, I updated the code for 64 bit systems.
      Cheers,
      Catalin

  4. John says:

    Another excellent article from THE excel guru.

    Very helpful, especially debugging complex formulas.

    Thanks for the tips!

  5. NickC says:

    Catalin, thank you, thank you!

    After literally dozens and dozens of hours of searching for answers and trying different proposed solutions without success, finally yours has proved successful.  I closely read (and re-read) and carefully followed your described process and, despite having no previous knowledge or experience of editing the code within Excel, I was able to successful undertake the task with the desired result! I can now make a copy of or save my pivot table-containing worksheet as a new version (with a new name (e.g. …version2.xlsx) or to a different place on the network) and when the new file is opened the pivot tables within seek their data from the source table within the same workbook rather than, as previously, from the previous version of the workbook.

    Nick

    • Hi Nick,
      Glad to hear that your problem is solved, hopefully Microsoft will solve this problem someday, without the need to disect the archive…
      It took me a lot of time too, to create this unique solution.
      Cheers,
      Catalin

  6. Richard Munsey says:

    Hi, When I export xml from pdf, I open in excel, source, xml map. field map only shows about 30 of the xml maps out of 112.

    Thank you

    • Hi Richard,
      Yes, this happens sometimes, but here is what you should try: don’t export blank form data, type some data into all fields from your form, then export again.
      Normally, it should not ignore fields with data. If you still have problems, you can send me the form, so I can take a look at it for you.
      Let me know if this solves the problem 🙂
      Cheers,
      Catalin

  7. A.Maurizio says:

    Good morning my name is John, I am writing because I have this problem that I would like to solve with Excel.
    Therefore, using an excel Chart with office 2007, I would faithfully recreate this chart that I found on the internet,
    but do not see any indication of the row or column that I could figure out how to proceed.
    Do not deny that I tried in a thousand ways to do it without asking anybody but there is nothing to do.
    She kindly can you give me a hand in this matter; Sincere greetings from a. Maurizio
    (PS) my Link where I can download my prospectus and this:

    https://app.box.com/s/yqbyrhyp1lggr8yofy01cpher7xqduyv

    • Hi Maurizio,
      Your chart’s data is linked to defined names from percentage_chart.xlsx’. The necesssary names are: arr_x, arr_y, arr_xx, arr_yy, first 2 are used for series 1 and last 2 for series 2.
      I understand that you don’t have the original file, but I can help you only if you provide all the details about how the chart should display, there is not enough info at this moment.
      Cheers,
      Catalin

  8. Nicolas says:

    Hello,

    it seems to be very effective for adding a new row, but with the macro running, I seems impossible to delete any row in the table.

    Did you face this issue?

     

    Best regards,

     

    • Hi Nicolas,
      Check the Switch sheet, you can find there a dropdown that is used to disable the code, in order to make manual changes, like deleting rows.
      Cheers,
      Catalin

  9. Zubayr says:

    I need a quote on a full version please as I need it urgently

  10. Rhy says:

    Wonderful post, this was exactly what I was looking for!  However, I am having a problem importing the mapped XML file from Excel back into Adobe?  No data is showing up in the form fields after import?  When I open the XML file I can clearly see that the correct data is in the XML…  Any ideas?

    Thanks!

    Rhy

  11. Excel-CRM is a nice piece of software Caltalin.  Well written !!!

  12. Katie says:

    Is there anyway to do this where you have a single excel file and have its data populate multiple different pdf forms?

    • Hi Katie,
      Yes, it is possible. You can add multiple xml maps to excel, just like you did for a single pdf. All xml fields can be mapped to the same cells, and you will receive a message when trying to export data, to select the mapping you want to export. You will have to name your maps, in order to easily recognize what should be exported.
      Also, you can use a macro to export each existing mapping.
      There are many possibilities. The one presented in this article describes a way to automatize this process for users that does not have Acrobat Pro installed. (It is also possible to use a code that exports excel data in FDF format, and create the PDF automatically based on that FDF exported file and a PDF template)

      For those who have Acrobat Pro, it’s a completely different picture, they have total control over the process, it can be done 100% via code, no need to manually export the field structure. It takes a few hours of work to write the program, but I can fill an unlimited number of PDF forms much faster. If you have Acrobat and you want me to help you, just let me know.
      Cheers,
      Catalin

      • Erin Hague says:

        I am trying to do this using Acrobat Pro. Granted the version now is more updated than your article post, the information still applys. Can you forward instructions or any links that demonstrates how to go from excel to pdf without having to do it manually? Thanks so much!

        • Hi Erin,
          The acrobat pro version is irrelevant, all versions have import/export commands.
          Go to the top of this post, there is a link to a more automated solution.
          Cheers,
          Catalin

      • Josiah Timmerman says:

        Hi Catalin

        Thanks so much for this! Exactly what I need, except I am having one issue.

        I can add multiple xml maps, but I get an error if I try to apply separate maps fields to the same cell? It says that it would overlap an existing mapping.

        • For the same cell?
          You mean for the same pdf template? Only one mapping can be used for a pdf template.

        • If you need to make different mappings for the same pdf template, then the easiest way is to copy the pdf template.
          Instead of MyTemplate.pdf, you will also have MyTemplate copy1.pdf, MyTemplate copy2.pdf, and so on.
          Each copy will get a separate worksheet, it’s the only way to do it without modifying the code.

  13. Carlos Januszewski says:

    Thank You!

  14. Prasad says:

    Hi Catalin, thanks a ton for the solution… similar problem was resolved at our end with detailed and clear step wise instructions….

  15. Gavin says:

    Hi Catalin,

    In step 2, what is the excel archive and what is archiver? Where can I find the xl\pivotCache\_rels folder?

    thanks,

    Gavin

  16. Denis says:

    Quote for full version please, I am just starting to investigate CRM tools.

  17. zoedtdt says:

    creatingpdf form from excel online can be download on this very well website http://www.tagpdf.com/online/convert-excel-to-pdf/, this is a wonderful creatingpdf form from excel online, and There are only three steps to creatingpdf form from excel easily.

    • Thanks for sharing the link you use, there are many online excel to PDF form converters, this article is not about creating the form though… It’s about filling it with data.

  18. yuki says:

    Hi, can i have the full version quote please?

  19. Pilar Keyes says:

    Hello Catalin,

    I have Adobe Acrobat Pro. I have a fillable PDF created using Live Cycle Designer.  It is a U.S. tax form (1042S) which can be found here.  https://www.pdffiller.com/en/project/76830301.htm?f_hash=b9649f&reload=true

    I ONLY have Excel 2011 for MAC.

    I would like to take my excel file and populate the fillable PDF with the many records in my excel.

    I do have the Designer tab in my excel but not the Source feature.

    When I try to use the adobe acrobat Tools – Forms – More Form Options – Import Data I get this error:

    Xml parsing error: syntax error (error code 2), line 1, column 1 of file /Macintosh HD/Users/pkeyes/Desktop/1042TabDelimitedTextforPDF.txt

    Any suggestions for me?

     

     

    • Hi Pilar,
      Unfortunately, Excel for Mac does not support XML tools, and other very useful excel tools, like Power Query for example. The file you indicated (“1042TabDelimitedTextforPDF.txt”) does not seem to be an xml file, it’s a tab delimited text.
      An xml file will start with a header like:
      <?xml version=”1.0″ encoding=”UTF-8″?>
      Then you will see the xml nodes and values:
      <topmostSubform>
      <c1_1>1</c1_1>
      <c1_2>2</c1_2>
      </topmostSubform>
      In your form, <c1_1> field tag corresponds to “1 Income code” field, <c1_2> to “2 Gross income”.
      You have to create the xml from code, and write all the values from code.
      In windows environment, I can even write directly from excel to PDF, using the Acrobat Pro library, but never tested the code on a Mac. Excel 2016 for Mac has more VBA limitations for manipulating files.
      I would go for writing the xml structure in a text file.
      Cheers,
      Catalin
      3

  20. Hi Caitlin.

    Unfortunately when sharing a spreadsheet using OneDrive, which allows multiple users to edit at the same time, by using the Edit in Browser option both VBA and macros are disabled. With this usage in mind is there any way to both protect formulas and allow the autoexpand functionality.
    I must be honest that Edit in Browser really restricts the functionality of Excel.

    Cheers
    Julian

    • I’m afraid that there are no other options on OneDrive, that limitation can be bypassed only with VBA, but as you already know, the code will not work in browser.
      Hopefully they will add this functionality on protected sheets.
      Cheers,
      Catalin

  21. shabrandi says:

    Hi
    I need a quote on a full version please help me how i can get it thanks

  22. Hmuny says:

    Can I have a full version?

  23. Mehmet Ali says:

    Buna zia Catalin this very good multu mesk mult if you dont mind I need full version quote please.

  24. Julian De Silva says:

    Hi Catalin

    The sample file that you have shared has a option to enable disable protection. My requirement is to keep the worksheet protected by default & should be able to enter data into the table and for it to get auto expanded. I dont want to have any buttons to enter new rows.

    All i want is to go on entering data into the protected sheet by auto expanding the table.

    I tried editing your code, but i messed it up big time. Appreciate your help on this.

    Cheers
    Julian

  25. Louis C says:

    Hello Caltalin,

    May i have the full version quote please?

  26. MeleaB says:

    Catalin.

    This is great, and just what I was looking for.

    I need to extend this to take multiple rows of data to populate multiple forms. (Each row represents a different person who each need their own populated form.)

    You have given me a great starting point. I’ll research further to figure out how to do this with a VBA macro.

    Cheers!

  27. Amoo Mass says:

    This is great!!!

    Do you have any advice on using Excel to to select and populate a customized PDF template form and then save it as a new PDF file (with a custom file name) without ever leaving Excel?  If so, I am looking at ultimately creating a macro button that does it all in one step.

    • It can be done, but with Adobe Reader is more dfficult, if you have many fields, you have to create an FDF file from vba. The easiest way is with Adobe Acrobat Pro version, you have almost total control from Excel.
      I will post tutorials for these 2 versions, but this will be next year.
      Cheers,
      Catalin

  28. Christos says:

    Hi, Catalin

    First of all thank for the time you consumed for us to share that option. I am new to VBA and I have one problem. When in another “sheet2” i use the “sumifs” formula and i use columns (ie A11:A) either protected or unprotected, from the protected “sheet1”, autoexpand does not work.

    • Hi Christos,
      Sorry for the late reply, hope you did solved the problem by now.
      If it’s still not working, can you please share a sample file? The description is not very clear, maybe a file will help understanding your problem.
      Regards,
      Catalin

  29. Sue says:

    I need a personal lesson on this topic! are you available for hire?

  30. Michelle says:

    stumbled across this page, and it worked like a champ on my computer. now I’m needing it to work on a mac for a co-worker and was wondering if you/someone already found the solution.

  31. Binh Binh says:

    i need fule version, quote please

  32. Dan O'Shea says:

    Please send a quote for the full version.

  33. Neao says:

    What a beautiful piece of work, may I have a full version please?

  34. Andy says:

    Hi There. Great solution! Do you have a solution that would create pages with each row and fill in the row below (similar to a mail merge)?

    • Hi Andy,
      No, but you can do it in a different way: if you have a template PDF with 1 page (or more), you can fill it with data for all your table data rows, when all pdf’s are filled, it’s very easy to merge them into a single PDF.
      Regards,

      • Scott Vang says:

        This product is excellent !!

        Can you provide the instructions on how to print all of the rows in the data tab??

        Thanks 🙂

        • Hi Scott,
          Look after the FillAll code in the comments below.

          Cheers,
          Catalin

          • Scott Vang says:

            Hi Catalin, I have located the code to fill all as you suggested but I am not putting the code in the correct location 🙁 Can you give me some guiidance??

            Thank you in advance. 🙂

          • Hi Scott,
            You have the instructions here.
            I’ll repeat them for you:
            Press Alt+F11, this will open visual basic Editor.
            On left side, in Project Explorer, you should see a list of Sheet modules and 2 standard modules.
            Double click on FillForms module, on code window (opens on right side) scroll down to the end of codes, paste your codes below.
            Make sure you retype the double quotes, the ones you copy from webpage are not the right ones.

  35. Kristine Smith says:

    how do you check checkboxes or radiobutton fields

    • Hi Kristine,
      Depends on the properties of the control.
      For radio buttons, if you have 10 radio buttons and they are grouped with only 1 option possible, you will have to send a value from 1 to 10.
      A checkbox might accept values like: Yes/No, True/False, Yes/Off. The easiest way to see what values that control accepts is to check the checkbox, select a value from radio buttons, and Manage Form Data-Export data – into an xml file. If you open that xml, you will see the control name and the value.
      Cheers,
      Catalin

      • David Robarts says:

        This is almost working for me. When I check the box, the exported data shows a 1 for boxes that are checked and a 0 form boxes that remain unchecked.

        Filling using 1 for the checkbox appears to work (the boxes show as checked when the form is filled), but the checkbox data is not saved. Commenting out the VBA code that closes the filled form, I exported the data from the automatically saved form and all the checkboxes were 0, manually clicking the checkbox in Adobe Acrobat twice (to uncheck then recheck) then exporting the data adds the 1 to the exported data for that checkbox.

        • Hi David,
          You have to go into form editing mode, in Acrobat or whatever app you are using, then go to that checkbox Properties, Options tab, and see what value is in the Export Value field. That Export Value can be anything: 1, True, Yes, David, On, or whatever text you imagine.
          That will be the exact value that you need to send from excel to check that box.

          • David Robarts says:

            I was using the export value, but that didn’t stick – it did show as checked in the form before closing, but did not actually get saved with the form data. I was able to fix it in VBA by using the checkThisBox method of checkbox fields:

            If ColDict.Exists(MapDict(MapKey)) Then
            With objJSO.GetField(MapKey)
            If .Type = “checkbox” Then
            .checkThisBox 0, Wks.Cells(Rw, ColDict(MapDict(MapKey))).Value: Check = True
            Else
            .Value = CStr(Wks.Cells(Rw, ColDict(MapDict(MapKey))).Text): Check = True
            End If
            End With
            End If

          • If it was checked after import and didn’t stick when closing the form, there might be some JavaScript code running and interfering with the values set by code.
            Glad to hear you solved it.
            Cheers,
            Catalin

  36. Krzysztof Noga says:

    Hello, the download link does not work. Can You please check? I would also be interested in a quote for a full version of this spreadsheet.

  37. William says:

    Auto Expand Tables on protected sheet. I would like to attach my file to see if it is possible to alter the code to fit this workbook.

    Thank you so much in advance

  38. Lukasz Majerowicz says:

    First of all. This is amazing tool. I just have a quick question, I am not sure how does the “Send Notification” “Send reminder” work i set the time and date and i am just not sure how this supposed to fuction.

  39. sjs says:

    how could I modify the VBA script to trigger on a button click instead of double clicking A1?

    • Hi,
      If you want to assign a button to run the code, you need also a new way to indicate what row needs to be transferred to PDF. Using the right click in that row already does that, the code knows what row triggered the code.

      You will have to insert in code an InputBox, where users should select the row that needs to be transferred.

  40. YasserKhalil says:

    Awesome tutorial. Can you make a video of the tutorial to be more easier and we can learn from you those magic tricks?

  41. Dale says:

    Greetings Catalin,
    I ran across your posts while searching for a solution to my own excel issue.
    I have several independent excel worksheets that are linked to a master sheet. The individual sheets’ cells are formatted to autowrap and autofill when the text in them runs long (keeping columns at a set width).
    The master sheet is 99% protected, with the exception of a few vlookup cells. When data from the independent sheets automatically populates the master, upon opening, the cells that have multiple lines do not appear in the master; I am having difficulty telling the master sheet’s cells to autofill row height to accommodate multiple lines of text, and then to shrink back down when the multiple lines change back to one line of text.

    Any suggestions would be a tremendous help.

    Thank you,
    Dale

    • Hi Dale,
      Autofit row height does not work when there are formulas in those cells. The only way I know is to calculate in VBA the necessary height for those rows, based on that cell text length and font size.

  42. Jackie says:

    Hi Catalin,

    Trying to break down the code for a more in-depth understanding. Got confused with this line of code:

    Off = 0: If Target.Row > 1 Then Off = -1

    What does Off refer to?

    I noticed that it was also used in the Offset method afterwards.

    Target.Cells.Offset(Off, 0).Locked = False Then
    Unprotect

    I look forward to your reply. Thanks!

    • Hi Jackie,
      Off is just a safety measure: when user selects the very first row, the code will no longer be able to check the previous row, in the line you mentioned: Target.Cells.Offset(Off, 0).Locked = False Then
      The normal value of the Off parameter should be -1, the code needs to check if the cell above is editable to unprotect the sheet.

  43. Christopher Bick says:

    Does this really save the time it takes to type the data into the PDF? We file numerous, non-uniform reports, would we need to set this up only once and it would be there for next year to auto fill the new information??

    • Hi Christopher,
      Each PDF file needs to be mapped with excel, the mapping is a one time process, once it’s set you can reuse it (until the PDF form structure is changed, this usually happens when the owner releases a new version). Mapping is easy, you can download the excel file and test it.
      Regards,
      Catalin

  44. Mel Brown says:

    There is no drop down list appearing in the source column. How do select what data should appear in the drop down list. Do I need to modify the code to specify what “sheet” contains the data? Thanks

    • Hi Mel,
      After you set the pdf file path in Templates List sheet, press the button from that page, and a new sheet will be created. That sheet will have dropdowns in Source column (first column), and the dropdown values are coming from the Data sheet table headers, this is your source data. The pdf fields will be listed in column B of the new sheet.

  45. Hi there, first of all thank you so much this help to save a lot of time on my work, I have a question i tested this whit a trial version of adobe acrobat pro? it’s this still going to work after my trial ends?

    • Hi Jonathan,
      That’s related to Acrobat, no idea how they stop the trial, with just warnings or limited functionalities.
      Regards,
      Catalin

      • Hello, I’m back so this happend my license trial of adobe acrobat expired two day’s ago, and I can still use this excel template, no need to buy the membership, just to let all u know. 🙂
        Thanks for this tutorial save a lot of time in my work.
        Have a great day!! 😀

        • Hi Jonathan,
          Good to know, thanks for sharing.
          For excel, only the acrobat library is needed- Acrobat.tlb, which can be found in C:\Program Files (x86)\Adobe\Acrobat xx.x\Acrobat (depends on acrobat and windows versions).
          Even if the acrobat trial is expired, the library is still there and can be used. Looks like a good clean way to get the library, without hacking the Acrobat files.
          Cheers,

  46. Jeff MacDonald says:

    May I have a quote for the full version please!
    L.E.: Nevermind. I keep getting compile errors in the lite version.

  47. Greg says:

    Hi,

    I had a sudden need of this, found your solution, enjoyed and relaxed 🙂

    Thanks a milion for this brilliant piece of VBA. The only addition would be a button to process all the rows at once. But I can easily add it myself once I stop being lazy 🙂

    Cheers

  48. David says:

    Can this be done for an Excel Binary file (.xlsb)?

    • Yes, in the PivotCache/_rels folder you will see the relationship files, the only difference is that it will have names like xl\pivotCache\_rels\pivotCacheDefinition1.bin.rels instead of xl\pivotCache\_rels\pivotCacheDefinition1.xml.rels (bin instead of xml), but they are still xml file types.

  49. SylvainR says:

    Thank you so much!
    Works great on Windows 10 Pro (build 1809), with Excel 365 and Acrobat DC Pro.

  50. Stephen K Jones says:

    Thanks for a great introduction to the subject.

    One question – I have a pdf form into which I wish to import data from Excel. I exported the form’s xml with no problem. Here’s a snippet:

    01/13/20
    01/14/20
    01/15/20
    01/16/20

    When I export the xml from Excel, “xfdf:original” has been changed to “ns1:original” and Foxit reader will not import the data. Here’s a snippet of the Excel export:

    01/13/20
    01/14/20
    01/15/20

    If I open the Excel exported xml in Notepad++ and replace “ns1” with “xfdf”, Foxit imports the modified xml with no problem.

    Is there a way to force Excel to export the xml with “xfdf”?

    Many thanks!

  51. Stephen K Jones says:

    I see that important information was purged from my snippets before they were posted. Now I need to learn how to post snippets of xml code!

  52. Stephen K Jones says:

    Thanks! I’ll repost my comment.

  53. Stephen K Jones says:

    One question – I have a pdf form into which I wish to import data from Excel. I exported the form’s xml with no problem. Here’s a snippet:

    01/13/20
    01/14/20
    01/15/20
    01/16/20

    When I export the xml from Excel, “xfdf:original” has been changed to “ns1:original” and Foxit reader will not import the data. Here’s a snippet of the Excel export:

    01/13/20
    01/14/20
    01/15/20
    01/16/20

    If I open the Excel exported xml in Notepad++ and replace “ns1” with “xfdf”, Foxit imports the modified xml with no problem.

    Is there a way to force Excel to export the xml with “xfdf”?

    Many thanks!

    • Hi Stephen,
      If you are able to edit the pdf form, the best way is to remove the spaces from field names: from “Item a” to “Itema”. The exported xml will no longer have the xfdf:original attribute that displays the original field name. Spaces are invalid chars in xml tags, that’s why the pdf application removes them and adds the original name attribute.
      Another way is to manipulate the exported xml from PDF with visual basic to pass data. This way, you will fill the exact xml exported from the PDF application, should be no problem at import.

  54. Reem says:

    Thanks!
    but I have a problem that when I open the pivotCacheDefinition1.xml.rels. I can’t delete the link! it does not allow me
    any idea?

    • How are you opening the file? Are you using a text editor, or just a browser?
      Open with Notepad, or Wordpad to make changes.

      • Lunar says:

        I faced the same problem in editing the code. It can open like a read-only on MS Edge. When I click to open with the text program, I can delete but can’t save to the same file type. Please give me your suggestion. Thank you.

        • Control Panel\Programs\Default Programs : Set your default programs by file type
          Or, from Win start button in bottom left: Settings, then search for default app, you should see an entry : Choose a default app for each file type. Then set the app for .xml instead of MS Edge to: Notepad++ (best choice IMO), or Notepad, or Wordpad.
          You can try also moving the xml file outside archive for editing, then move it back to archive when changes are saved.

  55. saul velasquez says:

    Why i can’t add more than one form? i am not getting the option shown in the first picture, “select pdf template”

    • Hi Saul,
      It’s a demo file, built for a single form.
      If you need more forms to be filled in the same time, it needs to be modified to work as you want. The multi-form version is not free unfortunately.
      Regards,
      Catalin

  56. Iris says:

    Awesome, thanks! Works like a charm on the fixed asset workbook I just created.

  57. Emilia Castillo says:

    What if the pivotCacheDefinition1.xml.rels fil
    coding does not look as the one sample picture you provided? What then?

  58. Rod says:

    Also, you can use a macro to export each existing mapping.
    There are many possibilities. The one presented in this article describes a way to automatize this process for users that does not have Acrobat Pro installed. (It is also possible to use a code that exports excel data in FDF format, and create the PDF automatically based on that FDF exported file and a PDF template)

    MAY I HAVE THIS CODE PLEASE! 😬. Thank you soooooo much.

    • Hi Rod,
      The code is very simple, you should record a macro while manually exporting the map.
      Here is an example:
      ActiveWorkbook.XmlMaps(“Contacts”).Export Url:= FullFilePath,True
      There is a way to fill pdf’s more easily, fully automated, without acrobat, see the other topic, the link is provided on top of this article (read comments too).

  59. Molly says:

    how can I do it so that the “PDF form file can be in an existing workbook in an existing folder rather than in the “form filing tools” folder?

    • Hi Molly,
      It is possible, but the code gets more complicated, embedded objects are stored in binary format in the excel archive. Code have to read the binary and convert it back to pdf, not sure if it’s a good idea to do it this way.

  60. Nate Lasko says:

    Hello
    Is there any way to do this with hundreds of records that export to separate PDFs that can be printed and or saved and emailed?

    I am doing work for a client that has a spreadsheet with hundreds of entries that need to be exported to a fillable pdf

    Thanks for your help!

    • Hi Nate,
      At the beginning of this article there is a link to another post that describes a more automated solution. It will do what you need, but requires Adobe Acrobat Pro. The good news is that you can install a trial of Acrobat Pro, the excel solution will still work even trial period ends and you never buy a full Acrobat license.

  61. duhmarus says:

    This is amazing! I wish I could have it merge a bunch of documents at the same time but still, this is a major time saver. I’m teaching myself VBA code now and can’t wait until I learn how to edit this one.

    GIRLS WHO CODE!!

    • It is amazing, congrats 🙂
      Good luck, and have fun!
      Well, it’s not always fun, frustrating sometimes, but when you make it through, beats everything.
      Girls power, even better.

  62. Hi, can i have the full version quote please? Any trial available?

  63. Tobe says:

    Hi,
    I stumbled on your page and this is exactly what I have been looking for. Thanks a lot for sharing the knowledge.

    I downloaded the zip and followed your instruction. I double-clicked on each name and I saw each folder was created for that particular name. What can I do to automate the creating of all PDF’s for all names instead of double-clicking one name at a time? Please advise. Thanks a lot

    • Hi Tobe,
      You can use this code that can send all rows to pdf’s:

      Sub FillAll()
      Dim Sh As Worksheet, tbl As ListObject, Cell As Range
      Set Sh = ThisWorkbook.Worksheets("Data")
      Set tbl = Sh.ListObjects(1)
      For Each Cell In tbl.ListColumns(1).DataBodyRange
      FillSelectedForms Sh, Cell.Row
      Next
      End Sub

      Q: “Also, how do I add more columns and rows on tab Employee Information. Thanks”
      The fields are extracted from the pdf form in the Employee_information sheet, which contains the mapping between the data table and the pdf form. Id does not make sense to add rows and columns in the employee information sheet, these are added automatically from the pdf, when you use the button from “Templates List” sheet to update the pdf template used. If your pdf has more fields, they will show up in the mapping sheet. If you manually add rows in the Employee Information table, this action will NOT add new fields in the PDF form…

      Please read again the article, you might be misunderstanding some things, the application is driven by what is in your pdf form, this is not a pdf form designer.
      If you want to use another pdf, go to templates list, use the button to select another pdf form, change the data table to the data structure needed for the new form, then do the mapping in the new sheet.

  64. Tobe says:

    Hi,
    Thank you very much for your reply. I just want to let you know that I have started enjoying your Excel PDF Mail Merge more and more everyday. So far I have played with different forms and they are all successfully created. As far as the codes, may I ask you to show me where to insert these codes? I really appreciate your sharing of knowledge.

  65. abss123 says:

    I have adopted that approach to my xlsm file.
    I have hidden my worksheets with setting xlsveryhidden. At first i have an info sheet advising users to enable content to unhide my worksheets.
    I have sent my xlsm template to other users to work with their pcs.
    However when they open xlsm workbook they get the
    enable content prompt on top but my worksheets are not hidden.
    They have same excel 2016 version as i do.

  66. abss123 says:

    This approach works successfully in my pc though.

  67. Adalia VondemBerg says:

    Catalin,

    I’ve got one cell that with the code on, the data validation is not copied down onto the new row (all other data validations copy down just fine), but it works with the code off.

    Can you point me in the right direction of where to look and what to look for to fix it?

  68. Jonathan says:

    Hi Catalin! Thank you so much for this. I included the code to send all rows to PDF but is there a way to disable the “ok” button confirming the creation of each pdf? Alternatively, is there a code to just click it each time? Thank you again.

    • Hi Jonathan,
      I suggest searching the code for the message you are seeing, for example, using Ctrl+F (same old search shortcut in excel works in visual basic too) to search for “created successfully!” will take you to that line. Put an apostrophe at the beginning of the line will disable it.
      Make sure you set the search to look into entire project.
      I could just indicate the position of the line, but don’t want to spoil your fun 🙂
      Cheers,
      Catalin

  69. Tobe says:

    Hi Catalin,
    I have played with your EXCEL codes and a single click on a row to send to PDF or send all rows to PDF. Recently, I have thought about how to send some selected rows to PDF because let’s say there are 100 rows (records) and after sending all to PDF, we realize that only 10 rows need to be corrected due to typing errors. So, instead of sending single row at a time or sending all rows again, there should be a way to send just selected rows to PDF. If you have any code or suggestion, please share with us. Thanks a lot

    • Hi Tobe,
      There are many ways: you can add a column with values of 1 for the rows you want to send, and check that column value before sending the row to pdf.
      Or, you can filter the table to keep visible only the rows you want to send. In code, simply check the row height: if it’s 0, don’t send it.
      Cheers,
      Catalin

  70. Tobe says:

    Thank you very much for a very quick response. I decided to use a filter and it showed only 10 records and hid the other 90 records. Then I used this codes below and it sent all 100 records to PDF instead of 10 wanted records.

    Sub FillAll()
    Dim Sh As Worksheet, tbl As ListObject, Cell As Range
    Set Sh = ThisWorkbook.Worksheets(“Data”)
    Set tbl = Sh.ListObjects(1)
    For Each Cell In tbl.ListColumns(1).DataBodyRange
    FillSelectedForms Sh, Cell.Row
    Next
    End Sub

    • Hi Tobe,
      As mentioned, you have to adjust the code to check the row height:
      Sub FillAll()
      Dim Sh As Worksheet, tbl As ListObject, Cell As Range
      Set Sh = ThisWorkbook.Worksheets(“Data”)
      Set tbl = Sh.ListObjects(1)
      For Each Cell In tbl.ListColumns(1).DataBodyRange
      If Cell.RowHeight > 0 Then FillSelectedForms Sh, Cell.Row
      Next
      End Sub

      This will allow you to use filters to send only visible data.
      Cheers,
      Catalin

      • Christopher Pham says:

        Hi Catalin, Thank you so much for sharing your knowledge. I am new to VB so I am learning your code. I successfully generated many PDF files using your code above. However, I tried to find the location in the code to automatically set the OK button value to “clicked” or something like that. If the spreadsheet has 100 rows then the users must click OK 100 times executing the current code. Thank you in advance!

        • Christopher Pham says:

          Actually, I tried the command Application.SendKeys (“{enter}”) and it works.
          I placed it inside the ‘Open the PDF file. like this at the end, but I still have to press OK one time at the beginning:
          End If

          MsgBox ResponseText, vbInformation, “Finished Form Filling”
          End If

          ‘added by Chris Pham
          Application.SendKeys (“{enter}”)

          objAcroAVDoc.Close True

  71. Tobe says:

    I just want to say Thank You Very Much for your help. Your script is very powerful. Thanks a lot.

  72. Tobe says:

    Yes I can do some testing. Thanks

  73. James Goodman says:

    I had the same problem and found this tool which I find is quite convenient: http://www.pdfmailmerger.com

    It doesn’t require any tech knowledge… I hope no one minds if I post this link here 🙂

  74. Daniel says:

    What is a full version cost, please send me.

  75. Hi Catalin,

    Great tool, I am just starting to work with it and have 2 questions:

    1. Is there any way to concatenate 2 fields in excel to 1 field in the PDF?

    2. It does not seem to be able to locate the files if stored on Onedrive, I use Office365, is there a workaround or is this an issue with VBA?

    I am not a programmer, but have some knowledge.

    • 1. Yes, best way is to add a new column in your data table, with those 2 columns concatenated, then use this new column for mapping, not the original columns.
      2. On OneDrive, ThisWorkbook.Path might return the “https://d.docs.live.net/” path instead of local path, so you have to replace the http path with the local onedrive path.
      You can find solutions on web. Basically, Environ(“OneDrive”) should provide the onedrive local path, so you can use this to replace the http path.
      Note that for OneDrive business, (office 365), there are more than 1 registry keys:
      Environ(“OneDriveCommercial”)
      For OneDrive Personal, use: Environ(“OneDriveConsumer”) if you have also a OneDrive business account.
      All onedrive keys can be found in registry: Computer\HKEY_CURRENT_USER\Environment

  76. Michael Hopkins says:

    Hi Catalin,

    I wanted to say thank you for making this code freely available to the public. I am playing around with this and have gotten the basic premise. I just have this thorny issue: I have a .xlsx file in a SharePoint library that I linked to the Fill PDF workbook. I now want to take the last line that is created in the linked sheet and update the “data” sheet. Also, I want to have the same attributes from the original three rows (2,3,& 4) for any new row added (as I type this I have a feeling I will have to adjust the code under ‘write fields to table comment to apply the same settings to any new additional row created in the “data” sheet).

    Once again, thank you for all that you have done.

    • It’s not clear what excel version you are using.
      If it’s an excel for windows, newer versions, You should be able to connect to the sharepoint file from the Fill PDF workbook using Power Query. This query should replace the data table, at each query refresh will get all data from that file, no need for additional codes to maintain data sync.

  77. Michael says:

    Hi Catalin,

    I want to first thank you for making this freely available to the public. This has really helped me start on a project for my HR department.

    I just have one issue that I am sure you will solve in a jiffy:

    How/What do I modify in the VB code to treat any new rows that are added in the “Data” sheet to have the same properties as the sample’s 2nd-3rd rows? Ex. I want to add a new employee “Jane Doe”, add all of her information in Row 5, double-click on her name and have that applied to the PDF.)

    As of right now, when I add information to a row below Row 4, nothing happens when I double-click the first cell in the row (I just have the normal function of editing the text in the cell when I double-click).

    Once again, thank you for all that you do.

    • Hi Michael,
      Not sure what you mean by “new rows to have the same properties”
      The data is organized into a defined table, whenever you add a new entry under the table, it should automatically autoexpand to include the new row. If it doesn’t, check if the autoexpand functionality is disabled. The setting is not very obvious:
      File->Options->Proofing->Autocorrect Options, make sure that in Autoformat as you Type you have the checkboxes checked.

  78. Ben says:

    Hi Catalin,

    I’m using C# instead of VBA. From what I’ve read, it looks like you are using the system clipboard. I was wondering if you can use the office clipboard instead.

    I’m trying to keep the “marching ants” around the copied selection and keep the Paste Special option available.

    Thanks!
    Ben

    PS Here is my C# code incase it’s helpful.
    [System.Runtime.InteropServices.DllImport(“User32”)]
    private static extern long OpenClipboard(long hwnd);

    [System.Runtime.InteropServices.DllImport(“User32”)]
    private static extern long CloseClipboard();

    • Hi Ben,
      The only purpose of opening the clipboard is to preserve the content for copy-paste made with the usual Ctrl+C – Ctrl+V, I am not using the content in any way. I never tested if the office clipboard gets cleared when a procedure runs, is it?

  79. Ben says:

    To be honest, I’m not sure. Before the code runs to to protect/unprotect the sheet, I have multiple paste options (paste, paste values, paste formulas, etc.). After the sheet is protected/unprotected, I only have two paste options (paste and match destination formatting).

  80. DAVID PICKETT says:

    no @ operator?

    • Hi David,
      Implicit intersection operator @ was introduced recently, after a major upgrade of Excel’s formula language, along with the introduction of new formulas that may return more than a single value, known as array formulas.
      At the time when this post was written, this operator did not existed.
      However, the implicit intersection concept exists in excel, only now has an explicit symbol.
      Thank you for pointing this, I appreciate.
      Cheers,
      Catalin

  81. Tony says:

    I tried replicating this code but im getting a DEBUG Error when i goes to the following code:

    For Each Cell In Range(“Map[Field]”)

    Run-time error ‘1004’:
    Method ‘Range’ of object’_Global failed.

    I have a range called Map so i dont understand why it doesnt work.

    Thanks
    Tony

    • Hi Tony,
      That Map name refers to a Range or a defined table? Range(“Map[Field]”) is referring to a column in table, not to a normal named range.
      If data is just in normal cells, not in a defined table, the table range syntax will not work, you will have to select that column and name it MapField for example, Range(“MapField”) will work in this case.
      Or make a table from those cells.

      • tony says:

        Thanks Catalin
        thanks i will try that.
        Looking at your code, i can’t see where you have defined your Map[Field] which is why i got stuck since i have limited experience in defining fields etc.
        In your Map Table sheets, are you saying the data in there is formatted as a table which is why you didnt need to define it?

        cheers
        Tony

  82. Tony says:

    Hi Catalin

    If i only wanted to create one PDF (like Word Mail Merge) for all the letters instead of many PDFs , how would I change that part of the code?

    Thanks Again!
    Tony

  83. Ash says:

    Hi,
    I’m interested to know more about your Excel-based CRM tool. For the paid for versions, is your code locked, or would I be able to get in there to modify stuff as necessary?

    If unlocked, I’d be very keen to know your price for this tool

  84. Van Wooten says:

    Hi Catalin,

    Newbie here, Do you run this code in sheet activate event? The Overwriting columns code, where does it run?

    Van

  85. Michel Levesque says:

    Please send me a quote for the full version.

  86. Xavier says:

    Hi Catalin
    This is such a good piece of work. Well done.
    The calendar-sheet or function in the lite version doesn’t seem to be working.
    Do I miss something?

  87. Van Wooten says:

    Thanks

  88. Xaba says:

    Hi,

    In my workbook i have 52 sheets (week 1 to week 52). Every sheet has 7 tables under each other (monday to sunday). It is used for a planning system for cars. The tables cannot be bigger than 40 rows, because this will never happen. This is why the tables are under each other.

    When i use your code it only works on the second table (tuesday). The first table as also the third to seventh table does not expand. Do you know why it is only working for table 2?

    • Hi Xaba,
      Works only for first table (by index, not position in sheet):
      Set Tbl = ActiveSheet.ListObjects(1)

      The structure you mentioned is not right, it certainly needs to be improved, you’ll need more and more complex solutions to get even simple reports from that structure.

      • Xaba says:

        Hi Catalin, Thank you for the fast reply.

        Does this mean that it is not possible to set up the code so that it expands every table?

        • It is possible, of course.
          Paste this function in a module in your project:

          Function GetTable(Cell As Range) As ListObject
          Dim tbl As ListObject
          On Error Resume Next 'try to see if the cell is within a table
          Set tbl = Cell.ListObject
          If tbl Is Nothing Then
          'try to see if the table is above 1 row:
          Set tbl = Cell.Offset(-1, 0).ListObject
          End If

          If tbl Is Nothing Then
          'if still nothing, it might be 1 cell to the left:
          Set tbl = Cell.Offset(0, -1).ListObject
          'should not be in the header row
          If Not tbl Is Nothing Then
          If Cell.Row = tbl.HeaderRowRange.Row Then Set tbl = Nothing
          End If
          End If
          Set GetTable = tbl
          End Function

          Then, in the original code, change this line:
          Set Tbl = ActiveSheet.ListObjects(1)
          with:
          Set Tbl = GetTable(Target): if tbl is nothing then exit sub

          It should work now.

          • Xaba says:

            Thank you for your suggestion Catalin. Unfortunately it does not change anything. It still only works on one table.

            I’ve pasted the function in a module and have adjusted the line in the original code.

            Is there anything else i need to adjust in the renewed code? I am trying to attach the workbook, but that is not possible.

          • you replaced only in the Change procedure. Needs to be replaced in both.
            The second reason for not working is that all cells in your table are locked.
            you have to follow the instructions from the article and decide which columns are allowed to edit.

  89. Xaba says:

    I indeed forgot to change it in both procedures. It now works, but only when I’m in the row directly under the table. When the sheet is unprotected (by clicking in the cell that is unprotected under the table) and I click just somewhere random in the sheet, for example the column next to the table, it does not reprotect the sheet again.

    With the previous code that only works for 1 table, it reprotects the sheet when I click somewhere random. Can the code be adjusted for the new case so that it reprotects the sheet when I click somewhere random in the sheet?

    Besides – when I change the procedures first declarations, so that it works in ThisWorkbook, with:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    it gives the following error:
    Compile error: Named argument not found (it highlights the “drawingobjects:=”)
    Did I do something wrong?

    I am aware that this structure is not optimal. The reason for it is to give the user the possibility to have a table for every day of the year. To prevent to have to many sheets they are placed in sheets per week.

    • Instead of Protect, use Sh.Protect
      Same for Unprotect, use Sh.Unprotect
      Did you changed the cell locked status for the columns you want to allow edit?

      • Xaba says:

        By changing the protect and unprotect to Sh.protect and Sh.unprotect it now works in the ThisWorkbook event 🙂

        For the columns i want to allow editing i changed the cell locked status to unlocked. Unfortunately when the sheet is unprotected, by clicking in an unprotected cell directly under the table, it does not reprotect it when i select a random cell in the sheet. Do you know why?

  90. VC Trout says:

    You are a life saver! I was just given a project to fill in 2 lines on a PDF for 1500 locations and I’m a Volunteer with little admin background. You literally saved me thousands of hours of typing. I couldn’t figure out how to add the script to run them all at once, so I’m doing in batches of 100. Now I just have to figure out how to rename all the pdfs to the format needed.

  91. Evie Totty says:

    Hi Catalin!

    This is awesome, but I am a novice when it comes to coding Excel sheets.

    You indicate using the following to create all the PDFs at once, but where do I put the code? Is there something it should replace? Thanks in advance.

    Sub FillAll()
    Dim Sh As Worksheet, tbl As ListObject, Cell As Range
    Set Sh = ThisWorkbook.Worksheets(“Data”)
    Set tbl = Sh.ListObjects(1)
    For Each Cell In tbl.ListColumns(1).DataBodyRange
    FillSelectedForms Sh, Cell.Row
    Next
    End Sub

    • Hi Evie,
      Press Alt+F11, this will open visual basic Editor.
      On left side, in Project Explorer, you should see a list of Sheet modules and 2 standard modules.
      Double click on FillForms module, on code window (opens on right side) scroll down to the end of codes, paste your codes below.
      Make sure you retype the double quotes, the ones you copy from webpage are not the right ones.

  92. Maciek says:

    Amazing! Thank you!!!

      • Mahmoud says:

        Hi Catalin.
        Thanks for the great code you provided, but I have a comment just in case you can address it somehow.
        If the user selects more than one cell in the protected column, the code will not run and he stills be able to view and edit/delete the formulas.

        • Hi Mahmoud,
          Indeed, this scenario is not covered, thanks for pointing it out.
          You can change the first line of code from:
          If Target.Cells.Count > 1 Then Exit Sub
          to:
          If Target.Cells.Count > 1 Or Target.Cells(1).Locked = True Then GoTo ExitCode

  93. Nosmada says:

    2 years later…

    Thank you for this very well demonstrated, explained and useful tutorial.

  94. Pankaj says:

    Hello,

    I am using FoxitPhantom to create formname field When I export it as CSV, it shows the field name in it but when I export the form fields as .xml and in excel, when I try to map that .xml file, no form field is shown. Why it is ?
    Thanks in advance.

    • Hi Pankaj,
      How are you “exporting the form fields as .xml and in excel ?
      Did you add the xml via Developer Tab>Source>XML Maps>Add Mapping as described in this article-Step 3?

  95. Mahmoud says:

    I fact I got the same result when I removed the line totally.

    Is this will make a negative impact on the code?

    • Removing the line completely may cause other unwanted behavior.
      If all works in your usage scenarios, no need to change anything, but if something is not working as expected, just add the line provided.

  96. Francis says:

    Hello,
    I just wanted to point out that, after having faced this same issue with an Excel workbook, and before diving into your more complex solution, I started by trying the first simple solution that you mentioned didn’t work (manually removing the file name from the Data Source reference in all pivot tables) and it seems this simple procedure now does the trick. I have edited, saved, closed and reopened the affected workbook a few times and the issue has not reappeared. Plus, I’ve checked and the data source reference stays relative. (Of course this is still a cumbersome solution if you have many pivot tables that need fixing…) I hope this might be of help to someone else coming across this issue.
    I’m running Excel for Microsoft 365 MSO (16.0.14326.20062) 32-bit, btw.
    Thanks!

  97. Will says:

    Thank you so much for posting this; this has been a lifesaver.

    I tried figuring this out on my end, but I was hoping you might be able to assist with the file name. I’m a bit new to the coding side of things and couldn’t really make out how it creates the file name.

    Is there a way for me to change the name of the file created? The lines that I use start with dates so all of my files and folders have dates based on the date in the cell. For folders this isn’t a problem, but for the file name, it would be a lot easier if I could pick a different cell, or even make my own name.

    Thanks again!

    • Hi Will,
      This line from FillForms module, WritePDFForms procedure is creating the pdf file name:
      strPDFOutPath = FolderPath & Format(Now(), “yyyy-mm-dd-hh-mm”) & ” ” & ValidateName(Fname & ” ” & .Cells(Rw, 1).Value) & “.pdf”
      This part reads the value from the first column: .Cells(Rw, 1).Value , you can read this way any column to build the name your way, just replace 1 with the column number you want.

  98. William Self says:

    Quote for the full version please.

  99. LuckyStar says:

    Hello,

    Worked out this very annoying issue for me (EXCEL 2013):
    1) Create a new excel file with the new name you want
    2) Move (not copy!) all the worksheets of the old name file into the new name file
    3) Update and save the new file
    Finished!

  100. Raymond says:

    Thank you very much for this script, very heplful!
    Is it possible to save the pdf files with the form fields flattened, so the pdf’s cannot be modified afterwards?

    • Hi Raymond,
      You have to open the form and run a JavaScript in the PDF:
      Set pdDoc = CreateObject(“AcroExch.PDDoc”)
      Set AForm = CreateObject(“AFormAut.App”)
      pdDoc.Open (Fullpath)
      Set avdoc = pdDoc.OpenAVDoc(Fullpath)
      AForm.Fields.ExecuteThisJavaScript “this.flattenPages();”

      • Raymond says:

        Hi Catalin,

        Thanks for the reply. Yesterday I managed to solve it by adding ‘FlattenForm = objJSO.flattenPages()’ just before the line where the pdf is saved (and of coarse declaring ‘FlattenForm’).

  101. Karl says:

    This is amazing. However, do you know how it can be adjusted to work on SharePoint?

    • Hi Karl,
      The easiest way is to use a cell to store the path to the destination folder and use that instead of the path used in code.
      The hard way: detect the url in registry and get the local path.

  102. Philip Hinton says:

    A simple way around the original problem is to not use Protect Sheet, but rather just validate the forbidden cells with Text Length Less than 1. This will show green validation error flags on each cell, but you can ignore those (there is a setting in Options that hides them, but then you’d not be notified about errors in other cells).

  103. Philip Hinton says:

    Oh I absolutely agree Catalin, and I’m not denigrating your article at all – indeed, you even account for users pasting data.

    However, in many situations to make a workable sheet for simple data entry by people with a brain (all my colleagues for example), it is really only necessary to prevent accidental data entry and perhaps hide the protected columns if showing them is not necessary. It also works in Julian Richardson’s “Edit in Browser” mode.

    It has worked for the five years I’ve been in my current job!

    • Well, a solution is best to be fool proof, even if it will be used by people with brain 🙂
      Data entry is a complex subject, most of the times the data storage needs to be separated from data entry (using forms for collecting data will be better).
      Editing the data directly in the archive is not the best way to do it, typing data in an area that is also used as a visual report is a bad idea, it’s like eating in the bathroom… 🙂
      What I do and recommend is collecting data using forms (even online forms), store collected data in an archive sheet, then use the archive to build reports.
      Cheers,
      Catalin

  104. Adrien says:

    Thanks so much for this! The unzip/edit procedure worked perfectly to repair a file that was causing massive headaches.

    For what it’s worth this was on Excel 2021 on a Mac, and after reopening the edited file I was prompted to “Repair” it which seemed to work and produced a report showing the broken pivot having been removed. The (very large) workbook also runs dramatically faster afterwards.

  105. Ivan says:

    Hi Catalin,
    I’m far from an expert, but I’m trying your method. Unfortunately your example file ‘Autoexpand table on protected sheet.xlsm’ doesn’t work for me: If I protect the sheet and then write something in a cell in the first row under the table, the table does not autoexpands…

  106. Polina says:

    Hello Catalin,

    Thank you very much for your article!

    I am just wondering which part is to be deleted so that the Excel file is not subsequently corrupted. Apparently, I have less content in my code that is is displayed in your example.

    My code looks like as follows:

    Could you please advise which part should be removed?
    If you could help me with this issue, I would be really grateful.

    Thanks,
    Polina

  107. Britt says:

    figured out that I don’t have this option on excel for mac, leaving a note so other mac users need to find an alternative.

  108. Fernando says:

    Catalin, you’re a hero and a genious!!!
    Thank you so much for this, simply amazing!

  109. Ariel says:

    Hi Catalin,
    I tried your template and it works like a charm but the Protection of the sheet is removed after adding the rows automatically. Is there any way to re-protect the sheet after adding the rows? Thank you.

  110. David Phillips says:

    Hello! First of all let me say AWESOME! This looks really good and I hope I can use it.

    Unfortunately, I’m getting a general error at the point at which I believe it is trying to save the file. It opens the PDF template and populates it successfully. I don’t really want to have to manually save each form. I do not believe that my form was made with Lifecycle so I don’t know if that’s the issue or not or if it’s something else.

    The Error Number is 424 and the description is Object Required and it occurs in the WritePDFForms routine in this section:
    For Each MapKey In MapDict.Keys
    If ColDict.Exists(MapDict(MapKey)) Then objJSO.GetField(MapKey).Value = CStr(.Cells(Rw, ColDict(MapDict(MapKey))).Text): Check = True
    Next

    Doing some debugging, it seems that the MapDict has one extra key that is empty. I’ve double checked the mapping table to ensure it has the right number of fields. Any suggestions?

    • Hi David,
      It might be a bug in the Acrobat library, if the field name contains a space after the name.
      When the code fails, press debug button, then put your mouse over the MapKey item, this should display the value that failed.
      Or, type ?MapKey in immediate window and press enter.

  111. keeema says:

    how to save the letter in Word?

    • Hi,
      NewFileName should end up with “.docx” instead of “.pdf”, and instead of WdDoc.ExportasFixedFormat you should use:
      WdDoc.SaveAs FileName:=NewFileName, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False

  112. Niels Cuelenaere says:

    Been hitting a wall with this for a few hours trying to see what’s wrong but:

    “Set formfields = Aformaut.Fields” does not return any formfields for the example PDF and crashes at the next line: “For Each FormField In formfields”

    Not sure why. Debugger shows everything works fine until that line. I’m using adobe pro 64.

    I thought it might be the AFormAut 1.0 Type library reference, but adding that didn’t help either.

    Any clue what might be wrong? I don’t see anyone else reporting issues so I thought I would ask.
    The error I’m getting:

    “Fieldlist Error ‘-2147319765’.
    Automation error.
    Cannot find element. VBAProject.”

    • Karl says:

      I am also having a similar issue with this, Niels. This worked for me back in January when I set it up, but now when importing a form to extract field names, the Fieldlist error you posted occurs, and it fails to generate the fields.

      • Can you provide the pdf and the excel file so I can test it?
        Thank you

        • Jerome Colburn says:

          The problem is that the latest versions of Acrobat have eliminated an actually undocumented feature your tool, and another that I have developed independently for a client, have been relying on: the Fields object is no longer a VBA Collection that can be iterated with For Each or accessed with a numeric index as in For intFieldIndex = 1 to Fields.Count. Fields can now be indexed only by the fully qualified field name! To obtain the fully qualified field names from a form, you need to export the fields to an FDF file, then read in and parse that file … which I’ve just developed a module to do.

          • Thanks Jerome for sharing.
            I personally consider Acrobat API not reliable, just too many bugs: a field name that ends with a space throws an error in VB; if there is a value in a PDF form field, that value acts as a formatting restriction (one of the strangest things I’ve seen)
            An external library like Aspose or Syncfusion should work better.
            Cheers,
            Catalin

  113. Charlie says:

    Thanks for this awesome tool! In my template there is an image field, is there a way to add a local image file to a column so it populates the image field in my template?

    thanks in advance 🙂

  114. Nick says:

    When using the samples I get an Error 52 on line
    If Len(Dir(ThisWorkbook.Path & “\” & DestFolder, vbDirectory)) = 0 Then MkDir ThisWorkbook.Path & “\” & DestFolder

  115. Nick says:

    Sorry for delay, got pulled for another project. I tried documents and desktop and get same error.

  116. Ron says:

    Hi Catalin,
    Can you use a colon between two Named Ranges?

    Ron

  117. Jan Goldberg says:

    Could you please, send me a full version quotation? TXS

  118. J. Ski says:

    Hello, Thank you for the awesome Tool. Wanted to see if I can use this to fill a PDF with more than one page. This doesn’t seem to fill in the form fields on anything beyond page 1.

    • Hi,
      The app is not limited to 1 page, it’s limited to 1 PDF, regardless of how many fields there are in that file.
      Make sure the fields are properly mapped to the data table.
      Cheers,
      Catalin

  119. Nathan says:

    Hi Catalin,

    Thankyou very much for this great work, really love it, really helpful for me.
    I did little modification for the pdf name format and also delete the “created successfully” pop up and also turn the pdf to save directly in filled form folder (did not make any additional folder with timestamp)

    i ran this for 1100++ pdf form, i already copy the vba to auto run all the row, but it always stuck on every 276-277 row, It show pop up with :

    Run-time error ‘-2147023170 (800706be)’: Automation error
    the remote procedure call failed.

    if i click debug, it immediately show this highlighted line :

    If objAcroAVDoc.Open(StrPDFPath, “”) = True Then

    do you have solution for this ? Thanks in advance 🙂

    Cheers,
    Nathan

    • Hi Nathan,
      When that line fails, please type in immediate window and press enter:
      ?StrPDFPath

      This will print the path of the file that fails.
      You have to go to that specific file that fails and try to open it manually, see if you get some error messages.
      Make sure also that the length of the StrPDFPath string does not exceed 255 chars, in this case rename the file with a shorter name.

      Cheers,
      Catalin

      • Nathan says:

        Hi Catalin,

        After some few update from adobe, now i used adobe acrobat pro instead of DC, everything was fine except they don’t run automatically. I have to click in every row to pdf every row. Do you have any solution for this ?

  120. Jason says:

    I know this is obvious, but where does it save the documents at?

    • Hi Jason,
      The destination folder, if you read the code, is in the same folder where the excel file is saved, in a new folder named “Letters”:
      DestFolder = “Letters”
      ThisWorkbook.Path & “\” & DestFolder

      Cheers,
      Catalin

  121. GM says:

    Worked like a charm. Thanks a lot for posting this very useful utility. I started from scratch not knowing anything but basics of coding. But was able to do it with the instructions easily.

  122. Miriam says:

    Hi Catalin, I was wondering how you created the excel spreadsheet to change all information from changing the name in the dropdown list

  123. Paul says:

    Tried using this and at the Update PDF Templates List button click I’m getting this error –
    FieldList Error: -2146959355 Server execution failed VBAproject. I’m using latest Excel and Acrobat Pro versions. Is there any solution to this error?

  124. Saud Jilani says:

    Hi Catalin,
    I tried to make it work on my workbook but couldn’t succeed. It is only working on the template sheet. I thought the existing macros might be causing some interference so I created a new workbook and couldn’t work with it either.

    2ndly I need it to work with 3 tables on the sheet.

    I tried for hours and read all the comments but couldn’t work it out.

    Looking forward for your reply.

    • Hi Saud,
      Depends on how the tables are placed on sheet:
      Code should be adjusted if they are side by side, or one under another, cannot be the same code.
      Sent an email for clarifications.

      • Saud says:

        Hi Catalin,
        I didn’t receive an email from you yet except the notification of your reply. Btw, my tables are side by side.

        • The solution is simple:
          Instead of assigning the first table to the tbl variable, you have to assign the current cell ListObject.
          In code, instead of:
          Set Tbl = ActiveSheet.ListObjects(1) ‘first table in sheet
          Use:
          Set Tbl = Target.ListObject ‘current cell list object
          And of course, you have to check the result and exit sub if there is no table:
          If tbl is Nothing Then Exit sub
          You have to replace this line in both procedures: Worksheet_Change event and Worksheet_SelectionChange event.

  125. Van Wooten says:

    Like Catalin said, if they are arranged down the page, simple VBA tied to a button to unprotected, addrow, protect. With Addrow you can fill some data if you want like maybe a date.

  126. Sarah says:

    Hi Catalin,

    Thank you for sharing this for us all to use. I’m running into an issue when I update the PDF templates list using your demo files. The popup error says “No field found, the form might have been created with Live Cycle Designer, this type of form can no be filled with this tool.”

    I am using Adobe Acrobat Pro. Any guidance?

The Excel Learning Journey that I propose for you, is structured in such a way as to offer you the most practical way to learn Excel, free.

Start Your Free Excel Learning Journey!

Enter your email address to subscribe to this blog: