Difference between revisions of "How to create an e-Mail PDF Survey Form"

From Scribus Wiki
Jump to: navigation, search
Line 74: Line 74:
  
 
Public Sub DoAdobeImport()
 
Public Sub DoAdobeImport()
 +
 
Dim FName As Variant
 
Dim FName As Variant
 +
 
Dim Sep1 As String
 
Dim Sep1 As String
 +
 
Dim Sep2 As String
 
Dim Sep2 As String
 +
 
Dim Sep3 As String
 
Dim Sep3 As String
 +
 
Dim Sep4 As String
 
Dim Sep4 As String
 +
 
Dim RowNdx As Integer
 
Dim RowNdx As Integer
 +
 
Dim ColNdx As Integer
 
Dim ColNdx As Integer
 +
 
Dim WholeLine As String
 
Dim WholeLine As String
 +
 
Dim Pos As Integer
 
Dim Pos As Integer
 +
 
Dim NextPos As Integer
 
Dim NextPos As Integer
 +
 
Dim StartPos As Integer
 
Dim StartPos As Integer
 +
 
Dim EndPos As Integer
 
Dim EndPos As Integer
 +
 
Dim recordPos As Integer
 
Dim recordPos As Integer
 +
 
Dim recordPos2 As Integer
 
Dim recordPos2 As Integer
 +
 
Dim SaveColNdx As Integer
 
Dim SaveColNdx As Integer
 +
 
Dim Part1 As String
 
Dim Part1 As String
 +
 
Dim Part2 As String
 
Dim Part2 As String
 +
 
'get name of FDF file
 
'get name of FDF file
 +
 
FName = Application.GetOpenFilename _
 
FName = Application.GetOpenFilename _
 
     (filefilter:="Adobe FDF Data Files(*.fdf),*.fdf,All Files (*.*),*.*", Title:="Select FDF file to import")
 
     (filefilter:="Adobe FDF Data Files(*.fdf),*.fdf,All Files (*.*),*.*", Title:="Select FDF file to import")
 
      
 
      
 
If FName = False Then
 
If FName = False Then
 +
 
     MsgBox "You didn't select a file"
 
     MsgBox "You didn't select a file"
 +
 
     Exit Sub
 
     Exit Sub
 +
 
End If
 
End If
 +
 
'Set record separators
 
'Set record separators
 +
 
Application.ScreenUpdating = False
 
Application.ScreenUpdating = False
 +
 
Sep1 = "<<"
 
Sep1 = "<<"
 +
 
Sep2 = ">>"
 
Sep2 = ">>"
 +
 
Sep3 = "/T"
 
Sep3 = "/T"
 +
 
Sep4 = ")"
 
Sep4 = ")"
 +
 
'set cell row and column where to start entering data
 
'set cell row and column where to start entering data
 +
 
ColNdx = ActiveCell.Column
 
ColNdx = ActiveCell.Column
 +
 
RowNdx = ActiveCell.Row
 
RowNdx = ActiveCell.Row
 +
 
Open FName For Input Access Read As #1 'open fdf file
 
