Creating a connection to Analysis Services

[ptmuldoon]

I'm trying to learn how to create a connection via VBA to a external Financial Analysis database where I then can create a Pivot Table and run CUBE formulas from that connection.

Currently, If I want to create a connection, I need to enter

1. the url address. something like. https://someaddress/blah/blah.dll
2. A username and password.

I know after I do this, the connection creates an odc file and the connection is stored on the PC. After that I typically just tell Excel to create the connection only, and then I will add pivot table if needed and start building a spreadsheet with the CUBE formulas.

I'm trying to perhaps make this easier for others where I may look to include the script in our XLAM add in file where the user would maybe be prompted for the password in creating the connection. Also, is there any to store the connection in the XLAM Add-in file itself and not in an odc file? Does that make sense?

I'm just not sure even where to start in trying to create the connection.

If it helps, I did export the odc file and can see this information. So I think I just need to learn how to turn into a script.

Please Login or Register  to view this content.

Any help is appreciated.

Thanks,
PT

[ptmuldoon]

So I made some progress on this and could perhaps use a little help.

I'm trying to check if 2 connections exist and if they do not, then add them. This is partially working but not quite correct. I know in testing if one connection exist, it is not adding the other one.

I think also that I do need to check if any connections exist first (count). As attempting to loop and if no connections are found, they are never added?

Please Login or Register  to view this content.

EDIT. I caught an error where I using ThisWorkbook vs ActiveWorkbook. But now when I run the code, it will keep adding/duplicating the connection even though it already exists?

I think I understand the logic/cause on why it is duplicating, but not sure about how to fix yet. I guess when it loops the connection names, the first loop will see it doesn't match one of the names, thus get's added.