• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Excel to Populate a PDF Form [SOLVED]

J

Jeffrey Lebowski

Member
Hey,


I have a block of data in Excel, and column A has a drop-down list on each row. I want to build in a feature that opens up a PDF template form I have created whenever "Warning" is selected from this drop-down. I also think it would be really awesome if this PDF form auto-populated several fields based on information in that Excel row.


Any ideas?
 
Jeffrey


Firstly, Welcome to the Chandoo.org Forums


You may wish to have a read of the post at:

http://www.excelhero.com/blog/2010/04/excel-acrobat-pdf-form-filler.html
 
Hi, Jeffrey Lebowski!


For the first part give a look at these files (workbook and test document):

https://dl.dropboxusercontent.com/u/60558749/Using%20Excel%20to%20Populate%20a%20PDF%20Form%20%28for%20Jeffrey%20Lebowski%20at%20chandoo.org%29.xlsm

https://dl.dropboxusercontent.com/u/60558749/Using%20Excel%20to%20Populate%20a%20PDF%20Form%20%28for%20Jeffrey%20Lebowski%20at%20chandoo.org%29.pdf


For the second part I'd try the inverse path, from PDF access to Excel. Does this helps?

http://www.pdfscripting.com/public/department48.cfm


Regards!
 
Thanks for yout timely responses!!!


Hui,


I have checked that link out before but I am trying to accomplish this without having every user that uses it have to download FDF.


SirJB7,


Two errors when running macro:

System Error &H80004005 (-2147467259). Unspecified error

-and-

Compile error: Out of memory


I have attached sample xlsx and pdf(let me know if they dont work). i have successfully populated forms through saving excel file as tab-delimited and then importing in PDF. the challenge is i would like this all to be accomplished by vba that runs when the 'WARNING!' is selected from dropdown within excel. possible?


https://www.dropbox.com/s/weeq2e8n3x8kbdy/ExportTest.xls

https://www.dropbox.com/s/seiytl9e74my46i/WARNING.pdf
 
Hello Jeffrey,

You are in good hands with SirJB7 for VBA stuff... but here is one suggestion.


If you setup a worksheet to look like your form, filling it with any data from the other sheet using formulas, you could always save that form sheet as PDF, or even open it.


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"MyForm.pdf", Quality:= _

xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _

OpenAfterPublish:=True


In this case, ActiveSheet would be your form sheet. But you can set it to be whatever you need it to be.


Cheers,

Sajan.
 
Hi, Jeffrey Lebowski!


I opened my uploaded file and it works fine. I'm running Excel 2010 32bits on a Win 7 64bits, but it only matters Excel version regarding certain incompatibilities between 2007 vs 2010 about data validation. Is this what you're experiencing?

http://answers.microsoft.com/en-us/office/forum/office_2010-excel/excel-2010-2007-data-validation-compatibility/20a6e301-f5b7-459a-9ee4-867496342119

http://stackoverflow.com/questions/7235093/data-validation-set-in-excel-2010-fails-in-excel-2007

If it's that, then using named ranges as in my uploaded file should do the job.


As the method I chose uses an Adobe control that's not included in those referenced by default you should add these 2 references to your VBA project (Alt-F11, Options, References):

- Adobe Acrobat 10.0 Type Library

- Adobe Acrobat Browser Control Type Library 1.0


And about how to convince Excel to write and update a PDF file, apart from the technique described in the link posted by Hui, I couldn't find anything to persuade it to do the job. That's why I posted my last link towards how to walk the reverse path: access Excel from the PDF.


Regards!
 
@Sajan

Hi!

You're gonna make me blush, that's the idea?

Regards!

PS: BTW, gave look to my uploaded pdf file? It's in Spanish but...
 
Sajan,

That is my next best option, to save as pdf. One reservation I have about doing this is that I want to incorporate a digital signature on the pdf...I do not think that would be possible going this route.


SirJB7,

