So this one is a little odd. We have an automation that opens an Excel file and refreshes the data in the file. The data is pulling from a SharePoint list that we have linked to a table. The automation works fine, but when it kicks off the refresh of the data, Excel asks for us to choose an account to log in with. We don't have to enter any credentials and there is only one account associated with this file.
The issue here is that the automation completely hangs when the popup comes up to choose a user to log in as. Because of this, I cannot use an image recognition automation or a mouse move to select the user account. This just causes the automation to hang until I get up in the am and go onto this laptop and select the user manually. Once I select the user manually, the automation continues where it left off, regardless of how long it is sitting on the screen with the popup to select the user.
Has anyone run into anything similar, or have a solution for this?
Issue with Excel macro
Moderators: Dorian (MJT support), JRL
Re: Issue with Excel macro
Windows security stops all script processing while that security window displays. Nothing running on that computer will be able to dismiss the window.
The solution I found requires a second computer and whatever vnc software you like. I like TightVNC. If you don't know VNC it allows remote access of a client computer without much overhead.
- Install VNC on both computers.
- Create a script that runs perpetually on the second computer looking for an image of a portion of the security window or better yet a pixel color that can be easily located on the security window. The script on the second computer will dismiss the security window on the primary computer.
- Log in to the Excel automation computer from the second computer using VNC
- Run the Excel automation script normally.
- Run the Security window dismissing script on the second computer
- If all is set up properly the second computer will act as you while you continue to sleep.
The OS permissions security has no affect on the actions of the second computer.
Hope this makes sense. If not ask and I'll try to clarify.
The solution I found requires a second computer and whatever vnc software you like. I like TightVNC. If you don't know VNC it allows remote access of a client computer without much overhead.
- Install VNC on both computers.
- Create a script that runs perpetually on the second computer looking for an image of a portion of the security window or better yet a pixel color that can be easily located on the security window. The script on the second computer will dismiss the security window on the primary computer.
- Log in to the Excel automation computer from the second computer using VNC
- Run the Excel automation script normally.
- Run the Security window dismissing script on the second computer
- If all is set up properly the second computer will act as you while you continue to sleep.
The OS permissions security has no affect on the actions of the second computer.
Hope this makes sense. If not ask and I'll try to clarify.
Re: Issue with Excel macro
Interesting. Thanks fore the info, much appreciated. We will give this a shot.
