embed an object in excel based on user values

[learning_grexcel]

Hi,

I have an excel file with embedded pdf object (as an icon) in B1.

Is it possible to change the file based on the user values entered in A1 ?


For e.g. if user types the name "tony" in A1, it shall show the resume of that person in B1(embedded icon in pdf). If they type again "Greg", it shall automatically replace the previous pdf and show Greg's pdf file there (as an icon)

 

[Domenic]

The file name is "greig" (pdf file) in desktop.

Just to be clear, the filename for your file on your desktop should end with the file extension .pdf. So the filename should be greig.pdf. Is this the case?

 

[learning_grexcel]

Just to be clear, the filename for your file on your desktop should end with the file extension .pdf. So the filename should be greig.pdf. Is this the case?

Yes, it is greig.pdf
File name is greig and extension .pdf

 

[Domenic]

For testing purposes only, try adding the following lines in red, and then try entering a name in A1. Do you get a message saying that the file does not exist?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    Dim objOLE As OLEObject
    Dim strFolder As String
    Application.EnableEvents = False
    For Each objOLE In Me.OLEObjects
        If objOLE.TopLeftCell.Address = Target.Offset(, 1).Address Then
            objOLE.Delete
        End If
    Next objOLE
    If Len(Target) > 0 Then
        strFolder = "C:\Users\Domenic\Desktop\"
        If Right(strFolder, 1) <> "\" Then
            strFolder = strFolder & "\"
        End If
[COLOR=#ff0000]        If Len(Dir(strFolder & Target.Value & ".pdf")) = 0 Then[/COLOR]
[COLOR=#ff0000]            MsgBox strFolder & Target.Value & ".pdf does not exist!", vbExclamation[/COLOR]
[COLOR=#ff0000]        End If[/COLOR]
        On Error Resume Next
        Me.OLEObjects.Add Filename:=strFolder & Target.Value & ".pdf", Link:=False, DisplayAsIcon:=True, Left:=Target.Offset(, 1).Left, Top:=Target.Offset(, 1).Top
        On Error GoTo 0
    End If
    Application.EnableEvents = True
End Sub

 

[learning_grexcel]

For testing purposes only, try adding the following lines in red, and then try entering a name in A1. Do you get a message saying that the file does not exist?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    Dim objOLE As OLEObject
    Dim strFolder As String
    Application.EnableEvents = False
    For Each objOLE In Me.OLEObjects
        If objOLE.TopLeftCell.Address = Target.Offset(, 1).Address Then
            objOLE.Delete
        End If
    Next objOLE
    If Len(Target) > 0 Then
        strFolder = "C:\Users\Domenic\Desktop\"
        If Right(strFolder, 1) <> "\" Then
            strFolder = strFolder & "\"
        End If
[COLOR=#ff0000]        If Len(Dir(strFolder & Target.Value & ".pdf")) = 0 Then[/COLOR]
[COLOR=#ff0000]            MsgBox strFolder & Target.Value & ".pdf does not exist!", vbExclamation[/COLOR]
[COLOR=#ff0000]        End If[/COLOR]
        On Error Resume Next
        Me.OLEObjects.Add Filename:=strFolder & Target.Value & ".pdf", Link:=False, DisplayAsIcon:=True, Left:=Target.Offset(, 1).Left, Top:=Target.Offset(, 1).Top
        On Error GoTo 0
    End If
    Application.EnableEvents = True
End Sub

Still nothing happens after I type "greig" and enter. It just loads something "=embed("")" in address bar and nothing happens.

FYI, my excel filename is ".xlt" because it asked me to save macro enabled extension.

 

[Domenic]

What text do you see in =embed(".....")? If you see =embed("") in the address bar, the pdf should have been embedded within the worksheet to the right of A1. Do you not see an icon displayed? If not, maybe it's there but it's not visible. See whether the object can be selected by doing the following on the ribbon...

Home tab >> Editing group >> Find & Select >> Go To Special >> Objects >> OK

Do you see an object selected? If you have other objects as well, all objects will be selected. In this case, you can use the Tab key to cycle through the objects.

 

[learning_grexcel]

What text do you see in =embed(".....")? If you see =embed("") in the address bar, the pdf should have been embedded within the worksheet to the right of A1. Do you not see an icon displayed? If not, maybe it's there but it's not visible. See whether the object can be selected by doing the following on the ribbon...

Home tab >> Editing group >> Find & Select >> Go To Special >> Objects >> OK

Do you see an object selected? If you have other objects as well, all objects will be selected. In this case, you can use the Tab key to cycle through the objects.

Great..Many thanks....Yes, it is hidden next to A1...I just dragged it outside after your post. The icon is blank white color square but when I click it opens the file correctly.
Is it possible to change the icon to pdf (same as when we insert pdf object manually)?

 

[learning_grexcel]

Also, every time, when I put new file name, the new icon hides. I need to use your instruction find and select >> ...Objects >>. ok then drag it outside.

 

[Domenic]

Try specifying the icon filename, index, and label, as per the lines in red...

        On Error Resume Next
        Me.OLEObjects.Add _
            Filename:=strFolder & Target.Value & ".pdf", _
            Link:=False, _
            DisplayAsIcon:=True, _
[COLOR=#ff0000]            IconFileName:="C:\Windows\system32\packager.dll", _[/COLOR]
[COLOR=#ff0000]            IconIndex:=0, _[/COLOR]
[COLOR=#ff0000]            IconLabel:=Target.Value, _[/COLOR]
            Left:=Target.Offset(, 1).Left, _
            Top:=Target.Offset(, 1).Top
        On Error GoTo 0

You may need to change the icon filename. You can always record a macro while you manually insert a PDF file to see the correct filename to use.

 

[learning_grexcel]

Try specifying the icon filename, index, and label, as per the lines in red...

        On Error Resume Next
        Me.OLEObjects.Add _
            Filename:=strFolder & Target.Value & ".pdf", _
            Link:=False, _
            DisplayAsIcon:=True, _
[COLOR=#ff0000]            IconFileName:="C:\Windows\system32\packager.dll", _[/COLOR]
[COLOR=#ff0000]            IconIndex:=0, _[/COLOR]
[COLOR=#ff0000]            IconLabel:=Target.Value, _[/COLOR]
            Left:=Target.Offset(, 1).Left, _
            Top:=Target.Offset(, 1).Top
        On Error GoTo 0

You may need to change the icon filename. You can always record a macro while you manually insert a PDF file to see the correct filename to use.

Thanks a lot. Now it shows the icon, not hiding anymore.

However, I have few more adjustments to do. Is it possible?
(1) The icon now it embeds is still not the PDF icon. It just inserts general icon.
(2) Same as A1, if I type in A2, A3, A4, and so on in A column. it shall insert the icon based on the A2, A3, A4, etc. value.

 

[Domenic]

I would suggest that you record a macro while you manually insert your PDF file onto your worksheet. In the dialog box, when you select "Display as Icon", the PDF icon should be automatically displayed. If so, the recorded code should contain the proper icon filename.

Does this help?