Import or Insert PDF file into Excel by VBA

Rahulwork

Active Member
Joined
Jun 9, 2013
Messages
284
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
The following code references the icon filename and index. To find the filename and view the icons available, add the oleobject manually...

Code:
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.

Code:
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top