Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic refresh of external data | Excel Worksheet Functions | |||
Automatic refresh of external data | Excel Discussion (Misc queries) | |||
Automatic refresh of external data | Links and Linking in Excel | |||
Timing of automatic query refresh and macro pivot table refresh | Excel Programming | |||
automatic refresh of formula that seeks data from an add-in | Excel Programming |