Import PDF into Excel with VBA
lucieaup
Posted messages
18
Status
Member
-
cs_Le Pivert Posted messages 8437 Status Contributor -
cs_Le Pivert Posted messages 8437 Status Contributor -
Hello,
I am looking to create a mini tool to import PDFs into an Excel file. Today I receive hundreds of PDFs weekly from officials all over Europe, and I have to load them manually one by one into our ERP (SAP). I have a way to enter them in bulk, but it needs to come from an Excel file.
I cannot use an online converter because we are not administrators of our machines and I deal with 12 countries in Europe, all of which do not have the same access, which is why I want to code it myself in VBA.
I found this code while browsing the web, but it does not work on my file..
'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.
'Alternatively you can find it Tools -> References -> Browse and check for the path
'C:\Program Files\Adobe\Acrobat xx.0\Acrobat\acrobat.tlb
'where xx is your Acrobat version (i.e. 9.0 or 10.0 etc.).
Dim PDFApp As AcroApp
Dim PDFDoc As AcroAVDoc
Dim PDFPageView As AcroAvPageView
Dim PDFPath As String
Dim DisplayPage As Integer
'Change this to your own complete PDF path
'Full path example
'PDFPath = "C:\Program Files\Autodesk\ACADM 2010\Setup\en-US\SetupRes\Docs\Acad_Mech_2010_UserGuide.pdf"
'For Word
'PDFPath = ThisDocument.Path & "\" & "PDF Sample.pdf"
'For Power Point
'PDFPath = ActivePresentation.Path & "\" & "PDF Sample.pdf"
'For Excel
PDFPath = ThisWorkbook.Path & "\" & "PDF Sample.pdf"
'Set the page you want to be displayed
DisplayPage = 3
'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)
'-------------
'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
On Error Resume Next
'Show the adobe application
PDFApp.Show
'Set the focus to adobe acrobat pro
AppActivate "Adobe Acrobat Pro"
End Sub
Can someone help me?
Thank you
Configuration: Windows / Internet Explorer 11.0
I am looking to create a mini tool to import PDFs into an Excel file. Today I receive hundreds of PDFs weekly from officials all over Europe, and I have to load them manually one by one into our ERP (SAP). I have a way to enter them in bulk, but it needs to come from an Excel file.
I cannot use an online converter because we are not administrators of our machines and I deal with 12 countries in Europe, all of which do not have the same access, which is why I want to code it myself in VBA.
I found this code while browsing the web, but it does not work on my file..
'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.
'Alternatively you can find it Tools -> References -> Browse and check for the path
'C:\Program Files\Adobe\Acrobat xx.0\Acrobat\acrobat.tlb
'where xx is your Acrobat version (i.e. 9.0 or 10.0 etc.).
Dim PDFApp As AcroApp
Dim PDFDoc As AcroAVDoc
Dim PDFPageView As AcroAvPageView
Dim PDFPath As String
Dim DisplayPage As Integer
'Change this to your own complete PDF path
'Full path example
'PDFPath = "C:\Program Files\Autodesk\ACADM 2010\Setup\en-US\SetupRes\Docs\Acad_Mech_2010_UserGuide.pdf"
'For Word
'PDFPath = ThisDocument.Path & "\" & "PDF Sample.pdf"
'For Power Point
'PDFPath = ActivePresentation.Path & "\" & "PDF Sample.pdf"
'For Excel
PDFPath = ThisWorkbook.Path & "\" & "PDF Sample.pdf"
'Set the page you want to be displayed
DisplayPage = 3
'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)
'-------------
'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
On Error Resume Next
'Show the adobe application
PDFApp.Show
'Set the focus to adobe acrobat pro
AppActivate "Adobe Acrobat Pro"
End Sub
Can someone help me?
Thank you
Configuration: Windows / Internet Explorer 11.0
2 answers
-
Hello,
see this:
https://groups.google.com/forum/?fromgroups#!topic/excelvbamacros/hPZsy2QyFGM
To make it work, you need to put this part in a separate module and then follow the instructions to change the path of the exe:
' change the path of adobe program as per your desktop
task = Shell("C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe "
Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) _ As Long Public Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess _ As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long Public Declare Function TerminateProcess Lib "kernel32" (ByVal hProcess As _ Long, ByVal uExitCode As Long) As Long Public Const SYNCHRONIZE = &H100000 Public Const STANDARD_RIGHTS_REQUIRED = &HF0000
-
Hello,
“but it doesn't work on my file.” Do you have Adobe Acrobat Pro???
--
Best regards
Patrice
