VBA to create userform that shows source excel files in PPT presentation (64-bit)

[Sizzlechest]

I realize this is an excel forum and my question is more PowerPoint related but was hoping someone has dealt with this issue before.

I am trying to create a userform similar to "Edit Links to File" in 64-bit PowerPoint that lists the source files (with address) for all excel charts, etc. linked to my PowerPoint presentation - but ONLY displays the address of the source file...not an itemized list of every single item linked.

For example, I have a presentation with over 40 slides with over 80 linked items from several source excel files (charts, etc.). If I go to File and select "Edit Links to Files" the pop up window shows every single linked item. I want the userform to show a list of only the source files of the linked objects. This way, if I want to quickly update all links I know which source files I have to open and then "Update Links" when opening the PPT file. The next step would be to be able to select a particular source file from the list and change it to a new source file.

The code needs to work in 64-bit PowerPoint as we recently upgraded to 64-bit MS Office. I've seen code that uses "MSComctlLib.ListView" but understand it doesn't work on 32-bit applications.

[dflak]

I don't know if this is what you want

Dim MyPath As String
Dim FileName As String

Dim objPPT As Object
Dim ppt As Object
Dim sld As Object
Dim shp As Object
Dim PPName As String

Dim sh As Excel.Worksheet
Dim RowNum As Long

Set sh = Sheets("Sheet1")
RowNum = 2

MyPath = ThisWorkbook.Path
PPName = MyPath & "\" & Range("Rpt_Name")

' Open the PowerPoint Presentation
Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
objPPT.presentations.Open PPName

Set ppt = objPPT.activepresentation

sh.Cells.ClearContents
RowNum = 2

For Each sld In ppt.slides
    For Each shp In sld.Shapes
        sh.Cells(RowNum, 1) = sld.Name
        sh.Cells(RowNum, 2) = shp.Name
        If shp.Type = msoLinkedOLEObject Then
            sh.Cells(RowNum, 3) = shp.LinkFormat.sourcefullname
        End If
        RowNum = RowNum + 1
    Next
Next

ppt.Close
Set ppt = Nothing
objPPT.Quit
Set objPPT = Nothing

[Sizzlechest]

I'm getting a run-time error 1004, "Method of 'Sheets' of object _Global failed" on row: Set sh = Sheets("Sheet1")

[dflak]

Do you have a sheet named "Sheet1" in your workbook?

[Sizzlechest]

Do you have a sheet named "Sheet1" in your workbook?

Yes, not sure what's going on.

[dflak]

Try

Set sh = ActiveSheet

instead of

Set sh = Sheets("Sheet1")

Anyway, the output should look something like:

[Sizzlechest]

Thanks, got it working now. Appreciate it.