From here: https://superuser.com/questions/579900/why-cant-excel-open-a-file-when-run-from-task-scheduler
1. Open Component Services (Start -> Run, type in dcomcnfg)
2. Drill down to Component Services -> Computers -> My Computer and click on DCOM Config
3. Right-click on Microsoft Excel Application and choose Properties
4. In the Identity tab select This User and enter the ID and password of an interactive user account (domain or local) and click Ok
Keeping it as the interactive user or the launching doesn't work with the task scheduler unfortunately, even when setting the task up to run under an account that has admin access to the machine.
From here: https://forums.asp.net/t/1740537.aspx?Microsoft+Excel+cannot+access+the+file+is+solved+by+creating+a+folder
These are the steps for resolving the issue. If I ran Task Scheduler on demand it worked, but it would not interact with Excel of it was Triggered by the timer!
This will open Component Services and navigate to Console Root
\Component Services\Computers\My Computer\DCOM Config\Microsoft Excel Application
Right click Microsoft Excel Application and select Properties
Click on Identity Tab and check selection.
It works for me when select option - The interactive user
This is a brilliant service! It's not 100% perfect but pretty cool and saved me hours on manually typing!