Your data validation list is still there. When I select Warning is when those errors appear. Named range did not fix it. In the VBE I tried opening UserFormPDF and get the same errors so I think that might be the root of the problem. Or it might be that I do not know what to do with the PDF file you provided? And I checked the references I already have browser control type library 1.0, but only 9.0 type library because thats the version of adobe I have.
 
Hi, Jeffrey Lebowski!


I agree that the Adobe Acrobat control in the user form is the problem. It's supposed to have backward compatibility but as per you experience it's not true.

I can't downgrade to test and check it, you can upgrade to do so, but I don't like so much the idea of running only on certain versions. I've read about issues in version 9 that were fixed in version X (10) but I don't remember exactly about what subject. I just tried a quick search but didn't find that article... But I found this, which you may find interesting:

http://www.khk.net/wordpress/2009/03/04/adobe-acrobat-and-vba-an-introduction/


Regards!
 
Thanks for the reference...although from my digging I am thinking the next best option of saving as pdf will have to be pursued.


I still need to configure the dropdown list column to trigger this macro if Warning is selected. I have tried the worksheet change event route as explained here:


http://en.kioskea.net/faq/6825-excel-run-macro-when-item-is-selected-from-dropdown-list


but it is not working. i want it set up so that each time Warning is selected, anywhere in col A, the macro runs and populates TargetSheet. Code is below, thanks for helping!

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim thisRow As Long

'here 1 is column number 1 which is column A
If (Target.Column <> 1) Then Exit Sub

Application.EnableEvents = False

thisRow = Target.Row

If Target.Value = "Warning" Then
'  copy important cells
Sheets("TargetSheet").Range("B3") = Cells(thisRow, "E")
Sheets("TargetSheet").Range("B11") = Cells(thisRow, "I")

Else
'  dont do anything if Warning not selected
Exit Sub
End If

Application.EnableEvents = True

End Sub
[/pre]
 
Hi, Jeffrey Lebowski!


In my original file the Worksheet_Change event was triggered and as it displayed an user form with an Adobe Acrobat control of other version that's why you got the error. Now if you forget about the code that refers to the user form you could still use the structure provided for that code event and then place there your new code:

-----

[pre]
Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' constants
Const ksRange = "DataList"
Const ksTrigger = "Warning"
' declarations
Dim rng As Range
' start
Set rng = Range(ksRange)
If Application.Intersect(Target, rng) Is Nothing Then GoTo Worksheet_Change_Exit
If Target.Cells.Count > 1 Then GoTo Worksheet_Change_Exit
' process
With Target
If .Value = ksTrigger Then
Load UserFormPDF
UserFormPDF.Show vbModeless
End If
End With
' end
Worksheet_Change_Exit:
Set rng = Nothing
End Sub
[/pre]
-----


Regards!
 
Hi,


Please review my code below. I have combined your code with the code that will copy certain cells to other sheet. It gives me an error code "Compile error: object required" when the code is triggered. The error points to this piece of code:


'Set thisRow = Target.Row'


I have tried defining thisRow as both Long and Double but that doesnt seem to fix it. Thanks for helping!


'Private Sub Worksheet_Change(ByVal Target As Range)

Dim thisRow As Long

' constants

Const ksRange = "DataList"

Const ksTrigger = "Warning"

' declarations

Dim rng As Range

' start

Set rng = Range(ksRange)

Set thisRow = Target.Row

If Application.Intersect(Target, rng) Is Nothing Then GoTo Worksheet_Change_Exit

If Target.Cells.Count > 1 Then GoTo Worksheet_Change_Exit

' process

With Target

If .Value = ksTrigger Then

' enter code to copy to other sheet HERE

Sheets("TargetSheet").Range("B3").Value = Cells(thisRow, "E").Value

Sheets("TargetSheet").Range("B11").Value = Cells(thisRow, "I").Value

'

'

End If

End With

' end

Worksheet_Change_Exit:

Set rng = Nothing

End Sub

'
 
Hi, Jeffrey Lebowski!

Try removing the "Set " from:

