Password Storage

[Tyler_Analyst]

I have a report I have to run that requires me to open up the power pivot window, select refresh all, enter in a username and password for the server then click ok. The PowerPivot refreshes the data eventually. Next I have to do a front-end refresh all under the data tab.

My issue is I want to write a macro that does all this for me so I can run the report when I am away from the desk. Does anyone have a suggestion on how to refresh all the power pivot window and store the password/enter the password in automatically.

Thanks!

 

[Matt Allington]

If you edit the connections in Power Query, can't you save the username and password against the connection? I know there is a place to do this in the wizard, but I do seem to remember someone saying it didn't work on one of the versions (Excel 2010 or 2013, can't remember)