PDF to Excel conversion putting each pdf page in a different worksheet
49,161
Try this. You should be able to make a loop and extract each pages of your PDF in different worksheet.
Author by
Mike
Updated on November 19, 2020Comments
-
Mike over 3 years
I am attempting to convert a pdf file (with 16 pages) to an excel file in order to run a program I already have in place in excel. I have a code that converts the pdf to excel already, but I would like the macro to put each separate page of the pdf file in a different worksheet in my excel file( currently it copies all of the pages and pastes page 1 of the pdf into a worksheet).
All of the pages have the same heading if that helps at all. My current code is included below, thanks in advance.
Private Sub CommandButton1_Click() 'Declare Variable(s) Dim appAA As Acrobat.CAcroApp, docPDF As Acrobat.CAcroPDDoc Dim strFileName As String, intNOP As Integer, arrI As Variant Dim intC As Integer, intR As Integer, intBeg As Integer, intEnd As Integer 'Initialize Variables Set appAA = CreateObject("AcroExch.App"): Set docPDF = CreateObject("AcroExch.PDDoc") 'Set PDF FileName strFileName = "C:\Documents and Settings\Michael Palkovitz\My Documents\Test\EC Operations Budget February FY13.pdf" 'Read PDF File docPDF.Open (strFileName) 'Extract Number of Pages From PDF File intNOP = docPDF.GetNumPages 'Select First Data Cell Range("A1").Select 'Open PDF File ActiveWorkbook.FollowHyperlink strFileName, , True 'Loop Through All PDF File Pages For intC = 1 To intNOP 'Go To Page Number SendKeys ("+^n" & intC & "{ENTER}") '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 'Paste Data In This Workbook's Worksheet ActiveSheet.Paste 'Select Next Paste Cell Range("A" & Range("A1").SpecialCells(xlLastCell).Row + 2).Select 'Maximize Adobe Window SendKeys ("%x") Next intC 'Close Adobe File and Window SendKeys ("^w"), True 'Empty Object Variables Set appAA = Nothing: Set docPDF = Nothing 'Select First Cell Range("A1").Select end sub