Use VBA to Automate the Login to PowerPivot

[guymasspk]

Excel 2013 on Windows 7.
My users are complaining that there are too many steps to take every time they want to run a PowerPivot query.
PowerPivot --> Manage (data model) --> Get External Data --> From Database --> From SQL Server --> Select ServerName + choose "Use SQL Server Authentication [Enter Login and Password] + Select DataBase Name --> Next

They have to perform all of these actions to get to the point where they are able to select the Query that they want to run. They have requested that we provide a button that automatically logs them in and gets to the point (as indicated in the list above).

I have only been successful in using SendKeys to open the first PP menu - Manage. But I don't know how to 'click' the next menu buttons in order to proceed.

I've tried using CommandBars.ExecuteMso "Manage" -- but I don't know is the right way to proceed -- and I am getting syntax errors (Don't know how to implement ExecuteMso).

Can someone provide a cod example showing how to accomplish this?

 

[guymasspk]

Thanks for everyone's suggestions - they are all good options.
I was able to login automatically using an AutoHotKey script and distributed it as an EXE. Had to play around with the Sleep (wait time) - but pretty simple.


I don't know if this question be a separate thread... "Editing the ODC file (HTML)"
I also tried to use the ODC file - but I'd like to be able to edit that file so the user has the option to select a different table at run time. I was thinking of asking the user which table (or view) they want to load by presenting them with a dropdown from which they would select. Then I would edit the ODC file and they could easily connect using the "new" file. Does anyone have experience editing an HTML file programmatically using VBA? I only want to replace the "table text".

 

[TimRodman]

If you do Imke's suggestion of not selecting any tables on the initial creation of the connection, then the .ODC file will open Excel and take you directly to the table selection dialog box as soon as you double-click on the .ODC file. No programming needed!

 

[guymasspk]

Yes, the ODC option is excellent -- and without coding.
I wanted to attach a picture - I used the button (Insert Image) - but the page keeps freezing.

 

[TimRodman]

You have to put the image somewhere so you can link to it. I use imgur.com