leishtheman
New Member
- Joined
- Oct 1, 2007
- Messages
- 32
Hello
I have an issue I've been unable to resolve after lots of searching which I'd like to request help with if that's ok.
I have an excel workbook set up to monitor stock (equities) prices using power query connections to yahoo finance. The file refreshes the connections > looks for major falls in stock prices during the previous day > then generates email alerts listing all stocks where the daily fall has been greater than 10%. The script works correctly when I open the workbook and run it manually. My problem is that I don't want to have to run it manually, and when I automate the running of it it doesn't work correctly:
I run the the script off the workbook open event and open the workbook using windows task scheduler upon startup. When I do this the workbook doesn't open 'fully' in the sense of you being able to see the ribbon and worksheets etc, but rather it just displays the small 'loading' box you get when Excel is firing up. The workbook open event triggers nevertheless and it starts running the code, with it opening 'fully' once it has finished running the code. The problem is that the power query connection refreshes don't appear to be visible until the workbook is 'fully' open, meaning it generates the email alert using old data whereas I need it to generate the emails using the newly refreshed data.
I suppose my question in a nutshell is either - a) how do I get a workbook to 'fully' open before the workbook open event triggers, or b) how do I get power query connections to refresh fully in the background so I can pick up the refreshed data?
Many thanks in advance,
Andy Leishman.
I have an issue I've been unable to resolve after lots of searching which I'd like to request help with if that's ok.
I have an excel workbook set up to monitor stock (equities) prices using power query connections to yahoo finance. The file refreshes the connections > looks for major falls in stock prices during the previous day > then generates email alerts listing all stocks where the daily fall has been greater than 10%. The script works correctly when I open the workbook and run it manually. My problem is that I don't want to have to run it manually, and when I automate the running of it it doesn't work correctly:
I run the the script off the workbook open event and open the workbook using windows task scheduler upon startup. When I do this the workbook doesn't open 'fully' in the sense of you being able to see the ribbon and worksheets etc, but rather it just displays the small 'loading' box you get when Excel is firing up. The workbook open event triggers nevertheless and it starts running the code, with it opening 'fully' once it has finished running the code. The problem is that the power query connection refreshes don't appear to be visible until the workbook is 'fully' open, meaning it generates the email alert using old data whereas I need it to generate the emails using the newly refreshed data.
I suppose my question in a nutshell is either - a) how do I get a workbook to 'fully' open before the workbook open event triggers, or b) how do I get power query connections to refresh fully in the background so I can pick up the refreshed data?
Many thanks in advance,
Andy Leishman.
