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
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
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.
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
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
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:
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
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
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?
It is now populating the pdf, but there is a field (CellSum) that is a sum of 3 others (Cell1, Cell2, Cell3), the 3 cells are populated but the sum is not calculated automatically.
if I fill the cells by hand, the sum is calculated.
Hi Bela,
The xml import will not trigger the existing javascripts from PDF.
Best if you map and send the correct value into the CellSum field as well from xml.
Catalin
Even if data is in xml, that xml structure might not be match the structure expected by that pdf.
Make sure you map to excel the xml exported from PDF.
If still does not work, try to export the xml from another app: Acrobat, Foxit Reader.
Cheers,
Catalin
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
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
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.
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.
Hi Gavin,
The excel file itself is an archive, it’s not just a simple file, see this Microsoft article for more details: Open XML Format
You have to right click on the excel file with the problem, and choose to open with an archiver, like winrar, winzip, 7zip :http://www.winzip.com/win/en/index.htm
Then you will be able to see the components of the excel archive.
Cheers,
Catalin
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.
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
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
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.
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
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.
Hi Julian,
Protection is enabled, if you want to remove the option to enable-disable the code, simply remove this line from code:
If Sheets(“Switch”).Range(“AutoExpand”) Like “Disabled” Then Exit Sub
Cheers,
Catalin
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.
You can try recording a macro while manually exporting data, you will get the basic code, just replace static values with dynamic parameters.
Cheers,
Catalin
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
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
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.
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,
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??
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.
I have two remaining requests I would like to explore.
Is there a way to eliminate the confirmation message for each record??
Also, can the generated pdf files be placed in one folder instead of a folder for each record??
Hi Scott,
You can remove the message, just press Crtl+F to search the code for MsgBox and commnent that line.
To use a single folder, just comment or delete these 2 lines from WritePDFForms procedure:
FolderPath = FolderPath & “\” & ValidateName(Wks.Cells(Rw, 1).Value) & “\”
If FSO.FolderExists(FolderPath) = False Then MkDir FolderPath
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
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.
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
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.
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.
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.
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.
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.
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
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.
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?
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,
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 🙂
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.
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”?
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”?
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.
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.
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
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).
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.
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.
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.
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.
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.
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.
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.
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?
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
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
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
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!
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}”)
Hi Christopher,
instead of SendKeys, it makes more sense to disable the MsgBox, just comment out that line and the pop-up will never show, rather than clicking it.
Cheers,
Catalin
That’s what the excel tool I provide here does…
It can be done without the need to have a licensed Acrobat Pro version, but it requires at least a trial of Acrobat PRO installed, even if you never purchase a license.
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
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).
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.
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).
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.
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?
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).
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
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.
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?
Exactly, the Map is a defined table in worksheet, it’s not just something you declare in code.
A table can be added from Insert-Table menu from excel ribbon, or Ctrl+T shortcut.
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
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?
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.
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
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.
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.
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?
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.
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.
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
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?
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.
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!
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.
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.
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!
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();”
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’).
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.
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).
Simple indeed, but only if you are comfortable with the fact that any user can easily disable that validation, so it’s not really a protection.
Thanks for the tip
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
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.
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…
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.
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.
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.
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
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.
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
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?
When using the samples I get an Error 52 on line
If Len(Dir(ThisWorkbook.Path & “\” & DestFolder, vbDirectory)) = 0 Then MkDir ThisWorkbook.Path & “\” & DestFolder
Hi Nick,
Try using the samples outside of OneDrive or SharePoint folders, inside these folders ThisWorkbook.Path returns a Http url path instead of a local path.
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
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 :
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.
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 ?
Hi Nathan,
What do you mean by “run automatically”?
If you are using the FillAll macro, you have to run this code from a button, it does not run automatically.
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
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.
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?
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.
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.
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.
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.
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.”
Thanks for sharing this it has made my work life so much easier. I’m running into an issue when I save the file to my network so other coworkers can use it. I get a Run-time error “1001′: NotAllowedError: Security settings prevent access to this property or method. with an option of end, debug, or help button. When I hit the debug it takes me to the line of code: ClearForm = objJSO.ResetForm() ‘clear the form
Any assistance is greatly appreciated.
Thanks
Hi Mikki,
In the later versions of Acrobat the security restrictions for this method had changed. It can now only be called from a trusted context, ie a folder-level script or a trusted document.
Potential Solutions:
Security Settings in Adobe Acrobat or Reader:
Some security settings in Adobe Acrobat Reader or Acrobat DC prevent access to certain JavaScript operations.
You can adjust these settings:
Go to Edit > Preferences > JavaScript.
Ensure that Enable Acrobat JavaScript is checked.
If running on a network drive, check if there are any restrictions on executing JavaScript on external files.
File Location and Network Policies:
If the file is located on a network drive, certain group policies might restrict running JavaScript or ActiveX controls from network locations.
Try copying the file to your local drive to see if the issue persists. If it works locally, there may be network policy restrictions.
Use Trusted Locations:
In some cases, adding the network location to Trusted Locations in Adobe Acrobat or your system’s settings may resolve this issue.
Go to Edit > Preferences > Security (Enhanced).
Add the network path to Privileged Locations.
Additionally, verify that the PDF allows for JavaScript execution. Some PDFs have built-in restrictions that prevent certain methods from being called.
Permissions on the Network:
Ensure that the network or shared drive where the file is saved has the appropriate read/write permissions for all users, especially when dealing with external objects or libraries like JavaScript or ActiveX.
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
You’re wellcome, glad to hear that it was helpful 🙂
Cheers,
Catalin
A very thorough and easy to follow post, Catalin.
Cheers,
Mynda
Thanks Mynda, you’re very kind, but it’s far away from your unique teaching skills…
Cheers,
Catalin
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:
With this:
Or, you can download the sample workbook again, I updated the code for 64 bit systems.
Cheers,
Catalin
Another excellent article from THE excel guru.
Very helpful, especially debugging complex formulas.
Thanks for the tips!
You’re welcome John 🙂
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
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
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
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
I need a quote on a full version please as I need it urgently
Hi Zubayr,
You have a quote sent by mail.
Regards,
Catalin
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
it is the same with me
Can you provide the PDF and the xml file so I can check them?
Hi Catalin,
I am having the same issue. Would you mind sharing your results?
Please send the PDF and xml please.
Thank you
Dear Catalin!
It is now populating the pdf, but there is a field (CellSum) that is a sum of 3 others (Cell1, Cell2, Cell3), the 3 cells are populated but the sum is not calculated automatically.
if I fill the cells by hand, the sum is calculated.
Hi Bela,
The xml import will not trigger the existing javascripts from PDF.
Best if you map and send the correct value into the CellSum field as well from xml.
Catalin
Even if data is in xml, that xml structure might not be match the structure expected by that pdf.
Make sure you map to excel the xml exported from PDF.
If still does not work, try to export the xml from another app: Acrobat, Foxit Reader.
Cheers,
Catalin
Excel-CRM is a nice piece of software Caltalin. Well written !!!
Thank you Michael, glad to hear that you like it 🙂
Cheers,
Catalin
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
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
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.
Thank You!
Hi Catalin, thanks a ton for the solution… similar problem was resolved at our end with detailed and clear step wise instructions….
You’re welcome Prasad,
Glad to hear you managed to make it work!
Cheers,
Catalin
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
Hi Gavin,
The excel file itself is an archive, it’s not just a simple file, see this Microsoft article for more details: Open XML Format
You have to right click on the excel file with the problem, and choose to open with an archiver, like winrar, winzip, 7zip :http://www.winzip.com/win/en/index.htm
Then you will be able to see the components of the excel archive.
Cheers,
Catalin
Quote for full version please, I am just starting to investigate CRM tools.
Hi Denis, you have a quote by mail.
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.
Hi, can i have the full version quote please?
Sure, I already sent it by mail.
Cheers,
Catalin
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
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
Hi
I need a quote on a full version please help me how i can get it thanks
Can I have a full version?
Buna zia Catalin this very good multu mesk mult if you dont mind I need full version quote please.
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
Hi Julian,
Protection is enabled, if you want to remove the option to enable-disable the code, simply remove this line from code:
If Sheets(“Switch”).Range(“AutoExpand”) Like “Disabled” Then Exit Sub
Cheers,
Catalin
Hurrayyyy it works Catalin. Thanks a million for your prompt response.
Glad to hear you managed to make it work as you wish 🙂
Cheers
Catalin
Hello Caltalin,
May i have the full version quote please?
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!
You can try recording a macro while manually exporting data, you will get the basic code, just replace static values with dynamic parameters.
Cheers,
Catalin
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
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
I need a personal lesson on this topic! are you available for hire?
I can provide training, let me know what you need.
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.
Hi Michelle,
Don’t think it will work on a Mac, too many restrictions in that environment.
Catalin
i need fule version, quote please
Please send a quote for the full version.
What a beautiful piece of work, may I have a full version please?
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,
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
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.
Good afternoon Catalin,
I have two remaining requests I would like to explore.
Is there a way to eliminate the confirmation message for each record??
Also, can the generated pdf files be placed in one folder instead of a folder for each record??
Hi Scott,
You can remove the message, just press Crtl+F to search the code for MsgBox and commnent that line.
To use a single folder, just comment or delete these 2 lines from WritePDFForms procedure:
FolderPath = FolderPath & “\” & ValidateName(Wks.Cells(Rw, 1).Value) & “\”
If FSO.FolderExists(FolderPath) = False Then MkDir FolderPath
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
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.
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
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.
Hi Krzysztof,
Try to right click the link and Save Link As.., should work.
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
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.
Hi Lukasz,
Those attributes are just stored in archives, there is no external application to send reminders or notifications.
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.
Awesome tutorial. Can you make a video of the tutorial to be more easier and we can learn from you those magic tricks?
Hi,
I might, but until then you can download the sample file, you can learn a lot by analyzing the query in the Query Editor.
Cheers,
Catalin
Thanks a lot for your reply and your interest.
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.
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.
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
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.
Got it. Thank you so much.
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,
May I have a quote for the full version please!
L.E.: Nevermind. I keep getting compile errors in the lite version.
Hi Jeff,
If you’re using a Mac, you may get compile errors, it’s not for Mac.
Cheers,
Catalin
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
You’re welcome Greg,
I was lazy too, that’s why I developed this solution 🙂
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.
Thank you so much!
Works great on Windows 10 Pro (build 1809), with Excel 365 and Acrobat DC Pro.
You’re welcome, glad to hear it works for you.
Cheers,
Catalin
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!
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!
Hi Stephen,
use textarea tags: <textarea> xml schema here </textarea>
Thanks! I’ll repost my comment.
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.
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.
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.
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
Awesome, thanks! Works like a charm on the fixed asset workbook I just created.
What if the pivotCacheDefinition1.xml.rels fil
coding does not look as the one sample picture you provided? What then?
Might not be your case.
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).
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.
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.
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.
Hi, can i have the full version quote please? Any trial available?
Hi Michel,
you have a quote by mail.
Cheers,
Catalin
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.
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.
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.
Hard to say without seeing what they actually did.
Record their screens and send it over to see it.
This approach works successfully in my pc though.
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?
Hi Adalia,
Can you please provide the file? Without analyzing the file, there is not much to tell.
Cheers,
Catalin
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
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
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
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!
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
Hi Christopher,
instead of SendKeys, it makes more sense to disable the MsgBox, just comment out that line and the pop-up will never show, rather than clicking it.
Cheers,
Catalin
Hi Catalin,
Commenting out the MsgBox line indeed saves me all the mouse clicks.
Thank you very much!
You’re welcome 🙂
I just want to say Thank You Very Much for your help. Your script is very powerful. Thanks a lot.
Thank you for your kind words!
Regards,
Catalin
Yes I can do some testing. Thanks
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 🙂
Thanks for the hint, that tool did the job 🙂
That’s what the excel tool I provide here does…
It can be done without the need to have a licensed Acrobat Pro version, but it requires at least a trial of Acrobat PRO installed, even if you never purchase a license.
What is a full version cost, please send me.
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
goooood
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.
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.
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?
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).
Well, after a code runs, usually the clipboard is cleared completely, just having only the first paste option is a plus.
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
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.
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
Exactly, the Map is a defined table in worksheet, it’s not just something you declare in code.
A table can be added from Insert-Table menu from excel ribbon, or Ctrl+T shortcut.
Thanks Catalin!
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
Hi Tony,
You should use a free tool to combine the PDF files, it’s your best option.
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
Hi Catalin,
Newbie here, Do you run this code in sheet activate event? The Overwriting columns code, where does it run?
Van
Hi Van,
Code is using the SelectionChange and Change events, not sheet activate.
You will find the codes in that sheet vba module.
Please send me a quote for the full version.
Sent by mail.
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?
Hi Xavier,
Can you please be more specific?
I guess you have allowed macros to run.
Catalin
Thanks
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.
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.
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.
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?
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?
This line:
Set Tbl = GetTable(Target): if tbl is nothing then exit sub
Should be:
Set Tbl = GetTable(Target): if tbl is nothing then GoTo ExitCode
Dear Catalin,
It seems that the workbook does exaclty what I want know.
Thank you for the help and suggestions!
Kind regards,
Xaba
You’re welcome, glad to hear it works as expected.
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.
You might want to check the comments as well, you will find in comments the code to fill all records in the data table.
https://www.excel-first.com/excel-app-to-fill-out-pdf-forms/#comment-1322
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.
Amazing! Thank you!!!
You’re welcome 🙂
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
2 years later…
Thank you for this very well demonstrated, explained and useful tutorial.
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?
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.
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!
Thanks for your feedback, good to know.
Cheers,
Catalin
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.
Quote for the full version please.
Hi William,
You have one by mail, sorry for the delay.
Regards,
Catalin
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!
Thanks for sharing LuckyStar!
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();”
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’).
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.
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).
Simple indeed, but only if you are comfortable with the fact that any user can easily disable that validation, so it’s not really a protection.
Thanks for the tip
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
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.
Sounds great Adrien, glad to hear you managed to make it work.
Cheers,
Catalin
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…
Hi Ivan,
I assume macros are not disabled?
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
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.
Indeed, unfortunately Excel for Mac is not as powerful as the Windows version.
Catalin, you’re a hero and a genious!!!
Thank you so much for this, simply amazing!
You’re welcome 🙂
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.
Hi Ariel,
If you try to select a locked cell, the protection should be applied automatically.
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.
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
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.”
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
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
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 🙂
Unfortunately, no.
The Button image can be inserted via JavaScript code that is not allowed to be launched from vba.
When using the samples I get an Error 52 on line
If Len(Dir(ThisWorkbook.Path & “\” & DestFolder, vbDirectory)) = 0 Then MkDir ThisWorkbook.Path & “\” & DestFolder
Hi Nick,
Try using the samples outside of OneDrive or SharePoint folders, inside these folders ThisWorkbook.Path returns a Http url path instead of a local path.
Sorry for delay, got pulled for another project. I tried documents and desktop and get same error.
These are usually in OneDrive too.
Hi Catalin,
Can you use a colon between two Named Ranges?
Ron
Of course, this will select a range between the 2 names.
Could you please, send me a full version quotation? TXS
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
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
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 ?
Hi Nathan,
What do you mean by “run automatically”?
If you are using the FillAll macro, you have to run this code from a button, it does not run automatically.
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
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.
Thank you for your feedback, glad to hear you found it useful!
Cheers,
Catalin
Hi Catalin, I was wondering how you created the excel spreadsheet to change all information from changing the name in the dropdown list
Hi Miriam,
In C8:C15 range, there are just simple Index/Match formulas based on the dropdown value, nothing special.
Cheers,
Catalin
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?
Hi Paul,
Try to disable Acrobat protection mode in settings.
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.
Hi Catalin,
I didn’t receive an email from you yet except the notification of your reply. Btw, my tables are side by side.
Checked spam as well?
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.
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.
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?
Thanks for sharing this it has made my work life so much easier. I’m running into an issue when I save the file to my network so other coworkers can use it. I get a Run-time error “1001′: NotAllowedError: Security settings prevent access to this property or method. with an option of end, debug, or help button. When I hit the debug it takes me to the line of code: ClearForm = objJSO.ResetForm() ‘clear the form
Any assistance is greatly appreciated.
Thanks
Hi Mikki,
In the later versions of Acrobat the security restrictions for this method had changed. It can now only be called from a trusted context, ie a folder-level script or a trusted document.
Potential Solutions:
Security Settings in Adobe Acrobat or Reader:
Some security settings in Adobe Acrobat Reader or Acrobat DC prevent access to certain JavaScript operations.
You can adjust these settings:
Go to Edit > Preferences > JavaScript.
Ensure that Enable Acrobat JavaScript is checked.
If running on a network drive, check if there are any restrictions on executing JavaScript on external files.
File Location and Network Policies:
If the file is located on a network drive, certain group policies might restrict running JavaScript or ActiveX controls from network locations.
Try copying the file to your local drive to see if the issue persists. If it works locally, there may be network policy restrictions.
Use Trusted Locations:
In some cases, adding the network location to Trusted Locations in Adobe Acrobat or your system’s settings may resolve this issue.
Go to Edit > Preferences > Security (Enhanced).
Add the network path to Privileged Locations.
Additionally, verify that the PDF allows for JavaScript execution. Some PDFs have built-in restrictions that prevent certain methods from being called.
Permissions on the Network:
Ensure that the network or shared drive where the file is saved has the appropriate read/write permissions for all users, especially when dealing with external objects or libraries like JavaScript or ActiveX.