Which runs first, Workbook_Open() or external query refresh?

[[email protected]]

I want to process some data when the user opens a spreadsheet, but not
until an external Access query has been refreshed.

"Refresh data on file open..." has been checked in the data range
properties. But I think my Workbook_Open() code is running before auto
refresh happens.

Where is the best place to put code to ensure that it runs after the
external MS-Query has been updated?

As a last resort, I could have the user press a button after all data
has been refreshed, but that is messy.

Thanks in advance....
...df

[K Dales]

I did a quick test with a MsgBox in the Worksheet_Open event; this message
came up before the message "This book contains automatic queries..." so it
seems that Worksheet_Open comes before any queries refresh.
--
- K Dales


"[email protected]" wrote:

> I want to process some data when the user opens a spreadsheet, but not
> until an external Access query has been refreshed.
>
> "Refresh data on file open..." has been checked in the data range
> properties. But I think my Workbook_Open() code is running before auto
> refresh happens.
>
> Where is the best place to put code to ensure that it runs after the
> external MS-Query has been updated?
>
> As a last resort, I could have the user press a button after all data
> has been refreshed, but that is messy.
>
> Thanks in advance....
> ...df
>
>

[Sean Connolly]

Hi,

When you think about it, probably makes sense that the workbook needs to
open before the query can be refreshed <g>.

That said, QueryTable objects do have events that can be intercepted and
coded - specifically AfterRefresh and BeforeRefresh. I'd say that you're
looking for AfterRefresh. There is a catch though. The Excel VBA help topic
'Using Events with the QueryTable Object' states ...

"Before you can use events with the QueryTable object, you must first create
a new class module and declare a QueryTable object with events."

Could I suggest (respectfully of course <g>) that a read through this topic
might be a good place to start. Please post back if you need something
further.

HTH, Cheers and Regards, Sean.

"[email protected]" wrote:

> I want to process some data when the user opens a spreadsheet, but not
> until an external Access query has been refreshed.
>
> "Refresh data on file open..." has been checked in the data range
> properties. But I think my Workbook_Open() code is running before auto
> refresh happens.
>
> Where is the best place to put code to ensure that it runs after the
> external MS-Query has been updated?
>
> As a last resort, I could have the user press a button after all data
> has been refreshed, but that is messy.
>
> Thanks in advance....
> ...df
>
>

[Sean Connolly]

As Tom Ogilvy also replied under a different thread, the following KB article
is probably also very useful here.

http://support.microsoft.com/support.../q182/7/35.asp
XL97: How to Use the Query Before and AfterRefresh Events

Regards, Sean.

"[email protected]" wrote:

> I want to process some data when the user opens a spreadsheet, but not
> until an external Access query has been refreshed.
>
> "Refresh data on file open..." has been checked in the data range
> properties. But I think my Workbook_Open() code is running before auto
> refresh happens.
>
> Where is the best place to put code to ensure that it runs after the
> external MS-Query has been updated?
>
> As a last resort, I could have the user press a button after all data
> has been refreshed, but that is messy.
>
> Thanks in advance....
> ...df
>
>

[[email protected]]

Thanks guys for the replies.

After posting that message, I had an idea, why not turn off "Refresh on
open" for the data range, and instead call a refresh function right
FROM workbook_open() - that way I can refresh first, then process away.
Calling ActiveWorkbook.RefreshAll from workbook_open() seems to do the
trick.

Thanks again.
....df