How to populate PDF form from Excel data?
July 13, 2012 7:55 AM Subscribe
How to populate PDF form from Excel data: number of copies based on number of rows?
For work, I have to generate a lot of paperwork when we ship to a certain customer, so I have a one-page form setup to help speed it along already, and make it more professional than the hand-written copies they used before. When we ship, we usually ship in bulk, and I have to fill out the same form anywhere from 5-50 times (printing to a shared printer each time I fill it out, so I cannot leave the paperwork there until I’m ready for it). Currently I fill it out in Adobe Reader X, but could get Adobe Professional if needed.
There are 42 fields on the form itself, of which 35 are usually the same (but could change at any time, so must be editable), and of the 7 remaining fields, I have the data for 5 well ahead of time; it isn’t until the day that we ship that I have to add in the remaining two fields.
I’m pretty good working with difficult calculations and such in Excel, but have absolutely no knowledge of how to utilize VB scripts to do what I’m looking for. I came upon this website: http://www.excelhero.com/blog/2010/04/excel-acrobat-pdf-form-filler.html which helps, but not to someone who has no VB knowledge.
What I’m looking to do is this:
Say we get an order for 20 boxes, I want to be able to put all of the data into one excel file (one row per box), click one button, and have that one-page form filled out 20 times (preferably as only one output file, so I can just click “Print” once from Adobe Reader). I do not need to save the file at all after I print it. The orders we get do vary though, so I would also want the number of filled rows to determine how many pages to create: 5 rows of data = 5 pages, 17 rows of data = 17 pages, etc.
If you have any ideas, let me know. I’d like to stay away from Adobe Professional & 3rd Party Software, but I am open to it if necessary: AutoMailMerge by Evermap looks like it would do everything I want, but if it can be done free and with VB, I’d rather stick with that. Thanks for looking, and any help you can provide.
For work, I have to generate a lot of paperwork when we ship to a certain customer, so I have a one-page form setup to help speed it along already, and make it more professional than the hand-written copies they used before. When we ship, we usually ship in bulk, and I have to fill out the same form anywhere from 5-50 times (printing to a shared printer each time I fill it out, so I cannot leave the paperwork there until I’m ready for it). Currently I fill it out in Adobe Reader X, but could get Adobe Professional if needed.
There are 42 fields on the form itself, of which 35 are usually the same (but could change at any time, so must be editable), and of the 7 remaining fields, I have the data for 5 well ahead of time; it isn’t until the day that we ship that I have to add in the remaining two fields.
I’m pretty good working with difficult calculations and such in Excel, but have absolutely no knowledge of how to utilize VB scripts to do what I’m looking for. I came upon this website: http://www.excelhero.com/blog/2010/04/excel-acrobat-pdf-form-filler.html which helps, but not to someone who has no VB knowledge.
What I’m looking to do is this:
Say we get an order for 20 boxes, I want to be able to put all of the data into one excel file (one row per box), click one button, and have that one-page form filled out 20 times (preferably as only one output file, so I can just click “Print” once from Adobe Reader). I do not need to save the file at all after I print it. The orders we get do vary though, so I would also want the number of filled rows to determine how many pages to create: 5 rows of data = 5 pages, 17 rows of data = 17 pages, etc.
If you have any ideas, let me know. I’d like to stay away from Adobe Professional & 3rd Party Software, but I am open to it if necessary: AutoMailMerge by Evermap looks like it would do everything I want, but if it can be done free and with VB, I’d rather stick with that. Thanks for looking, and any help you can provide.
If you know a bit of scripting, this is pretty easy..
I've used python and reportlab a bunch of times to fill out PDF templates programatically.
Free, and much preferable to learning VB(A).
posted by wongcorgi at 10:20 AM on July 13, 2012
I've used python and reportlab a bunch of times to fill out PDF templates programatically.
Free, and much preferable to learning VB(A).
posted by wongcorgi at 10:20 AM on July 13, 2012
Questions to clarify............
- You want the number of rows to dictate the print process.
- Are you printing paper sheets or PDFs?
- In the page where you enter the data, is this some sort of master page that fills data elsewhere?
- What would you expect the max number of pages to output? You mentioned 20, but is that average, high low etc?
What I am trying to figure out is whether you need to deal with PDFs at all or do you just need a well setup spreadsheet with a few formulas, some linking and a little VBA.
posted by lampshade at 4:36 PM on July 13, 2012
- You want the number of rows to dictate the print process.
- Are you printing paper sheets or PDFs?
- In the page where you enter the data, is this some sort of master page that fills data elsewhere?
- What would you expect the max number of pages to output? You mentioned 20, but is that average, high low etc?
What I am trying to figure out is whether you need to deal with PDFs at all or do you just need a well setup spreadsheet with a few formulas, some linking and a little VBA.
posted by lampshade at 4:36 PM on July 13, 2012
This thread is closed to new comments.
Also, there are versions of Acro Pro 9 still floating out there for purchase. All legal and legit. If you can get by with that and AMM, it drops the cost down significantly. Last time I checked around was earlier this year, so maybe in the last few months something has changed,but the prices were fairly stable for teh year before that.
As far as VBA and such, that is also possible. I will look more closely at that link and report back. It looks promising and I too would like a solution for this. Normally I would buy AMM (I already have Acro X) but I cannot justify the AMM cost for the one or two jobs that come up per year. Shame it is not a <$50 add-in...I would snatch it up in a heartbeat.
posted by lampshade at 9:27 AM on July 13, 2012