Open FName For Input Access Read As #1 'open fdf file
  
  
 
     Line Input #1, WholeLine 'Skip first three lines as they do not contain any data
 
     Line Input #1, WholeLine 'Skip first three lines as they do not contain any data
 +
 
     Line Input #1, WholeLine
 
     Line Input #1, WholeLine
 +
 
     Line Input #1, WholeLine
 
     Line Input #1, WholeLine
 +
 
     StartPos = (InStr(1, WholeLine, "[")) + 1 ' find where data starts
 
     StartPos = (InStr(1, WholeLine, "[")) + 1 ' find where data starts
 +
 
     EndPos = (InStr(1, WholeLine, "]")) - 1 ' find where data ends
 
     EndPos = (InStr(1, WholeLine, "]")) - 1 ' find where data ends
 +
 
     WholeLine = Mid(WholeLine, StartPos, EndPos) 'capture just the data fields
 
     WholeLine = Mid(WholeLine, StartPos, EndPos) 'capture just the data fields
 +
 
     Pos = 3 ' set start position
 
     Pos = 3 ' set start position
 +
 
      
 
      
 
     NextPos = InStr(Pos, WholeLine, Sep2) 'find end of current record
 
     NextPos = InStr(Pos, WholeLine, Sep2) 'find end of current record
 +
 
     While NextPos >= 1
 
     While NextPos >= 1
 +
 
         TempVal = Mid(WholeLine, Pos, NextPos - Pos) 'Find start of next record
 
         TempVal = Mid(WholeLine, Pos, NextPos - Pos) 'Find start of next record
 +
 
         recordPos = (InStr(1, TempVal, Sep4)) 'Go to end of record by using ")"
 
         recordPos = (InStr(1, TempVal, Sep4)) 'Go to end of record by using ")"
 +
 
         'Assume the value is in A1, in B1  =Left(A1,len(A1)-2)
 
         'Assume the value is in A1, in B1  =Left(A1,len(A1)-2)
 +
 
          
 
          
 +
 
         Part1 = Trim(Mid(TempVal, 1, recordPos)) 'get data record name
 
         Part1 = Trim(Mid(TempVal, 1, recordPos)) 'get data record name
 
          
 
          
 
         '*******Comment out the line below if you do not want data record name*****
 
         '*******Comment out the line below if you do not want data record name*****
         Cells(RowNdx, ColNdx).Value = Right((Left(Part1, Len(Part1) - 1)), Len((Left(Part1, Len(Part1) - 1))) - 3) ' trim off start and end superfluous characters and enter in cell
+
 
 +
         Cells(RowNdx, ColNdx).Value = Right((Left(Part1, Len(Part1) - 1)), Len((Left(Part1, Len(Part1) - 1))) - 3) ' trim off  
 +
 
 +
start and end superfluous characters and enter in cell
 
          
 
          
 
         '*******Comment out the line below if you do not want data record name*****
 
         '*******Comment out the line below if you do not want data record name*****
 +
 
         RowNdx = RowNdx + 1 'move to next row
 
         RowNdx = RowNdx + 1 'move to next row
 
          
 
          
Line 136: Line 185:
 
          
 
          
 
         Part2 = Trim(Mid(TempVal, recordPos2)) 'get data
 
         Part2 = Trim(Mid(TempVal, recordPos2)) 'get data
 +
 
         'Check to see if data field is blank
 
         'Check to see if data field is blank
 +
 
         If Part2 = Sep4 Then
 
         If Part2 = Sep4 Then
 +
 
         Cells(RowNdx, ColNdx).Value = ""
 
         Cells(RowNdx, ColNdx).Value = ""
 +
 
         'Check if Data is Yes or No
 
         'Check if Data is Yes or No
 +
 
         ElseIf Right(Part2, 1) <> Sep4 Then 'trim off start and end superfluous characters and enter in cell
 
         ElseIf Right(Part2, 1) <> Sep4 Then 'trim off start and end superfluous characters and enter in cell
 +
 
         Cells(RowNdx, ColNdx).Value = Right((Left(Part2, Len(Part2) - 0)), Len((Left(Part2, Len(Part2) - 0))) - 4) ' trim off start and end superfluous characters and enter in cell
 
         Cells(RowNdx, ColNdx).Value = Right((Left(Part2, Len(Part2) - 0)), Len((Left(Part2, Len(Part2) - 0))) - 4) ' trim off start and end superfluous characters and enter in cell
 +
 
         Else
 
         Else
 +
 
         Cells(RowNdx, ColNdx).Value = Right((Left(Part2, Len(Part2) - 1)), Len((Left(Part2, Len(Part2) - 1))) - 4) ' trim off start and end superfluous characters and enter in cell
 
         Cells(RowNdx, ColNdx).Value = Right((Left(Part2, Len(Part2) - 1)), Len((Left(Part2, Len(Part2) - 1))) - 4) ' trim off start and end superfluous characters and enter in cell
 +
 
         End If
 
         End If
 
          
 
          
 
         'Cells(RowNdx, ColNdx).Value = Trim(Mid(TempVal, recordPos2))  'Second part which contains data
 
         'Cells(RowNdx, ColNdx).Value = Trim(Mid(TempVal, recordPos2))  'Second part which contains data
 +
 
         ColNdx = ColNdx + 1 ' move to next column
 
         ColNdx = ColNdx + 1 ' move to next column
 
          
 
          
 
         '*******Comment out the line below if you do not want data record name*****
 
         '*******Comment out the line below if you do not want data record name*****
 +
 
         RowNdx = RowNdx - 1 ' move up a row
 
         RowNdx = RowNdx - 1 ' move up a row
 +
 
          
 
          
 
         Pos = NextPos + 4 ' move to start of next record record
 
         Pos = NextPos + 4 ' move to start of next record record
 +
 
         NextPos = InStr(Pos, WholeLine, Sep2) ' find end of next record
 
         NextPos = InStr(Pos, WholeLine, Sep2) ' find end of next record
 +
 
         'if more records loop again
 
         'if more records loop again
 +
 
     Wend
 
     Wend
 +
 
  'if no more records end
 
  'if no more records end
 +
 
