Consulting

Results 1 to 3 of 3

Thread: VBA Code - Convert PDF to Excel using Word

  1. #1
    VBAX Regular
    Joined
    Oct 2015
    Posts
    21
    Location

    VBA Code - Convert PDF to Excel using Word

    Hello,

    I am using the code below in Excel to open a PDF File in Word and extract the data as text only into Excel. The code works perfectly fine as it is, however, I want to loop through all pdf files within the folder instead of hardcoding the filename in pathAndFileName variable. Can someone assist with this piece of the code?

    My goal is to be able to:


    1. Open each PDF file in Word
    2. Extract its data as text into the Excel “Test”
    3. Run two excel macros to parse the data that Ineed into another sheet within the same workbook set in the code
    4. Close Word without saving the file
    5. Clear both Word and PDF
    6. Clear the data in Excel “Test” worksheet
    7. Move PDF File to another folder
    8. Repeat steps 1-6 for the next PDF File



    Can anyone assist in tweaking this code to do what I need?

    Sub pdf_To_Excel_Word()
    'Macro opens PDF Files as an editable Word Document
    'Copies the contents of the Word document
    'Pastes the Clipboard contents into Excel
    
    'Declare Variables
        Dim myWorksheet As Worksheet
        Dim wordApp AsWord.Application
        Dim myWshShell As wshShell
        Dim pathAndFileName As String
        Dim registryKey As String
        Dim wordVersion As String
    
    'Set Variables
        Set myWorksheet =ActiveWorkbook.Worksheets("Test")
        Set wordApp = NewWord.Application
        Set myWshShell = New wshShell
        pathAndFileName ="C:\Users\mh15601\Desktop\Projects\Audit Plan\2019\Testing\PDF Files\DONOT DELETE\A171065-ICG-TTS-Cash Management and Trade-Citibank NA-IndonesiaAudit Report.pdf"
        wordVersion = wordApp.Version
        registryKey ="HKCU\SOFTWARE\Microsoft\Office\" & wordVersion &"\Word\Options\"
    
    'Open and Copy PDF Files
        myWshShell.RegWriteregistryKey & "DisableConvertPdfWarning", 1,"REG_DWORD"
    
        wordApp.documents.Open _
           Filename:=pathAndFileName, _
            confirmconversions:=False
    
        myWshShell.RegWriteregistryKey & "DisableConvertPdfWarning", 0,"REG_DWORD"
    
    'Copy Data from Word
       wordApp.ActiveDocument.Content.Copy
    
    'Excel
        With myWorksheet
           .Range("A1").Select
            .PasteSpecialFormat:="Text"
        End With
    
    'Close Word
        wordApp.QuitSaveChanges:=wDoNotSaveChanges
    
    'Clear Word and PDF
        Set wordApp = Nothing
        Set myWshShell = Nothing
    
    
    End Sub 


    Thank you



  2. #2
    The following should paste each pdf document in strPath to a new worksheet.
    I'll let you decode what you want to do with the PDFs

    Sub pdf_To_Excel_Word()
    'Macro opens PDF Files as a editable Word Documenta
    'Copies the contents of the Word documents
    'Pastes the Clipboard contents into Excel
    
    
    'Declare Variables
    Dim myWorksheet As Worksheet
    Dim wordApp As Object
    Dim myWshShell As Object
    Dim strPath As String
    Dim oDoc As Object
    Dim strFile As String
    Dim registryKey As String
    Dim wordVersion As String
    
    
        'Set Variables
        On Error Resume Next
        Set wordApp = GetObject(, "Word.Application")
        If Err Then
            Set wordApp = CreateObject("Word.Application")
        End If
        On Error GoTo 0
    
    
        Set myWshShell = CreateObject("WScript.Shell")
        strPath = "C:\Users\mh15601\Desktop\Projects\Audit Plan\2019\Testing\PDF Files\DONOT DELETE\"
        wordVersion = wordApp.Version
        registryKey = "HKCU\SOFTWARE\Microsoft\Office\" & wordVersion & "\Word\Options\"
    
    
        'Open and Copy PDF Files
        myWshShell.RegWrite registryKey & "DisableConvertPdfWarning", 1, "REG_DWORD"
    
    
        strFile = Dir$(strPath & "*.pdf")
        While strFile <> ""
            Set oDoc = wordApp.Documents.Open(FileName:=strPath & strFile, _
                                              confirmconversions:=False)
            oDoc.Content.Copy
            'Excel
            Set myWorksheet = ActiveWorkbook.Worksheets.Add
            With myWorksheet
                .Range("A1").Select
                .PasteSpecial Format:="Text"
            End With
            oDoc.Close SaveChanges:=0
            strFile = Dir$()
        Wend
    
    
        'Close Word
        wordApp.Quit SaveChanges:=0
        myWshShell.RegWrite registryKey & "DisableConvertPdfWarning", 0, "REG_DWORD"
        'Clear Word and PDF
        Set wordApp = Nothing
        Set myWshShell = Nothing
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Regular
    Joined
    Oct 2015
    Posts
    21
    Location
    Hello,

    I tested your code and its not doing anything at all.
    There is no error or anything happening when I run it.

Posting Permissions

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