Import or Insert PDF file into Excel by VBA

[Rahulwork]

Hi Everyone,

I have a button and i need help your assistance in a macro on this button with following output.

1. Motive of this button is to insert document into excel as an icon

2. Document path should be based on A1 (Drive Location) + file name mentioned in code (.pdf)

3. Document caption should be defined in Code.

4. Cell location should also defined in code

Currently i have below code but it's not working and i know there is a need of amendment in this:

PSub Button21_Click()
Worksheets("Sheet1").OLEObjects.Add Filename:="c:\temp\sample.pdf", Link:=False, DisplayAsIcon:=False, Left:=40, Top:=40, Width:=150, Height:=10
End Sub

If m changing DisplayAsIcon as Yes it's uploading but no icon is there. it's showing as a blank icon.

Please help me with this. let me know if you need any information around this


Thanks

 

[Domenic]

The following code references the icon filename and index. To find the filename and view the icons available, add the oleobject manually...

Ribbon >> Insert >> Text >> Object >> Create from file >> check/select Display as icon >> click Change icon

There you'll find the filename you'll need to reference, and you'll be able to see the different icons available. Note that the index for these icons are 0-based.

Sub Button21_Click()

    Dim strPath As String
    Dim strFilename As String
    Dim strCaption As String
    Dim wksTarget As Worksheet
    Dim rngTarget As Range
    
    Set wksTarget = Worksheets("Sheet1")
    
    Set rngTarget = wksTarget.Range("A3") 'change the location in which to insert your object as desired
    
    strPath = wksTarget.Range("A1").Value
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    
    strFilename = "sample.pdf"
    
    If Len(Dir(strPath & strFilename, vbNormal)) = 0 Then
        MsgBox "'" & strPath & strFilename & "' does not exist!", vbExclamation, "Path and/or file?"
        Exit Sub
    End If
    
    strCaption = "myCaption" 'change the caption as desired
    
    wksTarget.OLEObjects.Add _
        Filename:=strPath & strFilename, _
        link:=False, _
        displayasicon:=True, _
        iconfilename:="C:\WINDOWS\system32\packager.dll", _
        iconindex:=0, _
        iconlabel:=strCaption, _
        Left:=rngTarget.Left, _
        Top:=rngTarget.Top, _
        Width:=150, _
        Height:=10
    
End Sub

Hope this helps!

 

[Rahulwork]

Thanks Mate, you are a champ.

I just changed IconFileName as per my system. Well i am curious to know that what amendment i have to make in code if i want to import more than 1 file from same path (Sample2.pdf) by clicking on same button.