Solved: Extract Data From PDF Files to Excel

Printable View

10-02-2005, 02:09 PM
Carpiem

Solved: Extract Data From PDF Files to Excel

Hello To All,

I was wondering given all the amazing things people achieve with VBA, is it possible to extract data / strings from PDF files using VBA?

If the answer is yes.... would someone please give me some insight as to how this is done.

Thank you,

Carpiem
10-02-2005, 10:13 PM
BlueCactus

Welcome to VBAX, Carpiem.

The good news is that yes, it is possible. The bad news is that to do it in VBA would probably (to the best of my knowledge) require extensive understanding of PostScript. But if you search around on the web, you'll probably be able to find programs for converting PDF to Word, plain text, HTML, and various other formats. Some of these programs are free and maybe they'll give you a good starting point.

Now, if the formats of your PDF documents all follow a similar design, you *might* be able to use VBA to extract the info you need with minimum or even no knowledge of PostScript. With even minor differences between files, things are going to get complicated.
10-03-2005, 01:47 AM
Ivan F Moala

If You use XL2003, then yes and you can do this via automation and MDI
But the process would require taking an image snap shot of the PDF file.
10-03-2005, 03:05 AM
sheeeng

Quote:

Originally Posted by Ivan F Moala

If You use XL2003, then yes and you can do this via automation and MDI
But the process would require taking an image snap shot of the PDF file.

Can you explain it step by step if you have time? I'm looking forward to try it.
Thanks in advence.
10-05-2005, 01:47 PM
Carpiem

BlueCactus,

Thank you for the precis. I actually use an excellent program (Read Iris) to convert PDF to Excel. As with most things though there is cleaning up to do after the conversion.

It seems I might better ask a few questions to do with cleaning up the file rather than approaching the exercise from an admittedly intriguing and new process to me.

Carpiem

Quote:

Originally Posted by BlueCactus

Welcome to VBAX, Carpiem.

The good news is that yes, it is possible. The bad news is that to do it in VBA would probably (to the best of my knowledge) require extensive understanding of PostScript. But if you search around on the web, you'll probably be able to find programs for converting PDF to Word, plain text, HTML, and various other formats. Some of these programs are free and maybe they'll give you a good starting point.

Now, if the formats of your PDF documents all follow a similar design, you *might* be able to use VBA to extract the info you need with minimum or even no knowledge of PostScript. With even minor differences between files, things are going to get complicated.
10-05-2005, 01:59 PM
Carpiem

Mr. Moala,

Never thought I would have the pleasure of responding to you. Have learned a good deal from you in the past and daresay if I was up to it would learn much more.

Confess to knowing zero about Multiple Document Interface... the program I dowloaded needed at least minimal understanding of the process to be invoked.

I shall plod along and try to refine my needs in a more subdued manner.

Thank you,

Carpiem

Quote:

Originally Posted by Ivan F Moala

If You use XL2003, then yes and you can do this via automation and MDI
But the process would require taking an image snap shot of the PDF file.
10-05-2005, 02:12 PM
mdmackillop

Hi Carpiem,
Can you attach a sample of an imported spreadsheet, which might give an idea of the "clean up" required. You can attach a file by zipping it and using the Manage Attachments button found in the Go Advanced option.
Regards
MD
10-05-2005, 04:23 PM
Carpiem

Good Evening MD,

Have attached the much shortened file for your scrutiny. My explanation may not be to clear... have to wait and see.

I do really appreciate you taking time out to look at this.

Regards,

Carpiem

Hi Carpiem,
Can you attach a sample of an imported spreadsheet, which might give an idea of the "clean up" required. You can attach a file by zipping it and using the Manage Attachments button found in the Go Advanced option.
Regards
MD
10-05-2005, 07:04 PM
mdmackillop

Hi Carpiem,
I'm not too clear on obtaining the results you show in G38. Can you explain why this should be 120. If this is correct, the code can be modified as required. The split is done in the Sample columns by two simple UDF (user defined formulae) in Module 1, which can be manipulated to suit.
Regards
MD
10-06-2005, 11:09 AM
Carpiem

Hello MD,

The UDF's you created are just what "The Doctor Ordered" and much appreciated.

I can't explain what I was thinking when I inserted column G.... hence no idea what to say. In short order though I will be using your functions and adding to them.

On the subject of UDF's how does one incorporate them into vba. In this instance I am referring to the possibility of having the UDF's automatically do their job when the "Quoted Cost" (E38) was entered or pasted in?

I do not wish to waste your time and am only exploring the possibility of further automating the order entry process.

Regards,

Carpiem
10-06-2005, 01:07 PM
mdmackillop

Hi Carpiem,
There's no question of "wasting time", that is just what we do here.
One method as follows. Paste the code into the worksheet module, edit the intersect range and adjust the offset values etc. as appropriate. If you don't need the formulae, and only the result, then this could be extracted and pasted in as values. It all depends on your purposes.
Regards
MD

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo LastLine
If Not Intersect(Target, Range("E38:E45")) Is Nothing Then
Target.Offset(0, 3).Formula = "=UOM(E" & Target.Row() & ")"
Target.Offset(0, 4).Formula = "=UNIT(E" & Target.Row() & ")"
End If
LastLine:
End Sub

