Consulting

Results 1 to 2 of 2

Thread: PDF Save as .xlsx

  1. #1

    PDF Save as .xlsx

    Hi everyone

    Is it possible to do a VBA in Excel, that will open a PDF and save that PDF as .XLSX? Basically the same task as the Adobe Acrobat is doing converting the PDF to .xlsx, when opening the PDF and clicking Save As.

    The Excel VBA should open the PDF and then use the Save As function from the PDF by Adobe Acrobat.

    Many thanks,
    John

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    'http://www.myengineeringworld.net/2013/03/vba-macro-to-convert-pdf-files-into.htmlSub 
    Main()
      Dim s As String
       s = "C:\Users\Ken\Dropbox\Excel\pdf\payslips.pdf"
      SavePDFAsOtherFormat s, "xlsx"
    End Sub
    
    
    Sub SavePDFAsOtherFormat(PDFPath As String, FileExtension As String)
     
        'Saves a PDF file as another format using Adobe Professional.
     
        'By Christos Samaras
        'http://www.myengineeringworld.net
     
        '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 objAcroApp      As Object 'Acrobat.AcroApp
        Dim objAcroAVDoc    As Object 'Acrobat.AcroAVDoc
        Dim objAcroPDDoc    As Object 'Acrobat.AcroPDDoc
        Dim objJSO          As Object
        Dim boResult        As Boolean
        Dim ExportFormat    As String
        Dim NewFilePath     As String
     
        'Check if the file exists.
        If Dir(PDFPath) = "" Then
            MsgBox "Cannot find the PDF file!" & vbCrLf & "Check the PDF path and retry.", _
                    vbCritical, "File Path Error"
            Exit Sub
        End If
     
        'Check if the input file is a PDF file.
        If LCase(Right(PDFPath, 3)) <> "pdf" Then
            MsgBox "The input file is not a PDF file!", vbCritical, "File Type Error"
            Exit Sub
        End If
     
        'Initialize Acrobat by creating App object.
        Set objAcroApp = CreateObject("AcroExch.App")
     
        'Set AVDoc object.
        Set objAcroAVDoc = CreateObject("AcroExch.AVDoc")
     
        'Open the PDF file.
        boResult = objAcroAVDoc.Open(PDFPath, "")
         
        'Set the PDDoc object.
        Set objAcroPDDoc = objAcroAVDoc.GetPDDoc
     
        'Set the JS Object - Java Script Object.
        Set objJSO = objAcroPDDoc.GetJSObject
     
        'Check the type of conversion.
        Select Case LCase(FileExtension)
            Case "eps": ExportFormat = "com.adobe.acrobat.eps"
            Case "html", "htm": ExportFormat = "com.adobe.acrobat.html"
            Case "jpeg", "jpg", "jpe": ExportFormat = "com.adobe.acrobat.jpeg"
            Case "jpf", "jpx", "jp2", "j2k", "j2c", "jpc": ExportFormat = "com.adobe.acrobat.jp2k"
            Case "docx": ExportFormat = "com.adobe.acrobat.docx"
            Case "doc": ExportFormat = "com.adobe.acrobat.doc"
            Case "png": ExportFormat = "com.adobe.acrobat.png"
            Case "ps": ExportFormat = "com.adobe.acrobat.ps"
            Case "rft": ExportFormat = "com.adobe.acrobat.rft"
            Case "xlsx": ExportFormat = "com.adobe.acrobat.xlsx"
            Case "xls": ExportFormat = "com.adobe.acrobat.spreadsheet"
            Case "txt": ExportFormat = "com.adobe.acrobat.accesstext"
            Case "tiff", "tif": ExportFormat = "com.adobe.acrobat.tiff"
            Case "xml": ExportFormat = "com.adobe.acrobat.xml-1-00"
            Case Else: ExportFormat = "Wrong Input"
        End Select
      
        'Check if the format is correct and there are no errors.
        If ExportFormat <> "Wrong Input" And Err.Number = 0 Then
          
            'Format is correct and no errors.
          
            'Set the path of the new file. Note that Adobe instead of xls uses xml files.
            'That's why here the xls extension changes to xml.
            If LCase(FileExtension) <> "xls" Then
                NewFilePath = WorksheetFunction.Substitute(PDFPath, ".pdf", "." & LCase(FileExtension))
            Else
                NewFilePath = WorksheetFunction.Substitute(PDFPath, ".pdf", ".xml")
            End If
          
            'Save PDF file to the new format.
            boResult = objJSO.SaveAs(NewFilePath, ExportFormat)
          
            'Close the PDF file without saving the changes.
            boResult = objAcroAVDoc.Close(True)
          
            'Close the Acrobat application.
            boResult = objAcroApp.Exit
          
            'Inform the user that conversion was successfully.
            MsgBox "The PDf file:" & vbNewLine & PDFPath & vbNewLine & vbNewLine & _
            "Was saved as: " & vbNewLine & NewFilePath, vbInformation, "Conversion finished successfully"
           
        Else
         
            'Something went wrong, so close the PDF file and the application.
         
            'Close the PDF file without saving the changes.
            boResult = objAcroAVDoc.Close(True)
         
            'Close the Acrobat application.
            boResult = objAcroApp.Exit
         
            'Inform the user that something went wrong.
            MsgBox "Something went wrong!" & vbNewLine & "The conversion of the following PDF file FAILED:" & _
            vbNewLine & PDFPath, vbInformation, "Conversion failed"
    
    
    
    
        End If
         
        'Release the objects.
        Set objAcroPDDoc = Nothing
        Set objAcroAVDoc = Nothing
        Set objAcroApp = Nothing
         
    End Sub

Posting Permissions

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