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 following macro uses the worksheet change event to change the embedded PDF file whenever a user enters a name in cell A1. It assumes that the name entered in A1 excludes the .pdf file extension for its corresponding PDF file, and that the corresponding PDF filename will be made up of the name entered in A1 and .pdf file extension. So if "john" is entered in A1, there will be a corresponding file named john.pdf.

To use the following code, right-click the sheet tab for your sheet, select View Code, and copy/paste the code into the code module for your sheet. Note that you'll need to change the path to the folder containing the PDF's, accordingly.

Option Explicit

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\" 'change the path to your folder accordingly
        If Right(strFolder, 1) <> "\" Then
            strFolder = strFolder & "\"
        End If
        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

Hope this helps!

 

[learning_grexcel]

The following macro uses the worksheet change event to change the embedded PDF file whenever a user enters a name in cell A1. It assumes that the name entered in A1 excludes the .pdf file extension for its corresponding PDF file, and that the corresponding PDF filename will be made up of the name entered in A1 and .pdf file extension. So if "john" is entered in A1, there will be a corresponding file named john.pdf.

To use the following code, right-click the sheet tab for your sheet, select View Code, and copy/paste the code into the code module for your sheet. Note that you'll need to change the path to the folder containing the PDF's, accordingly.

Option Explicit

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\" 'change the path to your folder accordingly
        If Right(strFolder, 1) <> "\" Then
            strFolder = strFolder & "\"
        End If
        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

Hope this helps!

Thanks for your answer.

I'm not sure what I did wrong, but I get the message when I try to run the Macro "Invalid use of Me object"

 

[Domenic]

As already mentioned, the code needs to be copied/pasted into the code module for your sheet, not a regular module...

To use the following code, right-click the sheet tab for your sheet, select View Code, and copy/paste the code into the code module for your sheet. Note that you'll need to change the path to the folder containing the PDF's, accordingly.

 

[learning_grexcel]

As already mentioned, the code needs to be copied/pasted into the code module for your sheet, not a regular module...

Thanks.

When I try this, I cannot see the macro to run.

 

[learning_grexcel]

Sorry, here is the image.

 

[Domenic]

The code is a worksheet change event declared as Private and placed in the sheet module. So you won't see it listed. The code will be triggered automatically whenever a user enters or changes the value in A1, as per your original post...

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)

Or is this not your intent?

 

[learning_grexcel]

The code is a worksheet change event declared as Private and placed in the sheet module. So you won't see it listed. The code will be triggered automatically whenever a user enters or changes the value in A1, as per your original post...

Thanks, You are correct. I didn't know the code could trigger automatically. I thought it must be run.

Regarding the code, I already changed the path and exactly typed the text as per the filename in A1, but nothing happens.

I see something =embed("") pops up in address bar after I press "Enter button" but nothing happens.

 

[Domenic]

Just to be sure, can you provide the following...

1) An example of the value entered in A1?

2) The corresponding filename for the value entered in A1?

 

[learning_grexcel]

Just to be sure, can you provide the following...

1) An example of the value entered in A1?

2) The corresponding filename for the value entered in A1?

The value entered is just "greig" in A1. The file name is "greig" (pdf file) in desktop. The path also I already replaced in the code.