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?

 

[TimRodman]

This is a great idea. I have the same problem at my company and we don't want to spring for the Power BI monthly fee in order to use the Power Query Data Catalog just yet.

However, I'm not sure how to do this in VBA.

Your post got me thinking though. Have you thought about using a program like AutoHotKey to do this? AutoHotKey is like VBA but for Windows and you can turn your scripts into EXE files. It can read mouse clicks, but I personally think it's best to send keyboard shortcuts since they're more reliable. You could send Alt-BM to open the PowerPivot Window, then Alt-HD1S to open the From SQL Server dialog box. Then you could tab through the fields to enter your information and use Alt-N to advance to the next window in the wizard.

 

[guymasspk]

Sounds interesting. I took a look at it. Any idea how I can pass the "parameters" to the Server Name dropdown (to avoid asking the user to select the server)? Same issue for the Database name. Alternatively, I noticed there is an option to use "From other sources" so I will look into how to save an ODC file and import that as part of the script.
I guess you are saying that once I get it working on my PC I can convert to an EXE and distribute it to other users who won't need to have AutoHotKey installed?

 

[TimRodman]

Try putting the code on the bottom of this in a Notepad file and save it with an .ahk extension. You can follow this link for instructions on how to convert it to an .exe.

Just replace SERVER with your server name and DATABASE with your database name.

The "Sleep, 1000" commands are telling it to sleep for one second. You might want to increase this if you think the windows will take a little longer to open.

Run, Excel.exeWinWaitActive, Book1 - Excel
Send {Alt Down}{Alt Up}bm
WinWaitActive, PowerPivot for Excel - Book1
Sleep, 1000
Send {Alt Down}{Alt Up}hd1s
Sleep, 1000
Send SERVER
Send {Tab}{Tab}{Tab}
Send DATABASE
Send {Alt Down}n{Alt Up}
Sleep, 1000
Send {Alt Down}n{Alt Up}

 

[ImkeF]

Hey guys, you’re quite adventurous - jumping all security settings

If you’re interested in a compliant alternative:

How about switching the SQL Server access to windows authentication? Then you wouldn’t have to enter the PowerPivot window at all.

Simply create an odc connection to your DB on the SQL server directly from Excel (will be stored per user) once – this can then be reused again & again.
So users would then just have to select: Data – Get External Data – Existing connections – Select&Open the connection. Then your desired window will pop up.

In order to create a connection on DB-level (that will open the desired window) instead of on table level, it’s crucial that you don’t select any tables on the initial creation of the connection. Instead uncheck “connect with one table” (or similar, using a german version) & check the box below: “Allow selection of multiple tables”. But don’t select any tables here! (The last checkbox will be greyed out) – continue – finish – check checkbox “Selection of multiple tables” and now check all tables. Check “only create connection” – and you’re done

 

[miguel.escobar]

Power Pivot doesn't really suits your scenario of sourcing data. I'd recommend using Power Query (it has a nice navigator for you to search your query by simply typing + storing credentials for different data sources) to load the data into Power Pivot.

 

[TimRodman]

@Imke - I was assuming Windows authentication in my script above, but I agree that .ODC would be MUCH cleaner. For some reason, I thought that you couldn't send anything on the Data ribbon to the Data Model with .ODC, but I just tried your suggestion and that's not the case. Now, I would maybe resort to macros if I wanted to prompt a user for a fact table and then automatically bring in the relevant dimension tables / create the relationships, but if I just want to take them to the list of tables, then .ODC works great.

@Miguel - I think the My Data Catalog Queries feature in Power Query would work in this situation, but I think you need a Power BI license to use it don't you?

 

[miguel.escobar]

Hey Tim,

The Catalog would be desirable! but not fully required since his scenario is quite straightforward. I'd just suggest the following.

1. Install Power Query on each client
2. Give them the credentials and they just need to set those once (and only once)
3. Let them connect to the data source and they can choose from a List
4. They can now load the query directly to the data model and that's it

Next time they use it all they have to do is:
1. Go to the recent sources in POwer Query and choose the source that has the queries that they need
2. choose or search (by typing a keyword) and click load to (data model)


The other workaround is still using Power Pivot but:
1. Save the credentials in the workbook
2. Instead of creating a new connection simply open the existing connection and choose the new queries that you want to add to your data model

That's it! The only bad part is that if they need to create a new workbook then they WILL have to create a new connection and do the long process that the OP explained which is where Power Query shines

Hope this helps.

 

[ImkeF]

Well - at least on my computer you don't need to create a new connection. The odc to the DB will be stored under ...documents/my datasources. But you can only acccess it directly in Excel through Data... and so on as I've descriebed above. Power Pivot doesn't offer them (under existing connections).

@Tim: Actually, the checkbox "Import relations between selected tables" comes as well with that odc-approach - so get the macro ready

 

[TimRodman]

@Miguel - I think the "recent sources" part is the secret to making your suggestion a winner for this situation. Your last idea got me thinking, if you want to only use Power Pivot then you could connect to a dummy table that doesn't have any data in it (to keep the workbook size small). Then you could save the workbook as an .xltx Excel Template and put it on a network share or a SharePoint library. Then you would essentially have a "Connection Template" and you could just click the Existing Connections button to access the data. Of course this would only work well with windows authentication.

@Imke - I thought the "Import relations between selected tables" feature only worked if the relationships were defined in SQL Server. Or it guesses based on the column names which makes me nervous. Is that right?