-----

Set thisRow = Target.Row

-----

since thisRow is a long variable, not an object. That should avoid that error.

Regards!
 
I have done that edit, and now the code runs completely through but the desired cells of "thisRow" are not copied to the TargetSheet.


I have triple checked that I am referencing the correct cells. Do you see any mistakes with the code below the trigger event?
 
Hi, Jeffrey Lebowski!


I did this:

a) Opened my uploaded file.

b) Renamed worksheet Hoja2 to TargetSheet.

c) Renamed private sub Worksheet_Change to Worksheet_Change1 for worksheet Hoja1.

d) Copied your code (fixing the thisRow issue) and pasted it below the renamed one.

e) Placed random values in E1:E11 and I1:I11 on worksheet Hoja1.

f) At column A on worksheet Hoja1 I changed any value from the validation list to anything different from "Warning" and nothing happened.

g) I repeated that but changing value to "Warning" and I got the values in worksheet TargetSheet.


I'm gonna update the uploaded file just up to e) so you can easily test f) and g). Please download it again from same previous link.


Regards!
 
SirJB7


I downloaded the updated workbook and I still receive the same error messages (unspecified and compile error:eek:ut of memory) when I select warning from dropdown. I think this is because of the userform and pdf incompatibilities.


Private Sub Worksheet_Change1 references this userform, but

Private Sub Worksheet_Change is essentially the same with added code.


So I removed the userform and deleted the Private Sub Worksheet_Change1 code, but now nothing happens when the copy/paste should be triggered.
 
Hi, Jeffrey Lebowski!


Let us try 3 things:

a) Would you please upload this last version of the file, with which it runs but nothing happens?

b) Please tell us which Windows version are you using (XP, Vista, 7, 8), 32 or 64 bits, which Excel version do you have, what's your hardware configuration (processor type and speed and RAM): just to discard a hardware related issue.

c) Do you have any personal macro workbook, o add-iñs or something more that's loaded whenever you open Excel? If so, could you try disabling all that stuff and try running the workbook at a clean and neat Excel instance?


Regards!
 
Doh!


I realized I hadn't saved your file since working with it. Once I saved it started working properly!


As far as I am concerned, I do not have anything that loads whenever Excel is opened. However, I do have one workbook with a lot of macros. Can you tell me why (or if) its important to set variables = nothing after they are no longer needed?


I really appreciate how helpful you were on this. Thanks a ton! ...Is there a way to mark the thread as "solved"?
 
Hi, Jeffrey Lebowski!


Destroying objects (setting them to nothing) is usually a good practice that should be accomplished by any procedure which creates them after finishing their use.


This house clean shouldn't be necessary if all the objects are declared within the procedure that uses them or if declared globally and there're further intentions to use them (a procedure creates it, and another procedure works on it). But this doesn't always happen, and memory and resources don't get cleared and free, specially when unhandled errors happen, then this fails and objects remain in memory.


Lots of links might be found googling "vba set object to nothing", most of them match in their recommendation which might be resumed to "if you're going to reuse it, don't destroy it; if not, you don't need to destroy it unless to force freeing resources in the middle of a procedure... but you'd better always do it even at the end of the procedure".


Glad you solved it at last. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.


Regards!


PS: Ninjas manually edit title topic when solved :(
 
life as a ninja must be challenging!


one last hurdle I can see that lies ahead:


This whole worksheet event code is for a worksheet that will be deleted and re-created every time the user uploads new data. What is the best way to incorporate the code so that every time this worksheet is created and named, the worksheet event code is written simultaneously?
 
Hi, Jeffrey Lebowski!

If you can't clear contents instead of deleting the worksheet, create a "template" worksheet and copy it changing the name properly.

Regards!
 
Hi, Jeffrey Lebowski!

Perhaps you want to give a look to this topic:

http://chandoo.org/forums/topic/vba-error-appears-pdf-export-from-excel

Regards!
 
You must log in or register to reply here.
Back
Top