Hi Everyone, i'm new to this forum and would really appreciate some help.
I have developed a spreadsheet that tracks crypto currency prices and volumes via a generated web query with API's and refreshes every 2 - 3 minutes. I then have a VBA code that detects a change in a cell value (after the API data is pulled in) and then executes some conditions and gives me advice on whether to buy or sell.
I have this running 24hours a day on a friends server. The idea is that it constantly refreshes and when certain conditions are met, it gives me advice via email.
In general everything works well, but sometimes excel does not get the information it requires from the API and then an error message pops up with something like: [Datasource.error] unable to retrieve information from: XXX (Bad gateway)
The problem is that the sheet then freezes until the pop up is clicked and the sheet can proceed. This happens on average twice a day and is very frustrating of course as it is supposed to work in the background without me checking up on it constantly.
I am not a programmer but I have googled and tried various things already:
The last option works best so far and in testing works flawlessly. My problem however is that sometimes when I check on the sheet (via remote connection) there are no popups but excel is frozen trying to retrieve the API (remains in the :Running background query mode) and is completely unresponsive. I then have to force it closed with task manager and open the sheet again.
I can't for the life of me figure out why it does this. Is there anybody that can perhaps help with a better solution? or to force excel to work through those pop ups? I don't mind if it misses an update every now and again. It can just catch it in the next one. Main thing is that the sheet must continue to run constantly.
Thanks in advance!
I have developed a spreadsheet that tracks crypto currency prices and volumes via a generated web query with API's and refreshes every 2 - 3 minutes. I then have a VBA code that detects a change in a cell value (after the API data is pulled in) and then executes some conditions and gives me advice on whether to buy or sell.
I have this running 24hours a day on a friends server. The idea is that it constantly refreshes and when certain conditions are met, it gives me advice via email.
In general everything works well, but sometimes excel does not get the information it requires from the API and then an error message pops up with something like: [Datasource.error] unable to retrieve information from: XXX (Bad gateway)
The problem is that the sheet then freezes until the pop up is clicked and the sheet can proceed. This happens on average twice a day and is very frustrating of course as it is supposed to work in the background without me checking up on it constantly.
I am not a programmer but I have googled and tried various things already:
- Modified my VBA code to include Displayalerts = false (not exact code) but the error is not in the VBA code, it is prior to the code running so this does not work.
- I have tried to suppress these popups by changing the settings but can't find a setting that does this.
- I have written some code with autohotkeys that maximises the main excel window every hour, if the error pop up exists, it clicks enter and loops until they are all cleared and then minimises excel again.
The last option works best so far and in testing works flawlessly. My problem however is that sometimes when I check on the sheet (via remote connection) there are no popups but excel is frozen trying to retrieve the API (remains in the :Running background query mode) and is completely unresponsive. I then have to force it closed with task manager and open the sheet again.
I can't for the life of me figure out why it does this. Is there anybody that can perhaps help with a better solution? or to force excel to work through those pop ups? I don't mind if it misses an update every now and again. It can just catch it in the next one. Main thing is that the sheet must continue to run constantly.
Thanks in advance!
