A lending hand to PDF FDF

[doubtfire]

I am doing a project to print PDF format docs, looping Excel rows to create PDF docs and save automatically.
From the net I have learnt to use FDF as a media to create PDF docs WITHOUT having Acrobat, only Adobe reader.
Anyone having this experience please help and share the whole process.
I have been testing and it seems it works, the only stage I am missing is to
use ShellExecute to close the PDF and save automatically.

[Bob Phillips]

If you have Excel 2007/2010, it has a print to PDF option.

[Paul_Hossler]

http://cutepdf.com/


Even the freeware version is very good. Add's a pseudo-printer that you can print to as if it were a 'real' printer, except you have a PDF file at the end.

No advertising watermarks or other limitations.

The pro version is $50 and offers a lot more features

Paul

[doubtfire]

Thanks guys for the response.
My question is Excel 2007 provides printing to PDF, does it need to have Adobe Acrobat present, or just Adobe Reader is fine if I am provided by my customer a fillable PDF form?
If the above is Yes could you please supply more details?
Thank you again.
I need to provide a solution to a group of fifty users therefore using the Excel features and without a third party software/driver (no matter fee/free) seems to my option.
I am open to any assistance.

[Kenneth Hobs]

It would be easier to do it all in Excel and fill that out.

Keep in mind that not all form fields created by others are fillable by you.

There are some 3rd party programs that can fill them. I have not found one that fits my needs.

I create a vb.net executable that can use xml to fill one that is fillable. The example was made for WordPerfect. The file can be called by the Shell() command. If not parameters are passed, it will show a MsgBox() that explains the input and output parameters. That file is at: http://www.wpuniverse.com/vb/showthr...s-5-iTextSharp

[doubtfire]

Ken,

Thank you for the assistance.
If you do not mind, I have attached code which I obtain through the net as attached.
It works and the thing that I got stuck is that the last part open the Adobe application and hanging there for a confirmation to save Y/N.
" ShellExecute vbNull, "open", sFileName, vbNull, vbNull, SW_NORMAL"
I need to program automatically (within a loop of an Excel range) saves one by one for the PDF format.
Could you help or other suggestion ?

[vba]
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 = "TaxForm.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
Dim vClient As Variant


' 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(f1_01(0))/V(---NAME---)>>" & vbCrLf & _
"<</T(f1_02(0))/V(---EIN_LEFT---)>>" & vbCrLf & _
"<</T(f1_03(0))/V(---EIN_RIGHT---)>>" & vbCrLf & _
"<</T(f1_06(0))/V(---OIN---)>>" & vbCrLf & _
"<</T(f1_04(0))/V(---TRADE_NAME---)>>" & vbCrLf & _
"<</T(c1_1(0))/V(---SEASONAL---)>>" & vbCrLf & _
"<</T(f1_05(0))/V(---STREET_ADDRESS---)>>" & vbCrLf & _
"<</T(f1_07(0))/V(---CITY_STATE_ZIP---)>>" & vbCrLf & _
"<</T(f1_08(0))/V(---CONTACT---)>>" & vbCrLf & _
"<</T(f1_09(0))/V(---PHONE_LEFT---)>>" & vbCrLf & _
"<</T(f1_10(0))/V(---PHONE_RIGHT---)>>" & vbCrLf & _
"<</T(f1_11(0))/V(---FAX_LEFT---)>>" & vbCrLf & _
"<</T(f1_12(0))/V(---FAX_RIGHT---)>>" & vbCrLf


vClient = Range(Selection.Row & ":" & Selection.Row)

sFileFields = Replace(sFileFields, "---NAME---", vClient(1, 2))
If Len(vClient(1, 3)) > 3 Then
sTmp = Replace(vClient(1, 3), "-", "")
sFileFields = Replace(sFileFields, "---EIN_LEFT---", Left$(sTmp, 2))
sFileFields = Replace(sFileFields, "---EIN_RIGHT---", Mid$(sTmp, 3))
Else
sFileFields = Replace(sFileFields, "---EIN_LEFT---", vbNullString)
sFileFields = Replace(sFileFields, "---EIN_RIGHT---", vbNullString)
End If
sFileFields = Replace(sFileFields, "---OIN---", vClient(1, 4))
sFileFields = Replace(sFileFields, "---TRADE_NAME---", vClient(1, 5))
sFileFields = Replace(sFileFields, "---SEASONAL---", vClient(1, 6))
sFileFields = Replace(sFileFields, "---STREET_ADDRESS---", vClient(1, 7))
sFileFields = Replace(sFileFields, "---CITY_STATE_ZIP---", vClient(1, 8))
sFileFields = Replace(sFileFields, "---CONTACT---", vClient(1, 9))
If Len(vClient(1, 10)) = 10 Then
sTmp = Replace(vClient(1, 10), "-", "")
sFileFields = Replace(sFileFields, "---PHONE_LEFT---", Left$(sTmp, 3))
sFileFields = Replace(sFileFields, "---PHONE_RIGHT---", Mid$(sTmp, 4, 3) & "-" & Mid$(sTmp, 7))
Else
sFileFields = Replace(sFileFields, "---PHONE_LEFT---", vbNullString)
sFileFields = Replace(sFileFields, "---PHONE_RIGHT---", vbNullString)
End If
If Len(vClient(1, 11)) = 10 Then
sTmp = Replace(vClient(1, 11), "-", "")
sFileFields = Replace(sFileFields, "---FAX_LEFT---", Left$(sTmp, 3))
sFileFields = Replace(sFileFields, "---FAX_RIGHT---", Mid$(sTmp, 4, 3) & "-" & Mid$(sTmp, 7))
Else
sFileFields = Replace(sFileFields, "---FAX_LEFT---", vbNullString)
sFileFields = Replace(sFileFields, "---FAX_RIGHT---", vbNullString)
End If

