Excel Export Cells to PDF Forms (fillable)

**DarinM** · 10-01-2015, 06:15 AM

Hi all,

I have done some googling and found a way you can import data from an excel file to PDF, however, it needs to be in columns, not specific cells.

I was hoping somebody would be able to tell/show me a way to import data into the PDF from specific excel cells.

I currently have 'statements' being made from a bunch of different spreadsheets via a macro/module, and I did not make it, so it looks really confusing. The information is then pulled from all spreadsheets into 1 spreadsheet and into various cells, not into columns like the PDF import likes.

p.s. I am trying to reorganize the macro into columns, but I am getting stuck with a bunch of the ifstatements, not every customer has 10 lines of sales, so it could be 1 column or 10 columns depending on if they are new/old (10 months as an example vs 1 month).

thanks for your time

**Kenneth Hobs** · 10-01-2015, 06:42 AM

If it is a form then you only have a set number of Fields that you can fill.

I guess you have probably seen the example in the link at the end. Just adapt it to your needs. That macro uses columns (fields) and rows (records). So, if you are just creating a one-off fill, you can fill the fields from any cell that you like. I would recommend Naming your cells in that case to make filling the form easier.

http://www.excelhero.com/blog/2010/0...rm-filler.html

**DarinM** · 10-01-2015, 07:03 AM

Thanks Ken,

question, I have opened it up, and I do see the "---NAME---" portion, but i don't see the cells being named that, and I don't see where it says "b5" or column B.

My spreadsheet statement that I want converted is say

A5 Name
A6 City
B6 Province
C7 Postal Code

so they are quite scattered, not in a nice column.

would I be able to say, name those cells ---Name--- , ---City---- etc, and just replace this line with mine? I don't see how that would work (so A5 would be ----Name--- instead of A5) ?? Then would the macro find ---Name--- wherever it is, either a5 or Z10?

sFileFields = Replace(sFileFields, "---CONTACT---", vClient(1, 9))

**Kenneth Hobs** · 10-01-2015, 07:17 AM

I guess you know that it is very easy to name cells. In the top left Name Box, left of the formula bar, enter say Name, and press Enter key. Then,

sFileFields = Replace(sFileFields, "---CONTACT---", Range("Name").Value)
'or
'sFileFields = Replace(sFileFields, "---CONTACT---", Worksheets("Sheet1").Range("A5").Value)

I think that the name method is easier for coding purposes though.

**DarinM** · 10-01-2015, 08:58 AM