[/VBA]
10-06-2005, 02:25 PM
Carpiem

Hello MD,

Seems everyone is exceedingly helpful here, which is great given the alternative. So once again 'Thank you".

Looking at your code and given that the range could be anything from a few rows to hundreds, would it be sufficient for me to use a named range to cater for additions or deletions to the rows and still incorporate your code as is?

Regards,

Carpiem
10-06-2005, 02:52 PM
mdmackillop

Hi Carpiem,
A named range would work fine. The only reason for the range is to exclude cells where you would not wish the code to run. eg, titles, totals or whatever. You could also get the code to run only if the text contains the / character. It's all depends on your conversions.
Regards
MD
10-06-2005, 06:03 PM
brettdj

FWIW, I once did something similar from outlook whereby I sent the pdf to an adobe email address that converted the pdf to text, then I scanned for a reply email from this adress, then used a RegExp to extract the portion of data from the text.
10-07-2005, 07:31 AM
Carpiem

Hello MD,

Well that is clear enough. Thank you again.

Regards,

Carpiem

Quote:

Originally Posted by mdmackillop

Hi Carpiem,
A named range would work fine. The only reason for the range is to exclude cells where you would not wish the code to run. eg, titles, totals or whatever. You could also get the code to run only if the text contains the / character. It's all depends on your conversions.
Regards
MD
10-07-2005, 07:37 AM
Carpiem

Hello BrettDJ,

I understand what you are saying.... the problem is that I have never heard of an Adobe E-Mail address before and searching on the web has not revealed anything relevant.

Could you please elaborate on the procedure you used a little more as it sounds like something I should explore.

Regards,

Carpiem

Quote:

Originally Posted by brettdj

FWIW, I once did something similar from outlook whereby I sent the pdf to an adobe email address that converted the pdf to text, then I scanned for a reply email from this adress, then used a RegExp to extract the portion of data from the text.
10-07-2005, 10:20 AM
Bob Phillips

Quote:

Originally Posted by Carpiem

Hello BrettDJ,

I understand what you are saying.... the problem is that I have never heard of an Adobe E-Mail address before and searching on the web has not revealed anything relevant.

Could you please elaborate on the procedure you used a little more as it sounds like something I should explore.

Perhaps this is what Brett was referring to http://www.adobe.com/products/acroba...linetools.html
10-07-2005, 02:35 PM
Carpiem

Hola XLD,

Gracias por dirigirme a ese sitio.

?Eso es un Saxophone muy fresco tambi?n!

Respeto,

Carpiem
10-07-2005, 03:29 PM
Carpiem

Good Evening MD,

I have more or less got my head around the way you constructed the UDF's and the vba to automate them.

Would you be willing to walk me through the next stage i.e. "You could also get the code to run only if the text contains the / character." .... ?

Regards,

Carpiem

Quote:

Originally Posted by mdmackillop

Hi Carpiem,
A named range would work fine. The only reason for the range is to exclude cells where you would not wish the code to run. eg, titles, totals or whatever. You could also get the code to run only if the text contains the / character. It's all depends on your conversions.
Regards
MD
10-07-2005, 03:43 PM
mdmackillop

Good evening Carpiem,
A simple modification of the WorkSheet sub can test for / in a string, if is found, the split takes place, if not, you can exit the sub, or as in this example, clear the UOM and Unit cells.
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo LastLine
If Not Intersect(Target, Range("E38:E45")) Is Nothing Then
If InStr(1, Target, "/") = 0 Then
Target.Offset(0, 3).ClearContents
Target.Offset(0, 4).ClearContents
Else
Target.Offset(0, 3).Formula = "=UOM(E" & Target.Row() & ")"
Target.Offset(0, 4).Formula = "=UNIT(E" & Target.Row() & ")"
End If
End If
LastLine:
End Sub
[/VBA]
Regards
MD
10-07-2005, 05:36 PM
mdmackillop

A further thought.
If you are running other code which changes a sheet containing a WorkSheet Change type macro, it is best to add "Application.EnableEvents = False" at the head of the code, resetting it to True at the end (very important - it's not reset by default). This prevents the WorkSheet code from running each time the sheeet is changed by your routines.
10-08-2005, 11:04 PM
brettdj

Quote:

Originally Posted by xld

Perhaps this is what Brett was referring to http://www.adobe.com/products/acroba...linetools.html

Thanks Bob
10-10-2005, 11:09 AM
Carpiem

Good Afternoon MD,

Thank you,

Carpiem

Quote:

Originally Posted by mdmackillop

A further thought.
If you are running other code which changes a sheet containing a WorkSheet Change type macro, it is best to add "Application.EnableEvents = False" at the head of the code, resetting it to True at the end (very important - it's not reset by default). This prevents the WorkSheet code from running each time the sheeet is changed by your routines.

All times are GMT -7. The time now is 09:29 PM.