Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web query: Want to pause macro while Web query completes refreshin
I have built a workbook that uses Web queries to populate pages with
financial data. The data is then used to populate other worksheets which analyze the fundamentals of a company as a prospective investment. The Web queries refresh automatically when I change the ticker symbol in the cell in one worksheet which triggers the refresh process with the new ticker. This works beautifully when I do one ticker symbol at a time. However, I have automated the process and a macro has it read down a list of ticker symbols and retrieve the data. Unfortunately, the refresh process, triggered as each ticker symbol is read, is too slow; and the macro continues and posts the data which has not been updated. I've tried changing the Option in External Data Range Properties by unchecking the box labeled "Enable Background Refresh." But that does not do it. I've tried adding code to the macro that makes use of the AfterRefresh Event to intercept it and hold up the macro's processing until the refresh is complete. No luck. I'd be grateful for any help you can give me. Ellis Traub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web query: Want to pause macro while Web query completes refreshin
Try "Sleep"
Place at top of your Module (declare public or private depending on your needs)... Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Place in your code after query code line... Call Sleep (3000) '(<<< try various #s to get correct # for your needs) Hope this helps. -- Thx MSweetG222 "Ellis" wrote: I have built a workbook that uses Web queries to populate pages with financial data. The data is then used to populate other worksheets which analyze the fundamentals of a company as a prospective investment. The Web queries refresh automatically when I change the ticker symbol in the cell in one worksheet which triggers the refresh process with the new ticker. This works beautifully when I do one ticker symbol at a time. However, I have automated the process and a macro has it read down a list of ticker symbols and retrieve the data. Unfortunately, the refresh process, triggered as each ticker symbol is read, is too slow; and the macro continues and posts the data which has not been updated. I've tried changing the Option in External Data Range Properties by unchecking the box labeled "Enable Background Refresh." But that does not do it. I've tried adding code to the macro that makes use of the AfterRefresh Event to intercept it and hold up the macro's processing until the refresh is complete. No luck. I'd be grateful for any help you can give me. Ellis Traub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web query: Want to pause macro while Web query completes refre
Thanks. I'll try it; but I'm not sure the query continues to run while the
macro is. I did get another suggestion which I tried and it works just fine. It was: With Sheets("QtrData").QueryTables(1) .BackgroundQuery = False .Refresh End With -- Ellis Traub "MSweetG222" wrote: Try "Sleep" Place at top of your Module (declare public or private depending on your needs)... Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Place in your code after query code line... Call Sleep (3000) '(<<< try various #s to get correct # for your needs) Hope this helps. -- Thx MSweetG222 "Ellis" wrote: I have built a workbook that uses Web queries to populate pages with financial data. The data is then used to populate other worksheets which analyze the fundamentals of a company as a prospective investment. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web query: Want to pause macro while Web query completes refre
Thanks, Don, I think I have this one whipped. I appreciate your interest
-- Ellis Traub "Don Guillett" wrote: Post your url(s), code and sample data. You may be able to get in ONE fetch. You may want to goto the yahoo xltraders group where I (donaldb36) and others have free files. -- Don Guillett Microsoft MVP Excel SalesAid Software "Ellis" wrote in message ... I have built a workbook that uses Web queries to populate pages with financial data. The data is then used to populate other worksheets which analyze the fundamentals of a company as a prospective investment. The Web queries refresh automatically when I change the ticker symbol in the cell in one worksheet which triggers the refresh process with the new ticker. This works beautifully when I do one ticker symbol at a time. However, I have automated the process and a macro has it read down a list of ticker symbols and retrieve the data. Unfortunately, the refresh process, triggered as each ticker symbol is read, is too slow; and the macro continues and posts the data which has not been updated. I've tried changing the Option in External Data Range Properties by unchecking the box labeled "Enable Background Refresh." But that does not do it. I've tried adding code to the macro that makes use of the AfterRefresh Event to intercept it and hold up the macro's processing until the refresh is complete. No luck. I'd be grateful for any help you can give me. Ellis Traub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use MS Query to query data within the current work book | Excel Discussion (Misc queries) | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
another macro query - deleting a worksheet within a query | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) |