Solved: PDF to Excel Data extract automatically

[DanOfEarth]

I'm trying to tie a PDF form I email to people and receive...to a spreadsheet for Data input. I save the completed form in the same file as the spreadsheet.

What's the best method...if any?

I'm swimming through the XML methods, but I can't grasp the XML Schema concept and how that works.

I've tried exporting directly from Acrobat, which I own, and Importing within Excel. Each way doesn't seem automated.

I'd even be OK with copying and pasting. Should I just buy "Able2Extract"?

Edit: Might I add. When it emails me the filled out form, it sends it back as an XML. What a headache.

[DanOfEarth]

uhh....tap...tap...tap....is this thing on?

[Aussiebear]

uhh....tap...tap...tap....is this thing on?

If you can see the print then its on.......

[DanOfEarth]

I solved it.

[Aussiebear]

Excellant.

[Blade Hunter]

Any chance of a code snippet? I am about to embark on a similar project.

Cheers

Dan

[DanOfEarth]

Well,

This is gonna be really strange and really "rigged". I only needed one form at a time, where I then erased the form.

It was a weird combination of things.....more of a workflow issue.

First, I created the PDFform in Acrobat with the 20 fillable text entries and added a "submit button" with my email to be received. When they email it back, it gets emailed as an XML file...you don't have a choice.

In the workbook I wanted the data, I had to create an "input" worksheet (I hid it though) that EXACLY MATCHED the PDF file. There couldn't be empty rows or anything.

To map the fields together automatically, I then emailed myself the file with the entries full, then opened my workbook, and hit Data>From Other Sources>From XML Data Import and chose the XML file.

When it asked where I wanted the data, I highlighted the box above the first entry, unchecked "header", and hit O.K (also first hit the option button and uncheck "readjust columns"). It then maps the whole file for you and creates an instance of that you can find displayed in the Data>Connections pop-up box.

I created a button called "Import" and attached the following code:

[vba]Sub Import()
'
Range("C4").Select
ActiveWorkbook.XmlMaps("topmostSubform_Map").DataBinding.Refresh
End Sub[/vba]

Two things however. It has to be the first thing you've ever mapped to use "topmostSubform_Map". To check, Hit Data>Connections and check to see if that "connection" is the top in the list.

Also notice that I coded: Range("C4".Select) first. The reason...to use that snippet, you half to have the cursor selected on one of the target cells....doesn't matter which. Otherwise it'll give you a weird error.

Strangely, you don't have to have the "Import" button on the same worksheet...it can be on any. Being that the code doesn't reference a particular worksheet...only a cell, it's interesting.

Lastly, you always have to save the file in the same sapot, otherwise you'll have to remap it.

Now you can have the Adobe compile the info of multiple forms onto a PDF data file, but I didn't need to do it. THAT you'll have to read in the adobe instructions...and they suck explaining it.

I hope that gets you started.....

[Blade Hunter]

Hmmmm, thanks but it seems my project is more different than I first thought.

I have a PDF which is locked for everything except printing. The PDF has an example XML in it. Being that I can't copy and paste I am screwed and I need to reverse engineer this. The company that supplied the doc say they don't have the example in any other format and they dont have an xsd we can generate from.

I tried screen dumping and using an old version of excel to get to the MODI command but screen dumping didn't give a clean enough tiff file to OCR from .

I am not retyping a 3 page sample xml file .

[DanOfEarth]

If they lock it, your screwed no matter what the circumstance. Just like a passworded doc or excel file.

Sure, you can buy a cracker to crack it but that's not what they're looking for.

[Blade Hunter]

Oh well, guess we have to push back and get them to send us samples somehow.

Thanks anyway

Dan

[DanOfEarth]

If you've got the XML, your fine. That's the doc that you use to map the fields.

Did they supply file you with an XML generated off of the PDF Form?

[Blade Hunter]

No, what I mean is they sent me a PDF locked against everything except printing.

The PDF is a white paper with a sample XML in for some content I need to deliver. Without having that XML available to me as an XML I can't reverse it in Oxygen.

[DanOfEarth]

Hmmmm.

Sounds like they need to step up to the plate a bit.

Sorry this didn't help. Too bad there's no "code" out there that "forces people to cooperate."

[Blade Hunter]

Hmmmm.

Sounds like they need to step up to the plate a bit.

Sorry this didn't help. Too bad there's no "code" out there that "forces people to cooperate."

God yes, that would be the most awesome piece of code ever