Embed a PDF document and rename

[Sumeluar]

Good day to all.

I have the below code that embeds a PDF document of my choice and changes the icon as per indicated path, up to there is all good. What I need assistance with is two things, one to have a popup message asking me to enter the desired name and to change the embedded document properties by making it transparent and without borders.

#
Sub InsertPDF()

Dim ChangeIcon As String, fullFileName As String

ChangeIcon = "E:\Icons\Reader.ico"

ActiveSheet.OLEObjects.Add(Filename:=Application.GetOpenFilename, _
Link:=False, _
DisplayAsIcon:=True, _
IconFileName:=ChangeIcon, _
IconIndex:=0, _
IconLabel:="Embeded Doc.").Select

End Sub
#

All ideas are greatly appreciated, thank you all before hand.

Warm regards, Sumeluar.

 

[Domenic]

Try the following...

Sub InsertPDF()

    Dim fullFileName As Variant
    fullFileName = Application.GetOpenFilename( _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Select PDF file", _
        ButtonText:="Select")
        
    If fullFileName = False Then Exit Sub

    Dim ChangeIcon As String
    ChangeIcon = "E:\Icons\Reader.ico"
    
    Dim embeddedPdfFile As OLEObject
    Set embeddedPdfFile = ActiveSheet.OLEObjects.Add( _
        Filename:=fullFileName, _
        Link:=False, _
        DisplayAsIcon:=True, _
        IconFileName:=ChangeIcon, _
        IconIndex:=0, _
        IconLabel:="Embeded Doc.")
        
    With embeddedPdfFile
        .ShapeRange.Fill.Transparency = 1
        .Border.LineStyle = xlNone
    End With

End Sub

Hope this helps!

 

[Domenic]

...to have a popup message asking me to enter the desired name

The desired name for your embedded PDF document?

 

[Sumeluar]

Try the following...

Sub InsertPDF()

    Dim fullFileName As Variant
    fullFileName = Application.GetOpenFilename( _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Select PDF file", _
        ButtonText:="Select")
       
    If fullFileName = False Then Exit Sub

    Dim ChangeIcon As String
    ChangeIcon = "E:\Icons\Reader.ico"
   
    Dim embeddedPdfFile As OLEObject
    Set embeddedPdfFile = ActiveSheet.OLEObjects.Add( _
        Filename:=fullFileName, _
        Link:=False, _
        DisplayAsIcon:=True, _
        IconFileName:=ChangeIcon, _
        IconIndex:=0, _
        IconLabel:="Embeded Doc.")
       
    With embeddedPdfFile
        .ShapeRange.Fill.Transparency = 1
        .Border.LineStyle = xlNone
    End With

End Sub

Hope this helps!

Domenic - Thanks for the prompt reply, half ways there as the embedded document does not give the option of a custom name.

 

[Sumeluar]

The desired name for your embedded PDF document?

Domenic - I need the ability to enter a custom name for the document, all the documents embedded are currently showing the same name "Embeded Doc.".

 

[Sumeluar]

Domenic - I need the ability to enter a custom name for the document, all the documents embedded are currently showing the same name "Embeded Doc.".

 

[Domenic]

Okay, try the following instead...

Sub InsertPDF()

    Dim fullFileName As Variant
    fullFileName = Application.GetOpenFilename( _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Select PDF file", _
        ButtonText:="Select")
       
    If fullFileName = False Then Exit Sub
   
    Dim embeddedFileName As String
    Do
        embeddedFileName = VBA.InputBox("Enter custom PDF filename.", "PDF Filename")
        If StrPtr(embeddedFileName) = 0 Then Exit Sub
    Loop Until Len(embeddedFileName) > 0

    Dim ChangeIcon As String
    ChangeIcon = "E:\Icons\Reader.ico"
   
    Dim embeddedPdfFile As OLEObject
    Set embeddedPdfFile = ActiveSheet.OLEObjects.Add( _
        Filename:=fullFileName, _
        Link:=False, _
        DisplayAsIcon:=True, _
        IconFileName:=ChangeIcon, _
        IconIndex:=0, _
        IconLabel:=embeddedFileName)
       
    With embeddedPdfFile
        .ShapeRange.Fill.Transparency = 1
        .Border.LineStyle = xlNone
    End With

End Sub

Hope this helps!

 

[Sumeluar]

Okay, try the following instead...

Sub InsertPDF()

    Dim fullFileName As Variant
    fullFileName = Application.GetOpenFilename( _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Select PDF file", _
        ButtonText:="Select")
      
    If fullFileName = False Then Exit Sub
  
    Dim embeddedFileName As String
    Do
        embeddedFileName = VBA.InputBox("Enter custom PDF filename.", "PDF Filename")
        If StrPtr(embeddedFileName) = 0 Then Exit Sub
    Loop Until Len(embeddedFileName) > 0

    Dim ChangeIcon As String
    ChangeIcon = "E:\Icons\Reader.ico"
  
    Dim embeddedPdfFile As OLEObject
    Set embeddedPdfFile = ActiveSheet.OLEObjects.Add( _
        Filename:=fullFileName, _
        Link:=False, _
        DisplayAsIcon:=True, _
        IconFileName:=ChangeIcon, _
        IconIndex:=0, _
        IconLabel:=embeddedFileName)
      
    With embeddedPdfFile
        .ShapeRange.Fill.Transparency = 1
        .Border.LineStyle = xlNone
    End With

End Sub

Hope this helps!

Domenic - Thank you for the quick solution, exactly what I was looking for.

 

[Domenic]

That's great, glad I could help.

Cheers!