Close #1
 
Close #1
 +
 
End Sub
 
End Sub
  

Revision as of 03:00, 31 May 2007

Introduction

How often have you ever been emailed a PDF form that you have been told to print it, fill it in with a pen and then fax or post it back to the sender?

….and you thought to yourself, “Why don’t they just send me a PDF form that I can quickly type in the information and email it back.”

With Scribus you can create electronic PDF forms that can do exactly this. In the Scribus Wiki article “Your first PDF form with Scribus” you are shown how to create a sample PDF form and if you tried it, you will have an idea of what is involved.

The demonstration form however uses an online server which is complicated and expensive to setup.

A far simpler way is to use e-mails to exchange the data and the form can be easily modified to change the way the data is transferred. The setup of the Submit Button is all that needs to be changed. Instead of the data being sent as HTML to a web address, the mailto:” command is used. By using the mailto:” command, Adobe Acrobat Reader automatically attaches data in a FDF file to an email. This command works with most mail clients used in Windows and Linux.

FDF is short for Forms Data Format and they are basically text files that contain data from the PDF form. Not quite HTML, but a similar cousin. The form's field names and data are stored within a tag set. Characters between tags are irrelevant and don't appear anywhere within the displayed PDF file.

Adobe Acrobat Reader can open a FDF file but requires the original PDF form to do so. If you double click on a FDF file, Reader requests the location of the PDF form. Once linked up with the PDF form location, the PDF form opens with the FDF data in the fields. FDF files are a fraction of the size of the complete PDF form.


Creating a e-Mail PDF form

Scribussurvey.JPG


In the document “Your first PDF form with Scribus” you are shown how to create a PDF form to submit data to a server. A copy of the example file can be downloaded from a link on that page.

You can easily modify the example form to e-mail the data. Open the Scribus version of the document with Scribus. Right click your mouse on the “Submit” Button (Blue Rectangle). A menu pops up and select PDF Options –> Field Properties

Next select the Action Tab –> choose Submit Form and enter “mailto:” with your email address. Do not tick the Submit data as HTML.


Submit.JPG

Click OK. Now save your document as a PDF and view it with Adobe Acrobat Reader.

Testing your PDF form

Enter some data on the form and click the SUBMIT Button. By answering the prompts and security questions correctly, your email client should create an email with a FDF attachment to the address you specified. Something like the image below.

Email.JPG


Notice the difference in the file size between the FDF and PDF.


How to use FDF files

There are two options that I can think of -

1. Dump the data back into the form and print it, or

2. Extract the data into a spreadsheet or database


While Adobe would prefer you to purchase the full version of Acrobat, you can still do a lot with the free Scribus and Acrobat Reader software.

As the data in the FDF file is text, it is easily accessible by using a script or macro to extract the data contained in it. If you Save a FDF file from a e-mail (this is the only way to get one out of Reader) and open it in a text editor, you will the field names and your data with a whole lot of other characters.

If you use the example, you will see amongst other words and characters, the following words

Country Komentarz Miasto Name No Street Submit Telefon Zip

You will notice that this order is different to the PDF form. The fields are in alphabetical order. If you want your fields in the same order as on the PDF form, then you will need to name the fields in alphabetical order.

For example-

AName BStreet CNo DZip EMiasto FCountry GTelefon HKomentarz Submit


