Insert Embedded File (Cancel)

[Jeffrey Mahoney]

This is part of my code where I import a PDF file to embed into excel. It works. This opens a file dialog where I choose the PDF. If I don't choose a file and cancel, it still returns an Object, so I can't test to see if the user actually chose a file. How can I test if the user pressed cancel instead?

With oSht
    On Error Resume Next
    Set oPDF = .OLEObjects.Add(ClassType:="AcroExch.Document.DC", Link:=False, DisplayAsIcon:=True, IconFileName:= _
        """C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"" ""%1""" _
        , IconIndex:=0, IconLabel:="Adobe Acrobat Document", Left:=cLeft, Top:=cTop, Height:=50, Width:=50)
    On Error GoTo 0
    
    If oPDF Is Nothing Then
      EventsOn
      Exit Sub
    End If

 

[John_w]

Use a separate FileDialog and, if not cancelled, specify the Filename argument with the selected file in the OLEObjects.Add instead of the ClassType argument:

    Dim fd As FileDialog, selectedFile As String
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        .Title = "Select PDF file to insert"
        .Filters.Clear
        .Filters.Add "PDF documents", "*.pdf"
        If Not .Show Then
            MsgBox "User cancelled"
            Exit Sub
        End If
        selectedFile = .SelectedItems(1)
    End With
    
    With oSht
        Set oPDF = .OLEObjects.Add(Filename:=selectedFile, Link:=False, DisplayAsIcon:=True, IconFileName:= _
            """C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"" ""%1""" _
            , IconIndex:=0, IconLabel:="Adobe Acrobat Document", Left:=cLeft, Top:=cTop, Height:=50, Width:=50)
    End With

 

[Jeffrey Mahoney]

John,

This is strangest thing. I did try to go get the filename prior to invoking the OLEObjects.add. I replaced the ClassType parameter with the FileName parameter. Now, when I run it, I get two OpenFile dialogs. One for the get file name and one for the OLEObjects.add. The FileName parameter is only looking for a string with a Path and file name, right?

 

[Jeffrey Mahoney]

My variable oPDF is Dimmed like this:
Dim oPDF as OLEObject

 

[Jeffrey Mahoney]

Now, for no reason at all, I can't open the embedded pdf files. My co-worker can. Nothing has changed on my computer since yesterday. Ugh. I've tried a couple different methods

Set aSht = ActiveSheet
  Set oSht = Sheets("Objects")
  Application.ScreenUpdating = False
  'oSht.OLEObjects(A).Activate
  oSht.OLEObjects(A).Verb Verb:=xlVerbOpen
  aSht.Activate
  Application.ScreenUpdating = True

The reason I have to activate the current sheet is because vba activates the Objects sheet before opening the PDF. Is there a way to stop that?

Is there a better way to reference the path to the reader? Maybe using system paths? I don't know if everybody out there uses the same app to view PDF files.
"""C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"" ""%1"""

 

[John_w]

This works me and only 1 file dialogue is shown. The IconFileName was generated by the macro recorder.

Public Sub Insert_PDF()

    Dim fd As FileDialog, selectedFile As String
    Dim cLeft As Single, cTop As Single
    Dim oPDF As OLEObject
    
    cLeft = ActiveCell.Left
    cTop = ActiveCell.Top
        
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        .Title = "Select PDF file to insert"
        .Filters.Clear
        .Filters.Add "PDF documents", "*.pdf"
        If Not .Show Then
            MsgBox "User cancelled"
            Exit Sub
        End If
        selectedFile = .SelectedItems(1)
    End With
    
    With ActiveCell.Worksheet
        Set oPDF = .OLEObjects.Add(Filename:=selectedFile, Link:=False, _
            DisplayAsIcon:=True, IconFileName:="C:\WINDOWS\Installer\{AC76BA86-1033-FFFF-7760-000000000006}\_PDFFile.ico", _
            IconIndex:=0, IconLabel:="Adobe Acrobat Document", Left:=cLeft, Top:=cTop, Height:=50, Width:=50)
    End With
    
    MsgBox "Inserted " & oPDF.Name & " at " & oPDF.TopLeftCell.Address
    
End Sub

Use this to get the executable associated with .pdf files:

#If VBA7 Then
    Private Declare PtrSafe Function FindExecutable Lib "shell32.dll" Alias "FindExecutableA" (ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long
#Else
    Private Declare Function FindExecutable Lib "shell32.dll" Alias "FindExecutableA" (ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long
#End If

Sub Test()
    Debug.Print Get_ExePath(selectedFile)
End Sub

Private Function Get_ExePath(lpFile As String) As String
    Dim lpDirectory As String, sExePath As String, rc As Long
    lpDirectory = "\"
    sExePath = Space(255)
    rc = FindExecutable(lpFile, lpDirectory, sExePath)
    Get_ExePath = Left$(sExePath, InStr(sExePath, Chr$(0)) - 1)
End Function

 

[Jeffrey Mahoney]

Thank you John. Embedding the PDF worked. I may use the other code later