Powerpoint, Refresh embedded Excel workbooks with Dynamics CRM querys

Question

Hi,

As MS decided to drop the macro-recording option from PowerPoint managing simple tasks became an utter pain in PowerPoint. I'm sure I could have solved this problem if I could have recorded a macro and then made some editing to the code in VBA, but I don't have the skills to start from scratch.

This is what I'm trying to achieve:

I have several Powerpoint slides. In the slides there are embedded excel-files containing calculations based on queries from Dynamics CRM. The slides are now working fine but I would like to add a Button/macro so that the users can refresh the data in the embedded excel-files with a click of of a button instead of going through all the slides and workbooks and refreshing them one at a time.

So as I see it I would need a little help/samples on how to:

Cycle through the slides in an active presentation, check if the page contains an embedded excel-file, if it does, then refresh the CRM-query.

Any help, links, or samples where similar things have been done would be greatly appreciated.

Best regards,

Janne

Answer 1

I'm afraid I've no experience/knowledge of CRM, so I don't know what is/isn't possible there.

It may be that the embedded Excel objects need to be activated within the PPT, then some further automation needs to be done on them to force the update.

Answer 2

Hi Steve and thank you for your answer,

The code is a little bit as thought it should be but I'm sorry to say that it doesn't work, it runs fine but nothing happens. I'm sure that the problem isn't with the code in itself but I failed to mention that the connection is to CRM Online and I'm not sure that it is possible to perform an data update through a vba macro, or is it?

Best regards,

Janne

Answer 3

Depending on the contents of the workbooks and how they're updated themselves, this may or may not help, but to at least force an update of the content from the PPT side:

Sub TryThis()

Dim osh As Shape

Dim osl As Slide

For Each osl In ActivePresentation.Slides

For Each osh In osl.Shapes

If osh.Type = msoLinkedOLEObject Then

osh.LinkFormat.Update

End If

Next

Next

End Sub

That'll update all the linked objects, not any one particular type; other than eating a bit of time, it shouldn't matter, but if so, it could be refined with further tests.