sTmp = sFileHeader & sFileFields & sFileFooter


' Write FDF file to disk
If Len(vClient(1, 1)) Then sFileName = vClient(1, 1) Else sFileName = "FDF_DEMO"
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
[/vba]

[Kenneth Hobs]

Make an example Excel file and post that. That will make it easier to help you and provide a more exact solution.

That code is just creating the form file. It has some redundant parts in it.

The last part opens the file using the Windows FDF associated program. Did you want to open it or print it? It can be printed with Shell() using adobe reader by command line switches.

[doubtfire]

Thanks again.
I need to extract Excel data row by row, each row data to be inserted into a customized and fillable PDF form, save the PDF under different name one by one WITHOUT opening it. My users have only Adobe Reader.
And I know FDF is an option and the previous info is obtained from the net. The following is the site
http://www.excelhero.com/blog/2010/0....html#comments
Thank you.

[Kenneth Hobs]

Seems like I did that sort of thing with pdftk but some forms had protected fields.

The only way that I know to do that is to open it and then use a Sendkeys() type of method. I don't like SendKeys() solutions. Focus and timing can be off and gum things up. With SendKeys() you need UAC disabled.

If you want to try pdftk or another 3rd party program, we can try that.

I did this with Adobe Acrobat so the menu commands might be a bit different in Adobe Reader.
[vba]Declare Function FindExecutable Lib "shell32.dll" Alias "FindExecutableA" _
(ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long

Public Sub MakeFDF_Ken()
Dim s As String, rc As Long, pdfName As String, rc2 As Long, r As Range, c As Range
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 vClient As Variant
Dim PDF_FILE As String

PDF_FILE = "f8655.pdf"

' Builds string for contents of FDF file and then writes file to workbook folder.
On Error GoTo ErrorHandler

Set r = Range("A6", Range("A" & Rows.Count).End(xlUp)).CurrentRegion
For Each c In r.Rows
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(f1_01(0))/V(---NAME---)>>" & vbCrLf & _
"<</T(f1_02(0))/V(---EIN_LEFT---)>>" & vbCrLf & _
"<</T(f1_03(0))/V(---EIN_RIGHT---)>>" & vbCrLf & _
"<</T(f1_06(0))/V(---OIN---)>>" & vbCrLf & _
"<</T(f1_04(0))/V(---TRADE_NAME---)>>" & vbCrLf & _
"<</T(c1_1(0))/V(---SEASONAL---)>>" & vbCrLf & _
"<</T(f1_05(0))/V(---STREET_ADDRESS---)>>" & vbCrLf & _
"<</T(f1_07(0))/V(---CITY_STATE_ZIP---)>>" & vbCrLf & _
"<</T(f1_08(0))/V(---CONTACT---)>>" & vbCrLf & _
"<</T(f1_09(0))/V(---PHONE_LEFT---)>>" & vbCrLf & _
"<</T(f1_10(0))/V(---PHONE_RIGHT---)>>" & vbCrLf & _
"<</T(f1_11(0))/V(---FAX_LEFT---)>>" & vbCrLf & _
"<</T(f1_12(0))/V(---FAX_RIGHT---)>>" & vbCrLf


vClient = c

sFileFields = Replace(sFileFields, "---NAME---", vClient(1, 2))
If Len(vClient(1, 3)) > 3 Then
sTmp = Replace(vClient(1, 3), "-", "")
sFileFields = Replace(sFileFields, "---EIN_LEFT---", Left$(sTmp, 2))
sFileFields = Replace(sFileFields, "---EIN_RIGHT---", Mid$(sTmp, 3))
Else
sFileFields = Replace(sFileFields, "---EIN_LEFT---", vbNullString)
sFileFields = Replace(sFileFields, "---EIN_RIGHT---", vbNullString)
End If
sFileFields = Replace(sFileFields, "---OIN---", vClient(1, 4))
sFileFields = Replace(sFileFields, "---TRADE_NAME---", vClient(1, 5))
sFileFields = Replace(sFileFields, "---SEASONAL---", vClient(1, 6))
sFileFields = Replace(sFileFields, "---STREET_ADDRESS---", vClient(1, 7))
sFileFields = Replace(sFileFields, "---CITY_STATE_ZIP---", vClient(1, 8))
sFileFields = Replace(sFileFields, "---CONTACT---", vClient(1, 9))
If Len(vClient(1, 10)) = 10 Then
sTmp = Replace(vClient(1, 10), "-", "")
sFileFields = Replace(sFileFields, "---PHONE_LEFT---", Left$(sTmp, 3))
sFileFields = Replace(sFileFields, "---PHONE_RIGHT---", Mid$(sTmp, 4, 3) & "-" & Mid$(sTmp, 7))
Else
sFileFields = Replace(sFileFields, "---PHONE_LEFT---", vbNullString)
sFileFields = Replace(sFileFields, "---PHONE_RIGHT---", vbNullString)
End If
If Len(vClient(1, 11)) = 10 Then
sTmp = Replace(vClient(1, 11), "-", "")
sFileFields = Replace(sFileFields, "---FAX_LEFT---", Left$(sTmp, 3))
sFileFields = Replace(sFileFields, "---FAX_RIGHT---", Mid$(sTmp, 4, 3) & "-" & Mid$(sTmp, 7))
Else
sFileFields = Replace(sFileFields, "---FAX_LEFT---", vbNullString)
sFileFields = Replace(sFileFields, "---FAX_RIGHT---", vbNullString)
End If

sTmp = sFileHeader & sFileFields & sFileFooter


' Write FDF file to disk
If Len(vClient(1, 1)) Then sFileName = vClient(1, 1) Else sFileName = "FDF_DEMO"
pdfName = ActiveWorkbook.Path & "\" & sFileName & ".pdf"
If Dir(pdfName) <> "" Then Kill pdfName
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
s = Quote(ExePath(sFileName)) & " " & sFileName
rc = Shell(s, vbNormalFocus)
AppActivate rc, True
SendKeys "%F", False
SendKeys "A", False 'Save As
SendKeys pdfName, True
SendKeys "%S", True
SendKeys "%F", True
SendKeys "X", True
Kill sFileName
DoEvents
Next c
Exit Sub

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

End Sub

Function ExePath(lpFile As String) As String
Dim lpDirectory As String, sExePath As String, rc As Long
lpDirectory = "\"
sExePath = Space(255)
rc = FindExecutable(lpFile, lpDirectory, sExePath)
sExePath = Left$(sExePath, InStr(sExePath, Chr$(0)) - 1)
ExePath = sExePath
End Function

Function Quote(str As String) As String
If Left(str, 1) = """" Then
Quote = str
Else: Quote = """" & str & """"
End If
End Function
[/vba]

[doubtfire]

Thanks for the precious time and efforts.
I would definitely test it.
THANK YOU!

[mr_chips]

Hi Guy's!



I have been using exactly the same piece of code from excel hero to auto fill some of my own pdf forms. I have a little knowledge of VBA but nothing great, what I'd like to achieve is for the code to auto fill the PDF and the save as a .pdf file with the client's name as the file name. The way i'm working I only need it to do this for one client at a time as I have one client's details in the excel sheet but it appears multiple times in multiple forms.

I enter the client details, run the code to enter the details i various locations in a set of 5 forms. I'm left with 5 FDF files, I then move these to a client folder but also have to move the originals, then move the originals back. It would be great to run the code and for it to create 5 PDF's. I only need the code to do this for one client at a time, no loops needed.

Any help would be greatly appreciated!! My VBA skills are slowly improving

Many thanks
Mark

[Bob Phillips]

If you have Excel 2007/2010 it has a save as PDF option.

You can also use SaveAs to avoid copy the originals back.

[mr_chips]

If you have Excel 2007/2010 it has a save as PDF option.

You can also use SaveAs to avoid copy the originals back.

Hey, thanks for the reply.

^That's not quite what I want.
I press a command button in the sheet and It creates 5 FDF files, already populated with my data. Within the folder that my excel file and the blank pdf's live, I then get 5 FDF files. If i open an fdf file it opens as a PDF, I'd then have to go to save as (in adobe reader) and re-type the client file name.

I would love to have the code to automatically do this for me so I'd end-up with 5.pdf files (instead of or as-well as) 5.fdf files.

Thanks
Mark