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

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


" 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

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.

" 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

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.

" 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

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