connect to powerbi desktop

[mim]

hi Guys

i asked the question already in stockexcahnge, maybe i get a reply here 

i have a small batch code to get the port number of powerBi desktop , i use it to connect to my "personal " ssas instance 🙂

just wondering if we can get the same result using VBA in Excel.

cheers 

[Brian_M · Accepted solution]

Certainly you can read the contents of a text file using VBA. The trick will be to the random AnalysisServicesWorkspaceXXXXX folder in C:\Users\%%%%%%%%%%\AppData\Local\Microsoft\Power BI Desktop\ and to access the msmdsrv.port.txt within the Data folder.

If you have multiple Power BI Desktop files open you will have multiple folders there each with a random name. The VBA to open the file is here below which isn't quite what you want, I'll work up something that scans for each folder and returns the port number of each of the open workbooks.

Workbooks.OpenText Filename:= _
"C:\Users\%%%%%%%%%%\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspaceXXXXXXXXXXX\Data\msmdsrv.port.txt" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True