Msgbox appears to early

[F Jones]

Hello

I have a refresh all button that runs the following (rather simple) macro:

Sub Refresh_all_Button_Click()
ActiveWorkbook.RefreshAll
MsgBox ("Refresh is complete")
Range("k2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
End Sub

It works fine but the message box pops up too early. the refreshing is still
going on and i can see the little globe in the bottom left corner. how can i
make it pop up after the refresh?

Thanks for your help.

 

[Bob Phillips]

Do you have BackgroundQuery set to true for the things being refreshed?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[F Jones]

Hello

How do i do that? is it something to do with the OLAP data?

--
F Jones

Do you have BackgroundQuery set to true for the things being refreshed?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

It would be in the query definition although OLAP is not my field.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hello

How do i do that? is it something to do with the OLAP data?

 

[F Jones]

Hello

I have a report which looks up data from two SQL Cubes and i run the macro
to refresh all. I want the message box to appear after it has finished.

i found some notes on Macro suspending background queries but i don't think
that is what i am looking for.

Dim qTbl As QueryTable

Sub CreateQueryTable()

'Create a new query table.
Set qTbl = ActiveSheet.QueryTables.Add("ODBC;dsn=Northwind", _
ActiveSheet.Range("A1"), Sql:="Select * from Customers")

'Refresh the query table.
qTbl.Refresh BackgroundQuery:=False

'Display the number of rows returned to the query table.
MsgBox qTbl.ResultRange.Rows.Count & " Rows Returned"

End Sub

 

[F Jones]

"Enable background refresh" is ticked in the properties of both of the
connections. its greyed out so i can't change this.

 

[Don Guillett]

Maybe

Sub Refresh_all_Button_Click()
application.enableevents=false
ActiveWorkbook.RefreshAll
application.enableevents=true

Range("k2")=date
MsgBox "Refresh is complete"
End Sub

 

[F Jones]

This doesn't work either. The message pops up after a minute and the refresh
takes closer to three minutes.

Any other ideas? I think it might be something to do with the the OLAP
queries that the report is based on. I need to access these to perform the
refresh all.

Thanks for your help
--
F Jones

Maybe

Sub Refresh_all_Button_Click()
application.enableevents=false
ActiveWorkbook.RefreshAll
application.enableevents=true

Range("k2")=date
MsgBox "Refresh is complete"
End Sub