Trying to create a automation flow for a scheduled refresh where data is getting fetched from Sharepoint Lists and later unpivoted in Power Query (which is working fine).
In order to refresh the excel file, shell script opens the excel file which stays open for some time during which data gets refreshed and before closing the file below MACRO script auto saves the excel file.
The problem is the below message box needs responding to when script opens the file
Sendkey works fine to tackle the above. However, there is a problem
The code invokes a method using the wscript.shell object and works fine when we are logged into the system. Unfortunately we need to this run on an unattended system. However due to the security policy the screen lock kicks in. When that happens it is no longer possible to invoke the wscript.shell object. Below link.
stackoverflow.com
However, I have been told the commands to open the Excel file and then do a graceful shutdown using macro do not have this limitation
Can you please guide how to resolve this, perhaps any changes in Macros ? Also to see what is causing the error message to appear. If that can be removed the automation should work fine I guess.
I am beginner trying to find the solution.
Using excel 365 for enterprise
Thanks
In order to refresh the excel file, shell script opens the excel file which stays open for some time during which data gets refreshed and before closing the file below MACRO script auto saves the excel file.
The problem is the below message box needs responding to when script opens the file
Sendkey works fine to tackle the above. However, there is a problem
The code invokes a method using the wscript.shell object and works fine when we are logged into the system. Unfortunately we need to this run on an unattended system. However due to the security policy the screen lock kicks in. When that happens it is no longer possible to invoke the wscript.shell object. Below link.
VBScript not working when PC is locked
I'm running a VBScript that communicates to an exe file in Windows 7. The VBScript works great! The issues I have, is that once the PC has been in locked, goes to sleep or hiberation the VBS...
However, I have been told the commands to open the Excel file and then do a graceful shutdown using macro do not have this limitation
Can you please guide how to resolve this, perhaps any changes in Macros ? Also to see what is causing the error message to appear. If that can be removed the automation should work fine I guess.
I am beginner trying to find the solution.
Using excel 365 for enterprise
Thanks
