Extract data from .PDF

[shahzeb123]

You guys have helped me alot as always and i am in immense pleasure of the creator of this forum.

THANK YOU !

Right now, i have a query in my project would like to ask you PROs that if you can help me or not.

I need exchange rate from website, however, this website does not have table so i cannot get data from power query.

State Bank of Pakistan

Above mentioned is the website, where u can see there is hyperlink for date wise exchange rate. When u click on the day the files open with .pdf

I have attached the picture of that file as well.

What i want is to extract the table or the whole pdf file data into excel, becasue i need exchange rate daily from this website.

I know its a hard ask, if it is not possible so be it. Otherwise, please HELP!!

 

[GWteB]

The website you mention offers the possibility to download the PDF. Once the file has been downloaded, the contents can be copied to an Excel worksheet. My code uses MS Word for this. See if this is of any use to you.

Public Sub shahzeb123()

    Dim InitialFolder As String, FullName As Variant, ShtPdfData As Worksheet
    Dim wdApp As Object, wdPdfDoc As Object, wdRange As Object

    InitialFolder = Environ("userprofile") & "\Downloads\"
    ChDrive Left(InitialFolder, 1)
    ChDir InitialFolder
    FullName = Application.GetOpenFilename("PDF Files (*.pdf), *.pdf", 1)

    If Not VarType(FullName) = vbBoolean Then

        Set wdApp = CreateObject("Word.Application")
        wdApp.Visible = True

        Set wdPdfDoc = wdApp.Documents.Open(Filename:=FullName, ConfirmConversions:=False, ReadOnly:=False, Format:=0, NoEncodingDialog:=True)
        Set wdRange = wdPdfDoc.Range(1)
        wdRange.WholeStory
        wdRange.Copy

        Set ShtPdfData = ThisWorkbook.Worksheets.Add
        ShtPdfData.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
        Application.Goto ShtPdfData.Range("A1")

        wdPdfDoc.Close False
        wdApp.DisplayAlerts = False
        wdApp.Quit

    Else
        'cancel was pressed
    End If
End Sub

 

[shahzeb123]

IT WORKS WONDERS! )))

I really love your work, it is crazy to see that !!

Great Job Done Man.

However, i will now see what i can do with this as to if i need some of your help ill get back to you.

 

[GWteB]

You're welcome and thanks for the feedback.

 

[justlearning4]

You're welcome and thanks for the feedback.

Does this code work for PDF files that have hundreds of pages of data? If not, would you be able to build a code that extracts a table on page 50 of "forexample.pdf" and then transfers it to an excel file?

 

[justlearning4]

IT WORKS WONDERS! )))

I really love your work, it is crazy to see that !!

Great Job Done Man.

However, i will now see what i can do with this as to if i need some of your help ill get back to you.

What did the output look like in excel for you?

 

[GJGGJGGJG]

The website you mention offers the possibility to download the PDF. Once the file has been downloaded, the contents can be copied to an Excel worksheet. My code uses MS Word for this. See if this is of any use to you.

Public Sub shahzeb123()

    Dim InitialFolder As String, FullName As Variant, ShtPdfData As Worksheet
    Dim wdApp As Object, wdPdfDoc As Object, wdRange As Object

    InitialFolder = Environ("userprofile") & "\Downloads\"
    ChDrive Left(InitialFolder, 1)
    ChDir InitialFolder
    FullName = Application.GetOpenFilename("PDF Files (*.pdf), *.pdf", 1)

    If Not VarType(FullName) = vbBoolean Then

        Set wdApp = CreateObject("Word.Application")
        wdApp.Visible = True

        Set wdPdfDoc = wdApp.Documents.Open(Filename:=FullName, ConfirmConversions:=False, ReadOnly:=False, Format:=0, NoEncodingDialog:=True)
        Set wdRange = wdPdfDoc.Range(1)
        wdRange.WholeStory
        wdRange.Copy

        Set ShtPdfData = ThisWorkbook.Worksheets.Add
        ShtPdfData.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
        Application.Goto ShtPdfData.Range("A1")

        wdPdfDoc.Close False
        wdApp.DisplayAlerts = False
        wdApp.Quit

    Else
        'cancel was pressed
    End If
End Sub

Hi, this is great, it does almost exactly what I need, but I'm trying to make it actually paste into the Excel file, and everything (which is mostly ActiveSheet.Paste) I try gives an error. Any thoughts?