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
Re: Refresh db connections in embedded excel objects from powerpoint macro
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.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Refresh db connections in embedded excel objects from powerpoint macro
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.
Re: Refresh db connections in embedded excel objects from powerpoint macro
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.
Reply With Quote
Resolved", if the query is solved