Consulting

Results 1 to 3 of 3

Thread: Solved: Open pdf file into Excel

  1. #1
    VBAX Regular Felix Atagong's Avatar
    Joined
    Jun 2004
    Location
    Louvain, Belgium
    Posts
    29
    Location

    Solved: Open pdf file into Excel

    I found a quick and dirty way to parse (simple) text-based Adobe Acrobat pdf files into Excel using a freeware pdf2txt utility. It can be useful when you always have the same pdf file or report that needs to be parsed.

    What I do is saving the pdf file into My Documents and then run a bat file.

    [vba]
    Sub OpenPDF()
    '-------------------------------------------'
    ' BAT FILE: pdftotext.exe -layout YourPage.pdf
    ' DOWNLOAD LINK: http://www.foolabs.com/xpdf/download.html
    '-------------------------------------------'

    ' these lines look for a pdf file in your My Documents folder
    Set WshShell = CreateObject("WScript.Shell")
    ChDir (WshShell.SpecialFolders("MyDocuments"))
    PageName = Application.GetOpenFilename("YourPage, *.pdf", , "YourPage")

    ' if no file is picked the macro ends
    If PageName = "False" Then
    Exit Sub
    End If

    ' copies and renames the pdf file to the pdf2txt folder
    FileCopy PageName, "C:\pdf2txt\YourPage.pdf"
    ChDir ("C:\pdf2txt")

    ' THE BATFILE CONTAINS ONLY 1 LINE:
    ' pdftotext.exe -layout YourPage.pdf

    TestValue = Shell("YourPage.bat", 1)

    ' because the bat file runs for 1 or 2 seconds (in my case)
    ' I let the Excel macro wait 5 seconds before doing anything else
    ' there are more ingenious ways for VBA to wait for the end of an
    ' application, but this suits me fine...

    Application.Wait (Now + TimeValue("0:00:05"))

    ChDir "C:\pdf2txt"
    PageName = "C:\pdf2txt\YourPage.txt"

    ' the following reads the text that has been generated
    Call ReadTextFile

    ' do your text parsing - text to columns etc... etc... hereafter

    End Sub

    Sub ReadTextFile()
    Dim FileNum As Integer
    Dim r As Integer
    Dim wb As Workbook
    Dim Data As String
    r = 1
    FileNum = FreeFile
    Set wb = Workbooks.Add
    Open BladNaam For Input As #FileNum
    Do While Not EOF(FileNum)
    Line Input #FileNum, Data
    ActiveSheet.Cells(r, 1) = Data
    r = r + 1
    Loop
    Close #FileNum
    End Sub
    [/vba]

    There are more beautiful ways to make this work, but like I said I needed a quick and dirty solution. Hope this might help somebody.

    Felix
    Last edited by Felix Atagong; 10-15-2007 at 04:41 AM.
    Felix Atagong
    Unfinished Projects

  2. #2
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    Hi Felix
    Good to see another MrExcel face over here
    Perhaps you would consider creating a knowledge base article on this subject?
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  3. #3
    VBAX Regular Felix Atagong's Avatar
    Joined
    Jun 2004
    Location
    Louvain, Belgium
    Posts
    29
    Location
    OK, will do!
    Felix Atagong
    Unfinished Projects

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •