refresh data in embedded excel objects

[dougerickson]

I have a powerpoint file that uses embedded excel workbooks that are
connected to access databases through an ODBC DSN connection. What I
am trying to do is create a powerpoint vba macro that will go to each
of the embedded workbooks in the file and refresh the data connection
in the workbook to get updated data. The reason that I am using
embedded workbooks instead of OLE links is to have access to the data
and the charts directly from the ppt file, instead of having to send
separate ppt and xls files.

So here is the code that I have so far. It can find each of the
objects and set the value of
cell A1.

Sub update_xl()

Dim oPres As Object
Dim oSld As Slide
Set oPres = ActivePresentation

With oPres
For Each oSld In .Slides
Call xlupdate(oSld)
Next oSld

End With

End Sub
Sub xlupdate(oSlideOrMaster As Object)
Dim oShp As PowerPoint.Shape

For Each oShp In oSlideOrMaster.Shapes
Dim oxl As Excel.Workbook
Dim xlsheet As Excel.Worksheet
If oShp.Type = msoEmbeddedOLEObject Then
Set oxl = oShp.OLEFormat.Object
Set xlsheet = oxl.Worksheets(1)
xlsheet.Cells(1, 1) = 20
End If

Set xlsheet = Nothing
Set xchart = Nothing
Set oxl = Nothing

Next oShp

End Sub


My hope was to be able to change the line 'xlsheet.cells(1,1) = 20' to
'oxl.RefreshAll' to refresh the data connection in the embedded
workbook object. Unfortunately that errors out. I have looked at
trying to set an excel workbook event based on the edit of a cell. It
seems I need to create a class module to enable withevents, but I have
not been able to get that to work either. The other approach would be
to discover the query table on each worksheet and then refresh the
querytable.

Unfortunately I can barely spell VBA, so I am fumbling my way around
in the dark here. Any help is appreciated.

Erick

 

[dougerickson]

Here is round 2.

I have tried the code below to get a refreshall to work. Then I tried
get the code to call an excel macro that is stored in the excel
object. Here is the code and the errors.

Sub xlupdate(oSlideOrMaster As Object)
Dim oShp As PowerPoint.Shape
Dim oxl As Excel.Workbook
Dim xlapp As Excel.Application
Dim xlsheet As Excel.Worksheet


For Each oShp In oSlideOrMaster.Shapes
If oShp.Type = msoEmbeddedOLEObject Then
Set oxl = oShp.OLEFormat.Object
Set xlapp = oxl.Parent
Set xlsheet = oxl.Worksheets(1)

' xlapp.ActiveWorkbook.RefreshAll
' The above line returns an error 91, Object Variable or With
block variable not set

' xlapp.Application.Run "refresh_data"
' The above line returns an error 1004, macro 'refresh_data'
cannot be found

' xlapp.Run "Worksheet in test.ppt (Compatibility Mode)'!
refresh_data"
' The above line returns an error 1004, cannot find the
worksheet

Set xlapp = Nothing
Set oxl = Nothing
Set xlsheet = Nothing

End If

Next oShp



End Sub


Thanks

Erick