Refresh db connections in embedded excel objects from powerpoint macro

I am trying to write a macro that will run from powerpoint and find embedded excel workbooks and then refresh the data connection to a database that is in that workbook. I first tried to execute a refreshall on the workbook. Then I tried to get it to run an excel macro saved in the workbook object. Here is the code I have tried without success, the errors are listed with the code. This is for Office 2003 on Windows XP.

Code:

---

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 = oShp.OLEFormat.Object.Application
        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

Here is a sample mdb file that can be used to make testing easier. It includes an update query to change the table content. I set up an ODBC DSN to c:\dummy\dummy.mdb for the test.

Erick

Is there not anyone who will take a crack at this? Was it something I said? Did I post it incorrectly?

Quote:

---

Did I post it incorrectly?

---

post is fine

i don't have powerpoint so hard to pick
xl.Application.Run "flash"
does work to run a macro in excel active workbook
macro must be public in a module

Quote:

---

xlapp.Application.Run "refresh_data"

---

as xlapp is an application object
xlapp.Run "refresh_data" should be the same thing


i did a post recently in this forum about how to run macros in sheet codepanes
http://www.vbforums.com/showthread.p...ght=run+macros

Quick question...

Is the excel file 'linked' or pasted as on object?

It is pasted as an embedded workbook object. I am sure the public module is the root cause, I am off to learn how to do that now. Ideally though I would like to be able to execute the refreshnow function directly and avoid running nested excel macro. Maybe the public module will enable that also.

Everyone thank you very much for the nudge in the right direction. The public variable definition gave me the right environment and then I found code to cycle through refreshing the querytables. Do not know why the refreshall did not work.

Here is my current working code.

Code:

---

Public oPres As Object
Public oSld As Slide
Public oShp As PowerPoint.Shape
Public oxlapp As Excel.Application
Public oxlbook As Excel.Workbook
Public oxlsheet As Excel.Worksheet
Public oxlqt As Excel.QueryTable

Public Sub update_xl()

Set oPres = ActivePresentation

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

    End With

End Sub

Public Sub xlupdate(oSlideOrMaster As Object)
   
    For Each oShp In oSlideOrMaster.Shapes
        If oShp.Type = msoEmbeddedOLEObject Then
        Set oxlapp = oShp.OLEFormat.Object.Application
        Set oxlbook = oShp.OLEFormat.Object
        For Each oxlsheet In oxlbook.Worksheets
            For Each oxlqt In oxlsheet.QueryTables
                oxlqt.Refresh BackgroundQuery:=False
            Next oxlqt
        Next oxlsheet

'        oxlapp.ActiveWorkbook.RefreshAll
               
        End If

    Set oxlqt = Nothing
    Set oxlsheet = Nothing
    Set oxlbook = Nothing
    Set oxlapp = Nothing
       
    Next oShp

End Sub

---

Now to test it with real data and many pages to determine the performance hit.

Thanks again.

just wanted to say thank you for this code. Worked perfectly in office 2010.

Zane