Need to disable a prompt in Excel when trying to programatically update links in a PPT Presentation

[shysue25]

I have code that updates graphs in PPT that are linked to data in
Excel. In my code I first open the Excel file that has the linked data
and then open the PowerPoint presentation. Then I have code that
updates the links. See the code below:

Public Sub UpdatePPTLinks(ByVal sPPTFile As String, ByVal sEXLFile As
String)

On Error Resume Next

'EXCEL

ConnectToExcel

'Opens the specified Excel file

Call OpenWorkBook(sEXLFile)

Set objExWB = goXLApp.ActiveWorkbook

'POWERPOINT

'Looks for a running instance of PowerPoint

Set objPPT = GetObject(, "PowerPoint.Application")


If objPPT Is Nothing Then

'Create a PowerPoint instance

Set objPPT = CreateObject("PowerPoint.Application")

If objPPT Is Nothing Then

MsgBox "PowerPoint is not Installed on your System!", vbCritical,
POWERVIEW_TITLE

Exit Sub

End If

objPPT.Visible = True

End If

'Opens the specified PowerPoint file

With objPPT

Set objPresentation = objPPT.Presentations.Open(sPPTFile)

End With



For Each objSlide In objPresentation.Slides

For Each objShape In objSlide.Shapes

If objShape.Type = 7 Then

Set objGraph = objShape.OLEFormat.object

Set objExWB = goXLApp.ActiveWorkbook

If objGraph.Application.HasLinks Then

objGraph.Application.Update

objGraph.Close

End If

End If

Next objShape

Next objSlide



'Shut down Excel

objExWB.Close SaveChanges:=False

Set objExWB = Nothing


'Shut down PowerPoint

objPresentation.save

objPresentation.Close

If objPPT.Presentations.Count = 0 Then objPPT.Quit

Set objPresentation = Nothing

Set objPPT = Nothing


End Sub



However, when the program tries to run through the following line:

Set objGraph = objShape.OLEFormat.object

for some graphs, Excel tries to re-open the Excel file that has the
linked information in it (even though it is already open). I think
this is because it the graph has links that are set to be updated
automatically. I tried changing the links to be set to manual
updating, but then the code doesn't work in updating the links.

When Excel tries to open the workbook again I get the following
message, with the option of clicking YES or NO:

" filename.xls is already open. Reopening will cause any changes you
made to be discarded. Do you want to reopen filename.xls?"

This message pops up for a lot of graphs in the presentation while the
program is running. When I click "No" each time, the graphs update
fine. My problem is that I cannot have this message popping up during
this process which is supposed to run without human intervention.

Does anyone know of anyway to disable this message in Excel?

Or of anything I can change in the code or in my presentation that
will make it not want to reopen up the Excel file again?

SOMEBODY PLEASE HELP ME! - This is at a critical point now!

 

[Shyam Pillai]

Excel will need to open the file if it needs to update the information. Are
the excel workbooks in shared or exclusive mode?

 

[Shyam Pillai]

Also, is there any particular reason, you are opening the excel file in your
code?

 

[shysue25]

I open the workbook like this:

goXLApp.Workbooks.Open sRptFileName

If I don't open the workbook, my graphs get updated but my Excel charts
within PowerPoint do not get updated. When I have the Excel file open,
then when this line is executed:

Set objGraph = objShape.OLEFormat.object

My Excel charts automatically get updated.

I guess my problem would be solved if you know of a way (diff code) to
update the Excel objects in the PPT presentation without opening the
Excel file that's linked to them.

Please help me.