How to open & select PDF data and paste it in excel file using VBA code ?

[Sandeep Babu S]

How to open & select PDF data and paste it in excel file using VBA code ?

 

[mpalk]

this will copy and paste the selected page of your pdf file into the first cell in the open worksheet, then close the pdf after the copy and paste.. There are also two sections of code that ensure the button remains in the same location and the same size.

Private Sub CommandButton1_Click()
   
    'In order to use the macro you must enable the Acrobat library from VBA editor:
    'Go to Tools -> References -> Adobe Acrobat xx.0 Type Library, where xx depends
    'on your Acrobat Professional version (i.e. 9.0 or 10.0) you have installed to your PC.
    
    Dim PDFApp As AcroApp
    Dim PDFDoc As AcroAVDoc
    Dim PDFPageView As AcroAVPageView
    Dim PDFPath As String
    Dim DisplayPage As Integer
    
   'regulate button location
        Set btn = ActiveSheet.Shapes("CommandButton1")
With btn
 btLeft = .Left
    btTop = .Top
    End With
    
    'find the PDF file in your documents
        PDFPath = "your pdf path.pdf"

    
    'Set the page you want to be displayed
    DisplayPage = 1
    
    'Initialize Acrobat by creating App object
    Set PDFApp = CreateObject("AcroExch.App")
    
    'Set AVDoc object
    Set PDFDoc = CreateObject("AcroExch.AVDoc")
    
    'Open the PDF
    If PDFDoc.Open(PDFPath, "") = True Then
        PDFDoc.BringToFront
        
        'Maximize the document
        Call PDFDoc.Maximize(True)
        
        Set PDFPageView = PDFDoc.GetAVPageView()
        
        'Go to the desired page
        'The first page is 0
        Call PDFPageView.GoTo(DisplayPage - 1)
        
        'Select All Data In The PDF File's Active Page
SendKeys ("^a"), True

'Right-Click Mouse
SendKeys ("+{F10}"), True

'Copy Data As Table
SendKeys ("c"), True

'Minimize Adobe Window
SendKeys ("%n"), True

'Select First Cell
Range("A1").Select

'Paste Data In This Workbook's Worksheet
ActiveSheet.Paste
        
        '-------------
        'ZOOM options
        '-------------
        '0 = AVZoomNoVary
        '1 = AVZoomFitPage
        '2 = AVZoomFitWidth
        '3 = AVZoomFitHeight
        '4 = AVZoomFitVisibleWidth
        '5 = AVZoomPreferred
        
        'Set the page view of the pdf
        Call PDFPageView.ZoomTo(2, 50)
        
    
    End If

    Set PDFApp = Nothing
    Set PDFDoc = Nothing
    
    'Maximize Adobe Window
SendKeys ("%x")


'Close Adobe File and Window
SendKeys ("^w"), True
    
'Select First Cell
Range("A1").Select
    
'regulate button size
CommandButton1.Height = 35
CommandButton1.Width = 125

    On Error Resume Next
   
        
End Sub