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
automatically accepting read-only ok message when update OLE
-
- 2StarLounger
- Posts: 169
- Joined: 08 Jun 2010, 14:33
- Location: Massachusetts, USA
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: automatically accepting read-only ok message when update
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.
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
Hans
-
- 2StarLounger
- Posts: 169
- Joined: 08 Jun 2010, 14:33
- Location: Massachusetts, USA
Re: automatically accepting read-only ok message when update
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?
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: automatically accepting read-only ok message when update
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
Hans
-
- 2StarLounger
- Posts: 169
- Joined: 08 Jun 2010, 14:33
- Location: Massachusetts, USA
Re: automatically accepting read-only ok message when update
Thanks for all your help Hans! One of the workarounds in the link you sent should do the trick! :-)