If you schedule a refresh, the add-in creates an entry in the Windows Task Scheduler. Open the Task Scheduler (not Task Manager) from the Windows start menu to follow along…
You will see a number of tasks that start with AnalyticsEdgeRefresh##. Select one and click Properties on the right side.
Go to the Actions tab and click the Edit… button. You will see a Program/Script entry like (get a copy of this):
with arguments like (copy this too):
"C:\Program Files\Microsoft Office\root\Office16\Excel.exe" "C:\Users\Mike\...\avgposition-2-0.xlsm" 5
The program reads the arguments and adds them to a queue file then starts the AE Schedule Manager if it is not already running. The AE Schedule Manager reads the queue file and processes the top entry. When the entry finishes processing (error, success or timeout), it removes the entry and re-reads the file for the next ‘top’ entry. This prevents more than one workbook from being refreshed at the same time (lots of bad things can happen).
To process an entry, the AE Schedule Manager runs a command line to start Excel using the path provided in the queue, includes a command line parameter /erefresh and passes along the path to the file as an argument so Excel will open the file. e.g.:
"C:\Program Files\Microsoft Office\root\Office16\Excel.exe" /erefresh "C:\Users\Mike\..\avgposition-2-0.xlsm"
The add-in detects the /erefresh and executes a refresh all, then closes Excel with an error status code.
The AE Schedule Manager waits for the Excel process to end (or times out using the number at the end of the parameters passed in the queue), then logs the status and moves on to the next entry.
Things That Can Go Wrong
– you reinstall the add-in/Excel and the AnalyticsEdgeRefresh.exe no longer exists in the location pointed to by the task entry.
– you reinstall/upgrade Excel, and Excel is no longer in the location pointed to in the task entry.- you move the file itself, and the task entry is wrong
– the file is on a removable/mounted network drive which is not available from the programmatic environment when the scheduler runs. (network drives will auto-remount when a user interacts with the environment). The program attempts to remount the drive but it is hit and miss.
– the file is on a path that cannot be accessed from the command line. Note: even with all the advances, Windows commands can still only process file paths of about 280 characters, so avoid really long paths.
THINGS FOR YOU TO TRY
Get the path parameters from one of your task entries described above, then start CMD (Command Prompt) from the Windows Start button.
Run a command as described above /erefresh
Excel should open with the file specified. The AE ribbon should NOT appear (this is by design — it is suppressed when /erefresh is used to prevent user interaction).
The queries and macros should refresh and then Excel should close all by itself.