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
1 Attachment(s)
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.
Erick
Re: Refresh db connections in embedded excel objects from powerpoint macro
Is there not anyone who will take a crack at this? Was it something I said? Did I post it incorrectly?
Re: Refresh db connections in embedded excel objects from powerpoint macro
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
Re: Refresh db connections in embedded excel objects from powerpoint macro
Quick question...
Is the excel file 'linked' or pasted as on object?
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.
Thanks again.
Re: Refresh db connections in embedded excel objects from powerpoint macro
just wanted to say thank you for this code. Worked perfectly in office 2010.
Zane