Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: How to copy data from pdf to excel using VBA for multiple sheets?

  1. #1
    Board Regular
    Join Date
    May 2013
    Posts
    65

    Default How to copy data from pdf to excel using VBA for multiple sheets?

    Hi,

    I have 200 sheets of data in PDF file which needs to copy to 200 sheets in excel workbook

    I need to use only acrobat reader 5.

    I have below macro which is copying only one and pasting to sheet1

    Please modify the below macro

    Sub StartAdobe()

    Dim AdobeApp As String
    Dim AdobeFile As String
    Dim StartAdobe

    AdobeApp = "C:\Program Files (x86)\Adobe\Acrobat 5.0\Reader\AcroRd32.exe"
    AdobeFile = "C:\Users\Dell\Desktop\Dead Men Tell No Tales.pdf"

    StartAdobe = Shell("" & AdobeApp & " " & AdobeFile & "", 1)

    Application.OnTime Now + TimeValue("00:00:05"), "FirstStep"

    End Sub


    Private Sub FirstStep()


    SendKeys ("^a")
    SendKeys ("^c")

    Application.OnTime Now + TimeValue("00:00:10"), "SecondStep"

    End Sub

    Private Sub SecondStep()

    AppActivate "Microsoft Excel"

    Range("A1").Activate
    SendKeys ("^v")


    End Sub

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,235

    Default Re: How to copy data from pdf to excel using VBA for multiple sheets?

    Are these 200 pages in one pdf, or 200 pdf files? I am assuming the latter although you write that it is only one file.

    You need to loop through each of the files in a sub that then calls the startAdobe sub. But somewhere you will also want to close the pdf files.

    Let's assume that all the pdf files are in one folder. The following will do the trick. I have used some of your code, but not the paste part, as we can use Excel's paste for that.


    Read throuh the code (plus comments) and see what it does and how it differs from yours


    Option Explicit

    Sub LoopThroughFiles()
        Dim strFile As String, strPath As String
        Dim colFiles As New Collection
        Dim i As Integer
        Dim rLog As Range, rOut As Range
        Dim wsLog As Worksheet, wsOutp As Worksheet
        
        strPath = "C:\TEMP\"
        strFile = Dir(strPath)
        ' Make a log sheet
        On Error Resume Next
        Set wsLog = Sheets("PdfProcessLog")
        On Error GoTo 0
        If wsLog Is Nothing Then
            Set wsLog = ThisWorkbook.Sheets.Add(before:=Sheets(1))
            wsLog.Name = "PdfProcessLog"
        End If
        Set rLog = wsLog.Range("A1")
        rLog.CurrentRegion.ClearContents
        rLog.Value = "PDF files copied to sheets"
        
        ' load all the files in a Collection
        While strFile <> ""
            If StrComp(Right(strFile, 3), "pdf", vbTextCompare) = 0 Then
                colFiles.Add strFile
            End If
            strFile = Dir
        Wend
        
        Application.DisplayAlerts = False
        
        'Loop through the pdf's stored in the collection
        For i = 1 To colFiles.Count
            'List filenames in Column A of the log sheet
            rLog.Offset(i, 0).Value = colFiles(i)
            strFile = Left(colFiles(i), Len(colFiles(i)) - 4)
            
            ' Delete sheet with filename if exists
            On Error Resume Next
            Set wsOutp = Sheets(strFile)
            On Error GoTo 0
            If Not wsOutp Is Nothing Then
                wsOutp.Delete
            End If
            ' (Re)Create the worksheet, give it the file name
            Set wsOutp = ThisWorkbook.Sheets.Add(after:=wsLog)
            wsOutp.Name = strFile
            
            ' Now open the file, and copy contents
            OpenClosePDF colFiles(i), strPath
            CopyStep wsOutp
        Next i
        
        Application.DisplayAlerts = True

    End Sub
    Sub OpenClosePDF(ByVal sAdobeFile As String, ByVal sPath As String)

        Dim sAdobeApp As String
        Dim vStartAdobe As Variant
        
        sAdobeApp = "C:\Program Files (x86)\Adobe\Acrobat 5.0\Reader\AcroRd32.exe"
        sAdobeApp = "C:\Program Files\Adobe\Reader 11.0\Reader\AcroRd32.exe"
        vStartAdobe = Shell("" & sAdobeApp & " " & sPath & sAdobeFile & "", 1)
        Application.Wait (Now + TimeValue("0:00:01"))

    End Sub


    Private Sub CopyStep(wsOutp As Worksheet)

        ' select all & copy
        SendKeys "^a", True
        SendKeys "^c", True
         Application.Wait (Now + TimeValue("0:00:01"))
       ' Paste into the sheet from cell A1
        wsOutp.Paste Cells(1, 1)
      
        Application.Wait (Now + TimeValue("0:00:01"))
        AppActivate "Adobe Reader"
        ' close Reader
        SendKeys "%{F4}", True

    End Sub

    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. Ennef.nl

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,235

    Default Re: How to copy data from pdf to excel using VBA for multiple sheets?

    forgot to say - in the OpenClosePDF sub you need to comment out the wrong path to your adobe
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. Ennef.nl

  4. #4
    Board Regular
    Join Date
    May 2013
    Posts
    65

    Default Re: How to copy data from pdf to excel using VBA for multiple sheets?

    Thank you so much

  5. #5
    Board Regular
    Join Date
    May 2013
    Posts
    65

    Default Re: How to copy data from pdf to excel using VBA for multiple sheets?

    Hi

    I tried with the macro but it is creating one log file and saying as pdf copied to excel and i am not able to find where to put my pdf path file so i am uploading excel and pdf please help me regarding this

    https://drive.google.com/file/d/0B9Q...it?usp=sharing

    Thank you

  6. #6
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,235

    Default Re: How to copy data from pdf to excel using VBA for multiple sheets?

    Please go through the code: in the sub LoopThroughFiles you see a line where the path for the pdf's is set (At them monent it reads C:Temp). Change it to where your pdfs are.

    Try to understand what the code is doing, else you have endless problems if you ever want to change something.
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. Ennef.nl

  7. #7
    Board Regular
    Join Date
    May 2013
    Posts
    65

    Default Re: How to copy data from pdf to excel using VBA for multiple sheets?

    Hi,

    Thanks i am new to excel VBA and could not figure out whats happening

    I tried with changing the path but i am getting a error like "There was error opening this document.The file does not exist"

    Error line: AppActivate "Adobe Reader"{which showed after pressing debug}

  8. #8
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,235

    Default Re: How to copy data from pdf to excel using VBA for multiple sheets?

    What is the folder name of where your pdf's are stored?
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. Ennef.nl

  9. #9
    Board Regular
    Join Date
    May 2013
    Posts
    65

    Default Re: How to copy data from pdf to excel using VBA for multiple sheets?

    I placed on desktop and gave that path

  10. #10
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,235

    Default Re: How to copy data from pdf to excel using VBA for multiple sheets?

    Desktop is not a path. The path to Desktop is something like:
    C:\Users\John Dory\Desktop
    But why would you want 200 pdf files on your desktop? Unless they are in a folder in your desktop. Can you please give me the proper path, so I can amend the code so it will run?
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. Ennef.nl

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com