Clever Geek Handbook

Powerpoint VBA refreshes links to excel files opened by someone else without warning

I have a powerpoint presentation that links to several large excel spreasheets. I want to update links to update my presentation as a whole. However, if I'm in Update All Link

, my PowerPoint crashes, probably because the associated excel files are large. So I'll have to manually update each link so that it doesn't crash.

This macro below will loop each linked object and update its link, however if the file is currently open it will throw a warning if I want to cancel, open read-only or notify. Display warnings set to false will have nothing against it.

Sub linkupdate()
    Application.DisplayAlerts = False 
    Dim osld As Slide
    Dim oshp As Shape
    For Each osld In ActivePresentation.Slides
        osld.Select
        For Each oshp In osld.Shapes
            If oshp.Type = msoLinkedOLEObject Then
                oshp.Select
                oshp.LinkFormat.Update
            End If
        Next oshp
    Next osld
    Application.DisplayAlerts = True
End Sub

I found that powerpoint opens an invisible excel app underneath to refresh the links, however I was unable to grab this open instance with vba and tell it to open read-only links.

I wish, however, this does not happen if the file is opened by someone else, but I want it to automatically open the file for update as read-only.

+3


source to share


1 answer


Looking at your code, I am assuming this is all done from within the PowerPoint VBA editor . With that said, let me explain why you are getting the popup from within Excel. When you wrote this line of code:

Application.DisplayAlerts = False

You mean the PowerPoint application and you need to turn to the Excel application as I am assuming the popup is happening there. To fix this, we need to create a link to the Excel application. If I add the following code, we can turn off the displays for the Excel application :

'Declare Excel Variables
Dim xlApp As Excel.Application
Dim xlWorkBook 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

Now that we have processed the popup, let's fix the problem with your link. To fix the link issue, we will open the Excel workbook referenced by the link in the background . This way, the links are updated faster. If we don't open the workbook, I had a case where the link update process could easily take over 5 minutes.

However, getting the correct filename can be a bit tricky if the object is, for example, a linked diagram. We need to parse the file so that it removes the sheet name and the chart name. Here's how we do it:



'Get the Source File of the shape.
 SourceFile = PPTShape.LinkFormat.SourceFullName

'We may need to parse the Source file because if it 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)

Next, we want to open the book, update the link, and then close the book. So, we'll add this section of code:

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

    'Update the link
    PPTShape.LinkFormat.Update

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

Overall, this is what your code would look like to update the references of all the various related OLEObjects.

Sub UpdateLink()

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

    'Declare Excel Variables
    Dim xlApp As Excel.Application
    Dim xlWorkBook 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

    '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 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)

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

                    'Update the link
                    PPTShape.LinkFormat.Update

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

            End If

        Next PPTShape
    Next PPTSlide

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

End Sub

Also be careful when pasting objects into your PowerPoint slide, if you use VBA to do this, sometimes it can insert link information incorrectly! When you go to update links, nothing changes because the link is incorrect and does not actually link to the element you want to update.

0


source






More articles:


All Articles