Help with Embed Macro

[Mldeuser]

Hello

I need some assistance with the below macro.

Sub Embed()
'
' Embed Macro
'

'
    ActiveSheet.Shapes.Range(Array("Object 775")).Select
    Selection.Delete
    Range("G11").Select
    ActiveSheet.OLEObjects.Add(Filename:= _
        "N:\FR\2020\10.2020\Reporting\03. Final PDFs\Individual Reports\Sample.pdf", Link:=False, DisplayAsIcon:=True, IconFileName:= _
        "C:\WINDOWS\Installer\{AC76BA86-1033-FFFF-7760-0C0F074E4100}\_PDFFile.ico", _
        IconIndex:=0, IconLabel:="Sample").Select
End Sub

1. How to get it to delete the old embedded file, the first line of the macro is not working.
2. Is there a way to have the file path change each month. The workbook name changes each month (10.2020) and the workbook and PDF files are stored in monthly folder. The PDF file name stays the same.
3 Can this macro be in my personal workbook instead of the client workbook.

Thank s for any assistance

 

[Domenic]

1. How to get it to delete the old embedded file, the first line of the macro is not working.

Assuming that the name of your object (the name that appears in the Name Box) is in fact "Object 775", and that the active sheet contains the object, try...

ActiveSheet.Shapes("Object 775").Delete

2. Is there a way to have the file path change each month. The workbook name changes each month (10.2020) and the workbook and PDF files are stored in monthly folder. The PDF file name stays the same.

Assuming that the day will always be two digits (ie. 01.2020, 02.2020, and so on until 12.2020), try...

Filename:="N:\FR\2020\" & Format(Date, "dd.yyyy") & "\Reporting\03. Final PDFs\Individual Reports\Sample.pdf", _

Otherwise, change the formatting to

Format(Date, "d.yyyy")

.

3 Can this macro be in my personal workbook instead of the client workbook.

Have a look at the following link...

Create and save all your macros in a single workbook - Microsoft Support

Create and save all your macros in a single workbook by creating them in Personal.xlsb, a hidden workbook that opens every time you open Excel. Macros created in Personal.xlsb are available every time you open a workbook in Excel.

support.microsoft.com

Hope this helps!

 

[Domenic]

It looks like each month you will be deleting the existing embedded file, and then replacing it with a new and current one. If so, here's a way to handle it without having to hard code the object's name into the code each time. First, re-name your existing object (using the Name Box) with a meaningful name. For this example, let's name it Report. Then, try the following macro. Each time it's run, it first deletes the object named Report, then it adds the current new file, and then names it Report.

Sub Embed()

    Const EMBEDDED_FILE_NAME As String = "Report" 'change the name as desired

    On Error Resume Next
    ActiveSheet.Shapes(EMBEDDED_FILE_NAME).Delete
    On Error GoTo 0

    Dim oleObj As OLEObject
    Set oleObj = ActiveSheet.OLEObjects.Add( _
            Filename:="N:\FR\2020\" & Format(Date, "dd.yyyy") & "\Reporting\03. Final PDFs\Individual Reports\Sample.pdf", _
            Link:=False, _
            DisplayAsIcon:=True, _
            IconFileName:="C:\WINDOWS\Installer\{AC76BA86-1033-FFFF-7760-0C0F074E4100}\_PDFFile.ico", _
            IconIndex:=0, _
            IconLabel:="Sample")
    
    With oleObj
        .Name = EMBEDDED_FILE_NAME
        .Left = Range("G11").Left
        .Top = Range("G11").Top
    End With
    
End Sub

Hope this helps!

 

[Mldeuser]

Quick question, can the macro look at the tab name and make that the active sheet. There are a few tabs that have embedded documents. I was thinking if this is possible I could copy the macro and paste it below the "End With" and update for the next tab and so on

Thank you

 

[Domenic]

Sorry, but I don't understand your question. Can you please clarify?

 

[Mldeuser]

On Error Resume Next
ActiveSheet.Shapes(EMBEDDED_FILE_NAME).Delete
On Error GoTo 0

Can the above look at the tab name instead of the active sheet.

 

[Domenic]

To specify a particular worksheet instead of the active worksheet, try the following macro instead. I've assume that Sheet1 contains the embedded file, and that it's located within the workbook running the programming. Change the references to the workbook and/or worksheet where specified as required.

Sub Embed()

    Const EMBEDDED_FILE_NAME As String = "Report" 'change the name as desired
    
    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = ThisWorkbook.Worksheets("Sheet1") 'change the workbook and sheet references accordingly

    On Error Resume Next
    sourceWorksheet.Shapes(EMBEDDED_FILE_NAME).Delete
    On Error GoTo 0

    Dim oleObj As OLEObject
    Set oleObj = sourceWorksheet.OLEObjects.Add( _
            Filename:="N:\FR\2020\" & Format(Date, "dd.yyyy") & "\Reporting\03. Final PDFs\Individual Reports\Sample.pdf", _
            Link:=False, _
            DisplayAsIcon:=True, _
            IconFileName:="C:\WINDOWS\Installer\{AC76BA86-1033-FFFF-7760-0C0F074E4100}\_PDFFile.ico", _
            IconIndex:=0, _
            IconLabel:="Sample")
    
    With oleObj
        .Name = EMBEDDED_FILE_NAME
        .Left = Range("G11").Left
        .Top = Range("G11").Top
    End With
    
End Sub

 

[Mldeuser]

Filename:="N:\FR\2020\" & Format(Date, "dd.yyyy") & "\Reporting\03. Final PDFs\Individual Reports\Sample.pdf", _

In this line if Ieave the date as is "dd.yyyy" I get an error stating it cannot find the file the date referenced in the error is 12.2020. If I change the dd.yyyy to 10.2020 i get the same error however the date changes to "144147.2020"

I am not sure what I may be doing wrong.

 

[Domenic]

Check to make sure that the path to the file is correct. Actually, enter the following line in the Immediate Window ( Ctrl+G ), and press the Enter key...

? Dir("N:\FR\2020\" & Format(Date, "dd.yyyy") & "\Reporting\03. Final PDFs\Individual Reports\Sample.pdf", vbNormal)

If the path and filename are correct, it should return the name of the file, which in this case is Sample.pdf. Does it return the filename?