AUTO_OPEN and Automatic Data Refresh
 

We have a workbook with queries bound to a few of the sheets. Once the
queries have automatically refreshed, I'd like a macro to run. AUTO_OPEN
macros appear to run before the data is refreshed. Any thoughts on how to
accomplish this?

I thought I might have the AUTO_OPEN macro check to see if the queries were
done and remain in a wait loop until the queries finished but I can't find
the dbengine in the object model and I'm not sure if it supports a "busy"
status, anyway. I thought I might just probe a known cell to see if the
queries were complete but that seems like an awfully awkward way to do this.

So, I'm looking for ideas. Any suggestions would be appreciated.

AUTO_OPEN and Automatic Data Refresh
 

How about this approach?

Turn off the automatic data refresh.

Then, the auto_open (or Workbook_Open) code can refresh each of the
PivotCaches and QueryTables. This way you know when the last refresh
completes!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article , says...
We have a workbook with queries bound to a few of the sheets. Once the
queries have automatically refreshed, I'd like a macro to run. AUTO_OPEN
macros appear to run before the data is refreshed. Any thoughts on how to
accomplish this?

I thought I might have the AUTO_OPEN macro check to see if the queries were
done and remain in a wait loop until the queries finished but I can't find
the dbengine in the object model and I'm not sure if it supports a "busy"
status, anyway. I thought I might just probe a known cell to see if the
queries were complete but that seems like an awfully awkward way to do this.

So, I'm looking for ideas. Any suggestions would be appreciated.

AUTO_OPEN and Automatic Data Refresh
 

That sounds workable but it leads to a follow-up question:

Where in the object model would I start looking for a handle on the query?
So far, I haven't located a way to get at them in code. I have a couple of
books here but so far haven't found references to queries.

"Tushar Mehta" <tm_200310@tushar_hyphen_mehta_dot_see_oh_em wrote in
message om...
How about this approach?

Turn off the automatic data refresh.

Then, the auto_open (or Workbook_Open) code can refresh each of the
PivotCaches and QueryTables. This way you know when the last refresh
completes!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article , says...
We have a workbook with queries bound to a few of the sheets. Once the
queries have automatically refreshed, I'd like a macro to run.
AUTO_OPEN
macros appear to run before the data is refreshed. Any thoughts on how
to
accomplish this?

I thought I might have the AUTO_OPEN macro check to see if the queries
were
done and remain in a wait loop until the queries finished but I can't
find
the dbengine in the object model and I'm not sure if it supports a
"busy"
status, anyway. I thought I might just probe a known cell to see if the
queries were complete but that seems like an awfully awkward way to do
this.

So, I'm looking for ideas. Any suggestions would be appreciated.

AUTO_OPEN and Automatic Data Refresh
 

After further searching, it looks like I can get at them through:

activeworkbook.sheets(n).querytables(n)...

but if you have other suggestions (more direct, more reliable, faster,
cleaner or just different), I'd be happy to hear them.

In the meantime, this is looking good. Thanks for the suggestion.

"DH" wrote in message ...
That sounds workable but it leads to a follow-up question:

Where in the object model would I start looking for a handle on the query?
So far, I haven't located a way to get at them in code. I have a couple
of
books here but so far haven't found references to queries.

"Tushar Mehta" <tm_200310@tushar_hyphen_mehta_dot_see_oh_em wrote in
message om...
How about this approach?

Turn off the automatic data refresh.

Then, the auto_open (or Workbook_Open) code can refresh each of the
PivotCaches and QueryTables. This way you know when the last refresh
completes!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article , says...
We have a workbook with queries bound to a few of the sheets. Once
the
queries have automatically refreshed, I'd like a macro to run.
AUTO_OPEN
macros appear to run before the data is refreshed. Any thoughts on
how
to
accomplish this?

I thought I might have the AUTO_OPEN macro check to see if the queries
were
done and remain in a wait loop until the queries finished but I can't
find
the dbengine in the object model and I'm not sure if it supports a
"busy"
status, anyway. I thought I might just probe a known cell to see if
the
queries were complete but that seems like an awfully awkward way to do
this.

So, I'm looking for ideas. Any suggestions would be appreciated.

AUTO_OPEN and Automatic Data Refresh
 

QueryTables and PivotCaches should cover the ways in which to get data
into XL.

Glad to learn you liked the suggestion and it is working out. :)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , says...
After further searching, it looks like I can get at them through:

activeworkbook.sheets(n).querytables(n)...

but if you have other suggestions (more direct, more reliable, faster,
cleaner or just different), I'd be happy to hear them.

In the meantime, this is looking good. Thanks for the suggestion.

"DH" wrote in message ...
That sounds workable but it leads to a follow-up question:

Where in the object model would I start looking for a handle on the query?
So far, I haven't located a way to get at them in code. I have a couple
of
books here but so far haven't found references to queries.

"Tushar Mehta" <tm_200310@tushar_hyphen_mehta_dot_see_oh_em wrote in
message om...
How about this approach?

Turn off the automatic data refresh.

Then, the auto_open (or Workbook_Open) code can refresh each of the
PivotCaches and QueryTables. This way you know when the last refresh
completes!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article , says...
We have a workbook with queries bound to a few of the sheets. Once
the
queries have automatically refreshed, I'd like a macro to run.
AUTO_OPEN
macros appear to run before the data is refreshed. Any thoughts on
how
to
accomplish this?

I thought I might have the AUTO_OPEN macro check to see if the queries
were
done and remain in a wait loop until the queries finished but I can't
find
the dbengine in the object model and I'm not sure if it supports a
"busy"
status, anyway. I thought I might just probe a known cell to see if
the
queries were complete but that seems like an awfully awkward way to do
this.

So, I'm looking for ideas. Any suggestions would be appreciated.