Convert PDF to Excel Document

[excelblackbelt]

Hi all,

I am trying to convert a PDF to an Excel document. Taking a PDF with a title (with headings and bullet points) and placing it in Excel.

Have any of you guys been successful in doing this?

Thank you!

 

[Datsmart]

In order to export a PDF file into another application you will need the full Adobe Acrobat program installed on your computer. This is not the same software as the Adobe Acrobat Reader.

If all you have is the free "Reader" program, you can search the internet for converter programs to export from PDF to other applications.

 

[excelblackbelt]

I have Adobe Acrobat and it is still not working.

 

[kpark91]

It also depends on the quality of your pdf.

If it's a scanned document, it will most likely not work
but if it's an exported document from an application such as excel, it will have a better chance in converting it with limited accuracy.

Have you tried OCRing the document?

 

[Cindy Ellis]

I've done it through VBA...but very specific to my application. I opened it in Acrobat, copied a page at a time into Excel using copy/paste, then parsed it line by line, since I knew what I was looking for.

Sub PDF2Excel()
Dim AdobeApp As String
Dim AdobeFile As String
Dim StartAdobe
 
GetDir = Application.GetOpenFilename("Excel Files (*.pdf), *.pdf")
If GetDir <> "False" Then
    MyPath = CurDir & "\"
Else
    MsgBox "Directory not selected"
    Exit Sub
End If
 
NextFile = Dir(MyPath & "*.pdf")
While NextFile <> ""
    Workbooks.Add
    MyXLFile = ActiveWorkbook.Name
    AdobeApp = "C:\Program Files\Adobe\Acrobat 9.0\Acrobat\acrobat.exe"
    AdobeFile = NextFile
    MyFileSaveName = Left(NextFile, Len(NextFile) - 3) & "xlsx"
     
    StartAdobe = Shell("" & AdobeApp & " " & AdobeFile & "", 1)
    MyPause = DoEvents
    Sleep 1000
    SendKeys ("^a")
    SendKeys ("^c")
    MyPause = DoEvents
    Sleep 100
    SendKeys ("^q")
    Sleep 1000
    AppActivate "Microsoft Excel"
    DoEvents
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A1")
    ParseDataFile
    ActiveWorkbook.SaveAs FileName:=MyFileSaveName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
NextFile = Dir()
Wend
End Sub
 
 
Sub ParseDataFile()
Application.ScreenUpdating = False
LastDataRow = ActiveSheet.Range("A65535").End(xlUp).Row
MyRow = 1
Do Until MyRow > LastDataRow
    Data2Parse = ActiveSheet.Cells(MyRow, 1).Value
    If MyRow = 1 Then
        'take some actions for the first header row
    ElseIf MyRow = 2 Then
        'take some other actions for the next header row
    Else
        'handle the non-header rows
    End If
    
    MyRow = MyRow + 1
Loop
'ActiveSheet.Columns("A").ColumnWidth = 30 'format your columns here
'ActiveSheet.Columns("B").ColumnWidth = 20  'format your columns here
Application.ScreenUpdating = True
End Sub

There's still a lot of work for you to do to fill in the content of ParseDataFile.
Hope that helps,
Cindy

 

[excelblackbelt]

What is OCRing the document?

 

[Cindy Ellis]

If it's a scanned pdf, you can use Optical Character Recognition to convert the "shapes" of the letters into editable text.

 

[indiantrix]

Try File | Save As | Word document
then see if you get usable results that may transport
from a Word table into Excel.
HTH Larry.

 

[Ruddles]

I've had some success with PDFText (http://pdf-analyzer.com/).

But I've always ended up having to jiggle the worksheet around to make sure everything was there and it all lined up properly.