working slowly on this, however I think I would prefer the sFileFields = Replace(sFileFields, "---CONTACT---", Worksheets("Sheet1")Range("A5").Value code.

howver, when I put that it, it says "Compile Error: Expect: list separator or )

**Kenneth Hobs** · 10-01-2015, 09:29 AM

A period and a closing parentheses was missing.
e.g.

Sub ken()  
  Dim sFileFields As String
  Worksheets("Sheet1").Range("A5").Value = "Kenneth Hobson" 'CONTACT
  sFileFields = "As a valued customer " & "---CONTACT---" & ", we hope that we have been helpful."
  sFileFields = Replace(sFileFields, "---CONTACT---", Worksheets("Sheet1").Range("A5").Value)
  MsgBox sFileFields
End Sub

**DarinM** · 10-01-2015, 09:36 AM

Thanks Ken,

for testing before you replied, I kept going with the named cell approach.

I have tried my own statement in a new excel, copied the code from the example, and I am running into an issue now. "13 Type mismatch VBAProject".

I also have some concerns about a part in the code, i'll try and highlight for you.

I uploaded the files, the pdf is super simple for testing, looks like I can't upload that, but you can trust it is done correctly (field names are in the code).

As for my concern, I don't have a Len(VClient(1,1) .. that was from the original excel, I would like that to be pointed to cell named "Account"

' Write FDF file to disk    If Len(vClient(1, 1)) Then sFileName = vClient(1, 1) Else sFileName = "FDF_DEMOTEST"
    sFileName = ActiveWorkbook.Path & "\" & sFileName & ".fdf"
    lngFileNum = FreeFile
    Open sFileName For Output As lngFileNum
    Print #lngFileNum, sTmp
    Close #lngFileNum
    DoEvents

but before we tackle that, I guess i'd like to see the script run!

Hope you can help, sorry if this is becoming a bigger project than what you wish to assist with....thanks Ken!

**Kenneth Hobs** · 10-01-2015, 10:07 AM

I will look into this more, it is not a problem, it is what we do here.

Offhand, it would be:

If Len(Range("Account").Value) Then 
  sFileName = Range("Account").Value 
  Else: sFileName = "FDF_DEMOTEST"
End If

**DarinM** · 10-01-2015, 10:12 AM

Great thanks, and ahh, I deleted the Len part and put in just the Range... i was close!.

I await your response

**Kenneth Hobs** · 10-01-2015, 11:05 AM

I had to create a named range for Opening and set a value for Account named range. Of course I did not have the cstest.pdf file to test with using those fieldnames.

Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
  ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Private Const SW_NORMAL = 1
Public Const PDF_FILE = "cstest.pdf"

Public Sub MakeFDF()
    Dim sFileHeader As String
    Dim sFileFooter As String
    Dim sFileFields As String
    Dim sFileName As String
    Dim sTmp As String
    Dim lngFileNum As Long
    
    ' Builds string for contents of FDF file and then writes file to workbook folder.
    On Error GoTo ErrorHandler
    
    sFileHeader = "%FDF-1.2" & vbCrLf & _
                  "%âãÏÓ" & vbCrLf & _
                  "1 0 obj<</FDF<</F(" & PDF_FILE & ")/Fields 2 0 R>>>>" & vbCrLf & _
                  "endobj" & vbCrLf & _
                  "2 0 obj[" & vbCrLf
                  
    sFileFooter = "]" & vbCrLf & _
                  "endobj" & vbCrLf & _
                  "trailer" & vbCrLf & _
                  "<</Root 1 0 R>>" & vbCrLf & _
                  "%%EO"


    sFileFields = "<</T(name)/V(---NAME---)>>" & vbCrLf & _
                  "<</T(address)/V(---ADDRESS---)>>" & vbCrLf & _
                  "<</T(city)/V(---CITY---)>>" & vbCrLf & _
                  "<</T(postal))/V(---POSTAL---)>>" & vbCrLf & _
                  "<</T(opening)/V(---OPENING---)>>" & vbCrLf & _
                   "<</T(month)/V(---MONTH---)>>" & vbCrLf & _
                  "<</T(balance)/V(---BALANCE---)>>" & vbCrLf


    sFileFields = Replace(sFileFields, "---NAME---", Range("Name").Value)
    sFileFields = Replace(sFileFields, "---ADDRESS---", Range("Address").Value)
    sFileFields = Replace(sFileFields, "---CITY---", Range("City").Value)
    sFileFields = Replace(sFileFields, "---POSTAL---", Range("Postal").Value)
    sFileFields = Replace(sFileFields, "---OPENING---", Range("Opening").Value)
    sFileFields = Replace(sFileFields, "---MONTH---", Range("Month").Value)
    sFileFields = Replace(sFileFields, "---BALANCE---", Range("Balance").Value)
    
    sTmp = sFileHeader & sFileFields & sFileFooter
    
    
    ' Write FDF file to disk
    If Len(Range("Account").Value) Then
        sFileName = Range("Account").Value
        Else: sFileName = "FDF_DEMOTEST"
    End If
    sFileName = ActiveWorkbook.Path & "\" & sFileName & ".fdf"
    lngFileNum = FreeFile
    Open sFileName For Output As lngFileNum
    Print #lngFileNum, sTmp
    Close #lngFileNum
    DoEvents
    
    ' Open FDF file as PDF
    ShellExecute vbNull, "open", sFileName, vbNull, vbNull, SW_NORMAL
    Exit Sub


ErrorHandler:
    MsgBox "MakeFDF Error: " + Str(Err.Number) + " " + Err.Description + " " + Err.Source


End Sub

**DarinM** · 10-01-2015, 11:14 AM

Thanks Ken.

I just did a copy paste of that code and it came back with "MakeFDF Error: 1004 Method 'Range' of object '_Global' failed VBAProject.

I searched global in the code, didn't come up with anything. ideas?

I uploaded the pdf here, it's super simple for testing.

http://speedy.sh/Zj3FX/cstest.pdf

**Kenneth Hobs** · 10-01-2015, 12:43 PM

I could not download due to company restrictions. You can always zip one or more files and attach if size is small enough. Otherwise, dropbox.com or such can sometimes be used. I can look at it tonight at home.

This should be close.

**DarinM** · 10-01-2015, 01:03 PM

Hi Ken,

It opens, but doesn't populate the PDF...so at least it opens! haha.

didn't know you could zip here, i would of done that originally, oops!

i changed the ---name--- to just name, and that also did not work. (not sure if that matters here?)

    sFileFields = "<</T(name)/V(name)>>" & vbCrLf & _    "<</T(address)/V(address)>>" & vbCrLf & _
    "<</T(city)/V(city)>>" & vbCrLf & _
    "<</T(postal))/V(postal)>>" & vbCrLf & _
    "<</T(opening)/V(opening)>>" & vbCrLf & _
    "<</T(month)/V(month)>>" & vbCrLf & _
    "<</T(balance)/V(balance)>>" & vbCrLf

I noticed it did not generate an .FDF file in my file location as I think it is supposed to?

**Kenneth Hobs** · 10-01-2015, 04:51 PM

This seems ok. I changed it just a bit. Of course one might want to edit it more to pass certain parts as input parameter values.

Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
  ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long


Public Sub MakeFDF()
    Dim sFileHeader As String
    Dim sFileFooter As String
    Dim sFileFields As String
    Dim sFileName As String
    Dim sTmp As String
    Dim lngFileNum As Long
    Dim PDF_FILE, SW_NORMAL As Integer
    
    SW_NORMAL = 1
    PDF_FILE = "cstest.pdf"
    
    ' Builds string for contents of FDF file and then writes file to workbook folder.
    On Error GoTo ErrorHandler
                    
    sFileHeader = "%FDF-1.2" & vbCrLf & _
                  "%âãÏÓ" & vbCrLf & _
                  "1 0 obj<</FDF<</F(" & PDF_FILE & ")/Fields 2 0 R>>>>" & vbCrLf & _
                  "endobj" & vbCrLf & _
                  "2 0 obj[" & vbCrLf
                                   
   sFileFooter = "]" & vbCrLf & _
                  "endobj" & vbCrLf & _
                  "trailer" & vbCrLf & _
                  "<</Root 1 0 R>>" & vbCrLf & _
                  "%%EO"


    sFileFields = "<</T(name)/V(---NAME---)>>" & vbCrLf & _
                  "<</T(address)/V(---ADDRESS---)>>" & vbCrLf & _
                  "<</T(city)/V(---CITY---)>>" & vbCrLf & _
                  "<</T(postal)/V(---POSTAL---)>>" & vbCrLf & _
                  "<</T(opening)/V(---OPENING---)>>" & vbCrLf & _
                   "<</T(month)/V(---MONTH---)>>" & vbCrLf & _
                  "<</T(balance)/V(---BALANCE---)>>" & vbCrLf


    sFileFields = Replace(sFileFields, "---NAME---", Range("Name").Value)
    sFileFields = Replace(sFileFields, "---ADDRESS---", Range("Address").Value)
    sFileFields = Replace(sFileFields, "---CITY---", Range("City").Value)
    sFileFields = Replace(sFileFields, "---POSTAL---", Range("Postal").Value)
    sFileFields = Replace(sFileFields, "---OPENING---", Range("Opening").Value)
    sFileFields = Replace(sFileFields, "---MONTH---", Range("Month").Value)
    sFileFields = Replace(sFileFields, "---BALANCE---", Range("Balance").Value)
    
    sTmp = sFileHeader & sFileFields & sFileFooter
    
    
    ' Write FDF file to disk
    If Len(Range("Account").Value) Then
        sFileName = Range("Account").Value
        Else: sFileName = "FDF_DEMOTEST"
    End If
    sFileName = ActiveWorkbook.Path & "\" & sFileName & ".fdf"
    lngFileNum = FreeFile
    Open sFileName For Output As lngFileNum
    Print #lngFileNum, sTmp
    Close #lngFileNum
    DoEvents
    
    Debug.Print sFileName
    ' Open FDF file as PDF
    ShellExecute vbNull, "open", sFileName, vbNull, vbNull, SW_NORMAL
    'Shell "cmd /c " & """" & sFileName & """", vbNormalFocus
    
    Exit Sub


ErrorHandler:
    MsgBox "MakeFDF Error: " + str(Err.Number) + " " + Err.Description + " " + Err.Source


End Sub

**DarinM** · 10-02-2015, 12:15 PM

Weird, I copied that code, hit run, it opened my PDF but did not fill it, then I tried it again, and it said an error 70 of permissions, now I try it again and it says 1004 Method Range of object _global failed VBA Project.

I am confused :| how does that happen?

even when it ran though, it did not populate. did the pdf populate for you Ken?

**Kenneth Hobs** · 10-02-2015, 01:52 PM

When you open it, there is probably a box near the top to enable the fill. Maybe there is an option in your PDF read program to allow those. It probably depends on which one you are using. Check closely when it opens....

**DarinM** · 10-05-2015, 05:52 AM

Hi Ken,

still getting an error in Excel when I click on button 1 with the new code. if you aren't getting the error, then what could it be on my end?

1004 Method 'Range' of object '_Global' failed VBAProject

---------

edit! I just looked online quick on what that means, and i noticed I had a line called "Opening" but no cell had the name "Opening" , deleted it, and it worked.

thanks!

I'll start editing more fields and will come back here if I have an issue.

appreciate it!

------

double edit!

I did notice top right it said 'options' ... 'trust this document once or always'...

thanks Ken .. I was too excited and missed that

**Kenneth Hobs** · 10-05-2015, 06:00 AM

Sorry, I forgot that I had added a named range to take care of that missing one. I had meant to explain that in the "changed it a bit".

**DarinM** · 10-05-2015, 06:12 AM

No Problem.

!

Curious, i am trying to get a bit more fancy now (human nature).

we have different levels of loyalty program, and I was hoping that at each level, it could open up a different template (cstest.pdf) ... is that possible?

if cell 1 = gold = csgold.pdf, if cell 1 = silver = cssilver.pdf.

or is it just one size fits all?

**Kenneth Hobs** · 10-05-2015, 07:09 AM

Sure, no problem at all. Did you see how I moved the PDF_File into the Sub? You can just as easily input it into your Sub as an Input parameter.

For more advanced calls, you can add more input parameters such as an array of Names and modify the code to iterate the Names.

For the one input parameter case:

Sub Main()  
  Dim mainPDF As String
  
  Select Case LCase(Worksheets("Sheet1").Range("A2").Value2)
    Case "gold"
      mainPDF = "csGold.pdf"
    Case "silver"
      mainPDF = "csSilver.pdf"
    Case Else
      mainPDF = "cstest.pdf"
  End Select
  
  If Len(Dir(ThisWorkbook.Path & "\" & mainPDF)) = 0 Then
    MsgBox ThisWorkbook.Path & "\" & mainPDF, vbCritical, "Missing File - Macro Ending"
    Exit Sub
  End If
  
  MakeFDF mainPDF
End Sub
 
Public Sub MakeFDF(Optional PDF_FILE As String = "cstest.pdf")
    Dim sFileHeader As String
    Dim sFileFooter As String
    Dim sFileFields As String
    Dim sFileName As String
    Dim sTmp As String
    Dim lngFileNum As Long
     
     ' Builds string for contents of FDF file and then writes file to workbook folder.
    On Error GoTo ErrorHandler
     
    sFileHeader = "%FDF-1.2" & vbCrLf & _
    "%âãÏÓ" & vbCrLf & _
    "1 0 obj<</FDF<</F(" & PDF_FILE & ")/Fields 2 0 R>>>>" & vbCrLf & _
    "endobj" & vbCrLf & _
    "2 0 obj[" & vbCrLf
     
    sFileFooter = "]" & vbCrLf & _
    "endobj" & vbCrLf & _
    "trailer" & vbCrLf & _
    "<</Root 1 0 R>>" & vbCrLf & _
    "%%EO"
     
    sFileFields = "<</T(name)/V(---NAME---)>>" & vbCrLf & _
    "<</T(address)/V(---ADDRESS---)>>" & vbCrLf & _
    "<</T(city)/V(---CITY---)>>" & vbCrLf & _
    "<</T(postal)/V(---POSTAL---)>>" & vbCrLf & _
    "<</T(opening)/V(---OPENING---)>>" & vbCrLf & _
    "<</T(month)/V(---MONTH---)>>" & vbCrLf & _
    "<</T(balance)/V(---BALANCE---)>>" & vbCrLf
     
    sFileFields = Replace(sFileFields, "---NAME---", Range("Name").Value)
    sFileFields = Replace(sFileFields, "---ADDRESS---", Range("Address").Value)
    sFileFields = Replace(sFileFields, "---CITY---", Range("City").Value)
    sFileFields = Replace(sFileFields, "---POSTAL---", Range("Postal").Value)
    sFileFields = Replace(sFileFields, "---OPENING---", Range("Opening").Value)
    sFileFields = Replace(sFileFields, "---MONTH---", Range("Month").Value)
    sFileFields = Replace(sFileFields, "---BALANCE---", Range("Balance").Value)
     
    sTmp = sFileHeader & sFileFields & sFileFooter
     
     ' Write FDF file to disk
    If Len(Range("Account").Value) Then
        sFileName = Range("Account").Value
    Else: sFileName = "FDF_DEMOTEST"
    End If
    sFileName = ActiveWorkbook.Path & "\" & sFileName & ".fdf"
    lngFileNum = FreeFile
    Open sFileName For Output As lngFileNum
    Print #lngFileNum, sTmp
    Close #lngFileNum
    DoEvents


     ' Open FDF file as PDF
    'Shell "cmd /c " & """" & sFileName & """", vbNormalFocus
     
    Exit Sub
ErrorHandler:
    MsgBox "MakeFDF Error: " + Str(Err.Number) + " " + Err.Description + " " + Err.Source
End Sub

Thread: Excel Export Cells to PDF Forms (fillable)

Thread Tools

Display

Excel Export Cells to PDF Forms (fillable)

Posting Permissions