Button to move data from current row to a pdf

[JRSone]

We collect Permit information for each customer once their project begins. Basic info is recorded from left to right all in one row. Name, address, equipment type, installation date, etc. About 12 columns of data, all as text or a number treated as text like a zip code or permit number. There are no mathematical calculations needed. The only other data types are dates in the mm/dd/yy format. Throughout the process of the project, more info is added to the customer’s row as it progresses towards completion. Relatively simple database. What I would like to do is insert a button in each row, in a designated column, that will take data from five or six cells within that same row and put them on the next Sheet which I have set up as a form letter. I need the “click” of the button to show me the now populated form letter so I can manually make any adjustments, then save it as a PDF and put it in our customer’s folder on a shared drive. Each row is a different customer with no limit on number for the year. List could be 200 customers long or 300 customers long depending on the year. I do not want to make 200+ macros so I can have the button grab the correct cells for each row.

 

[Candyman8019]

I'm sure there's more than one way to accomplish the task, but yes.

 

[JRSone]

I've been trying that for the last 2 hours! These PDFs (this one is just a screenshot) come directly from the internet. I would absolutely LOVE to be able to take data from my Worksheet and fill out the specific fields on this form. This sheet is for Plumbing improvements and usually we need to fill out additional sheets for Electrical and Mechanical Improvements. Each one requires the same customer information in addition to some minor changes. Do I need to re-create this form in Excel and try to make things fit? I had thought this would be very possible but the Overlords at Adobe have made it impossible to interact with a PDF even when I have the full version of Adobe Acrobat

 

[Candyman8019]

can you share the workbook you're working on...I'm sure we can figure it out.

...or a link to the pdf and I'll create a new workbook.

 

[Candyman8019]

I found the form you were referring to and put this sheet together...take a look.

PopulateForm.xlsm

Microsoft Excel Workbook

1drv.ms

 

[JRSone]

You just blew my mind! That is exactly what I'm looking for. It looks like Forms simplifies the procedure of building Macros all over the place. I had seen the Form control and ActiveX control menu but hadn't explored them at all. This would allow me to keep using this set up without having to pay for/use Adobe Acrobat too. How did you name the individual labels? Do I just download the construction code document and save it as a jpeg so I can insert it to worksheet? Then, add labels everywhere necessary. Then, add a button to my first customer Sheet and use code like yours to move the cell data to the labels.

 

[Candyman8019]

Yes, I just took a screenshot of the form and pasted it into the worksheet, resized it accordingly like any other picture. Next, place activeX label fields over any field that I want to fill in based on other data. To rename the fields, once it is placed on the sheet, select it, then go to 'Properties' in the Controls section of the developer ribbon bar...right next to the design mode button. In the properties, you can change the label name, font style, etc.

One of the things I really like about this approach is that you can save that filled in sheet as a pdf really easily as well.

 

[JRSone]

This works pretty well for the simpler pdf forms. The only Issue I am having is when I go to print. I have to constantly resize it and play with the margins to get it to look right. I may be asking the wrong question, so apologies if this sounds crazy... I have 5 different Construction forms that I have combined into one 5-page document with Adobe Acrobat Super-Duper-Whatever. Each page has the same address, name, phone #, etc. Each page also has it's own unique fields that I would fill out separately. The Adobe Acrobat Super-Duper-Whatever document has form field names identical to the ones on my excel worksheet. With Excel, Adobe, or some other magical site, can I put the excel data from a row into that PDF in the appropriate fields by clicking a button I have made in Excel? I see responses discuss saving something as a CSV file but I can't figure out if that means the entire worksheet or just the row. Does Adobe Acrobat PDF just not communicate with Excel at all?

 

[Candyman8019]

I've never run into that issue. I typically set my page size and margins on the Layout ribbon for the sheet that I'm printing/saving as PDF and also set my print options before saving the workbook and then I don't need to mess with them again. As for interacting directly with acrobat...I can't really say either way.