Another VBA BANGER - Manually Update External Links One Link at a Time

The Sandbox Software/Technology
1
  1. There exists several external linked objects in a Powerpoint file. These external links point to files on a network drive
    1b. Powerpoint wants to update external links
  2. For every single link, Powerpoint opens that excel file (downloads it from the network) and updates the link
  3. Powerpoint closes the workbook and moves to the next link
  4. On a file with 350 external links, this takes an hour which is dumb

SO

If I have the external linked files open in Excel, the updating process is faster. This is what Iā€™d like

BUT

there are more than one external files linked.

SO

Powerpoint only recognizes one as being ā€œopenā€ at a time and tries to ā€œopenā€ the other when it encounters an externa link pointing to a new source. To which is runs into the error ā€œcanā€™t open files with the same name.ā€ Because Iā€™ve already got it open you dumb machine.

How do I get Powerpoint to recognize all the workbooks it needs are already open?

I THINK Iā€™ve seen VBA where you create an excel workbook instance, open the files in the backgroundā€¦

Has anybody encountered this? Donā€™t worry about the differences between powerpoint and excelā€¦I can work through them.

My first idea is to loop through each linked object in Powerpointā€™s VBA and ā€œupdateā€ its sourceName with its original name to force a link refresh on 1 external link at a time. That way I can use VBA to switch focus to the matching excel workbook AS its updating

2

Who is this PowerPoint Presentation for?
Do they need all that info in a PowerPoint Presentation?
Have they seen the memo about the new cover page?

Better idea might be:

  1. Create the slides in an Excel file, where the links are easily updated (I hope).
  2. Make a PDF of the tabs you want to show (ā€œsave asā€ then file type = ā€œPDFā€).
  3. Send the PDF to whoever cares.
  4. Sit on your porch the rest of the day.
2 Likes
3

I tried to tell them about the TPS reports.

The way they do reporting here is one slide deck holds 10 reports. Read into that as you must.

Everyone needs more porch time. Everyone.

4

found potentially relevant code on the interwebs

5

You need to go out fishing for the day, then bring in your catch and bone them at work.

6

Oh trust me, Iā€™ve tried this with mixed results

lol I think the auto admin just edited my post for being dirty?

Nothing wrong with boning at work

7

Hereā€™s what worked

Sub RefreshLinks()

'Declare PowerPoint Variables
Dim PPTSlide As Slide
Dim PPTShape As Shape

'Declare Excel Variables
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkBook2 As Excel.Workbook

'Create a new Excel Application, make it invisible, set the Excel Display alerts to False.
Set xlApp = New Excel.Application
    xlApp.Visible = False
    xlApp.DisplayAlerts = False

Set ExternalFile = "filepath1"
Set ExternalFile2 = "filepath2"

'This will open the file as read-only, and will not update the links in the Excel file.
Set xlWorkBook = xlApp.Workbooks.Open(ExternalFile, False, True)
Set xlWorkBook2 = xlApp.Workbooks.Open(ExternalFile2, False, True)

Set count = 0

'Loop through each slide in the Presentation.
For Each PPTSlide In ActivePresentation.Slides

    'Loop through Each Shape in the slide
    For Each PPTShape In PPTSlide.Shapes

        'If the Shape is a linked OLEObject.
        If PPTShape.Type = msoLinkedOLEObject Then
            
            'Get the Source File of the shape.
            SourceFile = PPTShape.LinkFormat.SourceFullName

            'We may need to parse the Source file because if it's linked to a chart, for example, we can get the following:
            'C:\Users\NAME\ExcelBook.xlsx!Chart_One!
            'We want it to look like the following:
            'C:\Users\NAME\ExcelBook.xlsx

            'This will parse the source file so that it only includes the file name.
            Position = InStr(1, SourceFile, "!", vbTextCompare)
            FileName = Left(SourceFile, Position - 1)

            If InStr(1, FileName, "filename1", vbTextCompare) > 0 Then
                xlWorkBook.Activate
                'Update the link
                PPTShape.LinkFormat.Update
                count = count + 1
            Else
                xlWorkBook2.Activate
                'Update the link
                PPTShape.LinkFormat.Update
                count = count + 1
            End If

            Debug.Print "Updated " & count & "/356"
        End If

    Next PPTShape
Next PPTSlide

'Close the workbook and release it from memory.
xlWorkBook.Close
xlWorkBook2.Close
Set xlWorkBook = Nothing
Set xlWorkBook2 = Nothin

'Close the Excel App & release it from memory
xlApp.Quit
Set xlApp = Nothing

End Sub

8

nevermindā€¦this did not work :frowning:

10

What failed?

I can try to help, but also ChatGPT is usually good at getting you 75% of the way there. Though Iā€™ve seen it fail at a task, on request try a more complicated solution which fails, make it more and more complicatedā€¦ to fail.

11

Running PPTShape.LinkFormat.Update. Powerpoint doesnā€™t recognize that the source files are already open.

Whichever application object that run Update is not aware that there are laready instances of the excel source files open.

Was able to just open the source files and run update in a reasonable amount of time. but still very slow

12

Hmm. Unfortunately Iā€™ve never done VBA in PP.

1 Like
13

Iā€™ve done VBA ā†’ Powerpoint stuff before, but I generally avoid linked reports, and not sure I even follow what youā€™re saying.

What does this mean? Like you opened them before hitting the update link button? And doing that is faster?

But if you do the same thing via VBA, it doesnā€™t know that the files are open?

1 Like
14

Another option is to have only one Excel file that is linked to the PPP, and maybe that one is linked to all the other files.

2 Likes
15

This is a good solution. Another weird solution might be to dump pictures into PP instead of links.

Iā€™m not a fan of links. Or how PP likes to reformat things for no reason.

2 Likes
16

If you go this route, my advice is to pony up the $20 for a GPT-4 subscription.

1 Like
17

yes good solutions all of these. Trouble is Iā€™m the new guy on the team and these are long-established processes. Eventually we will move these out of Excel and into something better like SQL/Tableau.

18

No thatā€™s the problem. So the PPT has charts which are linked objects pointing to 2 different excel spreadsheets. If you open those spreadsheets prior to clicking ā€˜update linksā€™ the whole process runs slow but smoothly.

If you try to get slick and open those spreadsheets using VBA inside a program that calls LinkFormat.Update, PPT does not recognize that these worksheets are open and exist.

Ultimately itā€™s a business as itā€™s been done situation. I was just maybe seing if I could find a slick answer

19

Yeah you could also create all the charts in Excel and then VBA script create the PPT to paste all charts as pictures. Great notion

20

I guess I donā€™t understand why doing things with VBA would be faster powerpoint doing it on its own.

Whatā€™s the speed advantage?

Is powerpoint repeatedly opening the same workbooks? Or something?

Would it be faster if you had a macro that just opened all the workbooks at once, and then hit the update links button, then closed them all at once?

21

Why does anyone else have to know?
You ā€œhave to work long nights to get this TPS report rightā€ according to your bosses, then you ask for the next day off, no PTO used. Meanwhile, you were done at 2 PM the previous day.

Honestly (or dishonestly), your job is to make your job more efficient, but not let anyone know, lest they ChatGPT your ass.