Get Data Source from Connection String

[Vivek D]

I have only 1 Connection (to an OLAP DB) setup in a workbook. I would like to get the Data Source name from the connection string and use that to check if I am able to connect to the Data Source or not and show an appropriate message if it cannot connect.

I've gotten the check connectivity part figured out but want a way to dynamically pick the Data Source from the connection string so that when I switch between Dev/QA/Production by changing the Data Source, I don't have to change the Data Source in the CheckConnectivity procedure as well.

My connection string currently looks like this

Provider=MSOLAP.6;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=DB_NAME;Data Source=https://portal.abc.com;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue

I want to get the Data Source i.e. https://portal.abc.com

 

[Chihiro]

Can't you declare Data Source (or Provider) string as variable and use that downstream?

 

[Vivek D]

Right now, I change it directly in the Data -> Connections in excel menus. So usually I just change the URL when switching from Dev/QA/Prod.

If there isn't a direct way to get the Data Source, I will declare and use.

 

[Chihiro]

I have searched before but wasn't able to find a clean solution for it.