Automatic refresh of excel where connection is password protected

I am trying to perform the following steps either by Python 2.7 or AutoIt scripts. Preferable is Python though.

1. Open excel file.
2. Press 'refresh all'
3. Enter userid and password in a dialog box.
4. Save excel after refresh
5. Quit excel

I am stuck automating the 3rd steps when the excel file in question asks for credentials for executing a web query. The files do not allow to save credentials as well so for every refresh it asks for credentials. Can someone please help on automating of this part.

One way you could do this is by using a python module called pyautogui. http://pyautogui.readthedocs.io/en/latest/

pyautogui is a module that allows you to move the mouse to a coordinate on the screen and also do keyboard functions. You could have it [every once in a while] open up the excel file, move the mouse and click the button, then enter the username and password and so on. Go ahead and check out the link.