automatically accepting read-only ok message when update OLE

Asher
2StarLounger
Posts: 169
Joined: 08 Jun 2010, 14:33
Location: Massachusetts, USA

automatically accepting read-only ok message when update OLE

Post by Asher »

Hello,

I have 2 Excel files. Each Excel file has a range I want added to a Powerpoint file. I am trying to add each them as an OLE so that when I open the file, all I have to do is update the links to the embedded ranges and all data will be up to date (each file gets updated weekly).

This seems to work for the most part, but I do run into one problem. Both of the Excel files are stored as "Read-Only Recommended", so when I try to update the OLE's in the Powerpoint, they get hung up on the "Open as read only?" question box. The messages don't pop up on screen for me to accept, I have to find them in task manager and select them for them to pop up, which won't be realistically usable to anyone else.

Is there a setting or code snippet I can use in the presentation to automatically accept the "Read-Only" message so when I try to update links it will just happen?

Thanks for your assistance,

Asher

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: automatically accepting read-only ok message when update

Post by HansV »

One way would be to make the workbooks shared, but I can't recommend that - shared workbooks are more likely to become corrupted than non-shared ones.

Using code, you could open the workbooks as read-only, then update the OLE objects. You'd have to set the linked objects to update manually - the automatic update process would cause the prompt.

Code: Select all

Sub UpdatOLELinks()
    ' Modify the paths and file names as needed
    Const strWorkbook1 = "C:\Excel\FirstWorkbook.xlsx"
    Const strWorkbook2 = "C:\Excel\SecondWorkbook.xlsx"
    Dim sld As Slide
    Dim shp As Shape
    Dim xlApp As Object
    Dim xlWbk1 As Object
    Dim xlWbk2 As Object
    Dim blnStart As Boolean
    Dim blnOpen1 As Boolean
    Dim blnOpen2 As Boolean
    On Error Resume Next
    ' Reference to Excel
    Set xlApp = GetObject(Class:="Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject(Class:="Excel.Application")
        If xlApp Is Nothing Then
            MsgBox "Can't start Excel!", vbExclamation
            Exit Sub
        End If
        blnStart = True
    End If
    ' Reference to first workbook
    Set xlWbk1 = xlApp.Workbooks(strWorkbook1)
    If xlWbk1 Is Nothing Then
        Set xlWbk1 = xlApp.Workbooks.Open(FileName:=strWorkbook1, ReadOnly:=True)
        If xlWbk1 Is Nothing Then
            MsgBox "Can't open workbook 1!", vbExclamation
            GoTo ExitHandler
        End If
        blnOpen1 = True
    End If
    ' Reference to second workbook
    Set xlWbk2 = xlApp.Workbooks(strWorkbook2)
    If xlWbk2 Is Nothing Then
        Set xlWbk2 = xlApp.Workbooks.Open(FileName:=strWorkbook2, ReadOnly:=True)
        If xlWbk2 Is Nothing Then
            MsgBox "Can't open workbook 2!", vbExclamation
            GoTo ExitHandler
        End If
        blnOpen2 = True
    End If
    On Error GoTo ErrHandler
    ' Update linked OLE objects
    For Each sld In ActivePresentation.Slides
        For Each shp In sld.Shapes
            If shp.Type = msoLinkedOLEObject Then
                shp.LinkFormat.Update
            End If
        Next shp
    Next sld

ExitHandler:
    ' Clean up
    On Error Resume Next
    If blnOpen1 Then
        xlWbk1.Close SaveChanges:=False
    End If
    If blnOpen2 Then
        xlWbk2.Close SaveChanges:=False
    End If
    If blnStart Then
        xlApp.Quit
    End If
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub
Best wishes,
Hans

Asher
2StarLounger
Posts: 169
Joined: 08 Jun 2010, 14:33
Location: Massachusetts, USA

Re: automatically accepting read-only ok message when update

Post by Asher »

Hans this works like a charm. Is there a way to add it to an "on open" event so it calls the macro when the .ppt is open?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: automatically accepting read-only ok message when update

Post by HansV »

Unfortunately, PowerPoint does not have the equivalent of Word's Document_Open event and Excel's Workbook_Open event. See How can I get my code to run automatically when a presentation opens? for some workarounds.
Best wishes,
Hans

Asher
2StarLounger
Posts: 169
Joined: 08 Jun 2010, 14:33
Location: Massachusetts, USA

Re: automatically accepting read-only ok message when update

Post by Asher »

Thanks for all your help Hans! One of the workarounds in the link you sent should do the trick! :-)