An example of a script to process FDF files into MS Excel

Below is a macro that allows you to extract data from FDF files. The macro is heavily commented to show what each line of code does. This script should be able to be easily converted to other applications.


Excel Macro

Public Sub DoAdobeImport()

Dim FName As Variant

Dim Sep1 As String

Dim Sep2 As String

Dim Sep3 As String

Dim Sep4 As String

Dim RowNdx As Integer

Dim ColNdx As Integer

Dim WholeLine As String

Dim Pos As Integer

Dim NextPos As Integer

Dim StartPos As Integer

Dim EndPos As Integer

Dim recordPos As Integer

Dim recordPos2 As Integer

Dim SaveColNdx As Integer

Dim Part1 As String

Dim Part2 As String

'get name of FDF file

FName = Application.GetOpenFilename _

   (filefilter:="Adobe FDF Data Files(*.fdf),*.fdf,All Files (*.*),*.*", Title:="Select FDF file to import")
   

If FName = False Then

   MsgBox "You didn't select a file"
   Exit Sub

End If

'Set record separators

Application.ScreenUpdating = False

Sep1 = "<<"

Sep2 = ">>"

Sep3 = "/T"

Sep4 = ")"

'set cell row and column where to start entering data

ColNdx = ActiveCell.Column

RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1 'open fdf file


   Line Input #1, WholeLine 'Skip first three lines as they do not contain any data
   Line Input #1, WholeLine
   Line Input #1, WholeLine
   StartPos = (InStr(1, WholeLine, "[")) + 1 ' find where data starts
   EndPos = (InStr(1, WholeLine, "]")) - 1 ' find where data ends
   WholeLine = Mid(WholeLine, StartPos, EndPos) 'capture just the data fields
   Pos = 3 ' set start position


   NextPos = InStr(Pos, WholeLine, Sep2) 'find end of current record
   While NextPos >= 1
       TempVal = Mid(WholeLine, Pos, NextPos - Pos) 'Find start of next record
       recordPos = (InStr(1, TempVal, Sep4)) 'Go to end of record by using ")"
       'Assume the value is in A1, in B1  =Left(A1,len(A1)-2)


       Part1 = Trim(Mid(TempVal, 1, recordPos)) 'get data record name
       
       '*******Comment out the line below if you do not want data record name*****
       Cells(RowNdx, ColNdx).Value = Right((Left(Part1, Len(Part1) - 1)), Len((Left(Part1, Len(Part1) - 1))) - 3) ' trim off 

start and end superfluous characters and enter in cell

       '*******Comment out the line below if you do not want data record name*****
       RowNdx = RowNdx + 1 'move to next row
       
       recordPos2 = (InStr(1, TempVal, Sep4)) ' find ")" which is end of record
       
       Part2 = Trim(Mid(TempVal, recordPos2)) 'get data
       'Check to see if data field is blank
       If Part2 = Sep4 Then
       Cells(RowNdx, ColNdx).Value = ""
       'Check if Data is Yes or No
       ElseIf Right(Part2, 1) <> Sep4 Then 'trim off start and end superfluous characters and enter in cell
       Cells(RowNdx, ColNdx).Value = Right((Left(Part2, Len(Part2) - 0)), Len((Left(Part2, Len(Part2) - 0))) - 4) ' trim off start and end superfluous characters and enter in cell
       Else
       Cells(RowNdx, ColNdx).Value = Right((Left(Part2, Len(Part2) - 1)), Len((Left(Part2, Len(Part2) - 1))) - 4) ' trim off start and end superfluous characters and enter in cell
       End If
       
       'Cells(RowNdx, ColNdx).Value = Trim(Mid(TempVal, recordPos2))   'Second part which contains data
       ColNdx = ColNdx + 1 ' move to next column
       
       '*******Comment out the line below if you do not want data record name*****
       RowNdx = RowNdx - 1 ' move up a row


       Pos = NextPos + 4 ' move to start of next record record
       NextPos = InStr(Pos, WholeLine, Sep2) ' find end of next record
       'if more records loop again
   Wend
'if no more records end

Close #1

End Sub


Possible Uses for this System

Club membership applications / database Client information database Electronic Survey forms Electronic examinations