vba read from pdf

[ccordner]

I have an excel spreadsheet, which will be handling stores ordering. However most of the people that will be submitting orders won't have excel.

I can create a pdf form using Scribus, with textboxes etc, which produces a data form (*.fdf) which is emailed in.

Is it possible to read the data from that file using VBA?

Thanks
Chris

 

[Ruddles]

See http://wiki.scribus.net/canvas/How_to_create_an_e-Mail_PDF_Survey_Form.

This link http://www.box.net/shared/e5hdj945gu has an "Excel spreadsheet with a macro that allows you to extract data from FDF files. The macro is heavily commented to show what each line of code does."

 

[ccordner]

Thanks

There are three files.

First, the original scribus file I used to complete the form. I think the fields were given names such as txtName, txtColour, chkEasy, lstDays, btnSubmit.
http://www.yourfilelink.com/get.php?fid=693361

Second, the Scribus file is converted to the following PDF, which can be completed on any computer. Clicking the 'Send!' button taps into your email client (Outlook, Thunderbird etc.) and creates an email, with an '*.fdf' file attached.
http://www.yourfilelink.com/get.php?fid=693362

Finally, the '*.fdf' file which is emailed to the intended recipient. This is specified in Scribus, so if you do email any forms they'll go to my address.
Normally, if you try opening this file it will use the original PDF as a template and then 'fill it in' with the data from the FDF file, so you might need the PDF saved somewhere to be able to read it.
http://www.yourfilelink.com/get.php?fid=693359

Thanks for your help. If you can figure this out, it opens up a whole wealth of possibilities!

Thanks
Chris

 

[Ruddles]

Download the ZIP file and unzip the XLS. Place it in the same folder as your FDF file. You don't need the PDF file.

Open the XLS file and press Alt-F11 to enter the VBA editor. Locate the three lines which read Line Input #1, WholeLine and create an identical fourth line. (I think the original code is incorrect as it reads three lines and uses the third, whereas I think it should read away three lines, then read and use the fourth. It crashed when I ran it but with my change it seems to work.)

Clear the contents of the worksheet and run the code. When prompted, navigate to the FDF file and select it, then click Open. Finally go and look at the worksheet.

I got this:-

<TABLE style="WIDTH: 280pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=375><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=5 width=75><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 width=75>btnSubmit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=75>chkEasy</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=75>lstDays</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=75>txtColour</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=75>txtName</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Yes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>þÿTuesday</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Red</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Chris</TD></TR></TBODY></TABLE>

Is that what you were expecting?

 

[ccordner]

That's pretty much what I'm trying to achieve, but can't see where the zip file is?

Thanks
Chris

 

[Ruddles]

There was a link in my first post.

 

[ccordner]

Sorry, my mistake! Been a long day!

That's brilliant. I can edit that to work with any pdf file, so I'll have a play.

Thanks
Chris

 

[Ruddles]

I'm not sure what editing you're referring to. The only editing should be to repeat that line of code.

Did you intend to make some additional changes? (Actually I did tidy up the code a little and add a counter of the number of data items found.)

Let me know when you get it to work?