Reply
mim Member
Member
Posts: 117
Registered: ‎04-26-2016
Accepted Solution

connect to powerbi desktop

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 Smiley Happy

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

 

cheers 


Accepted Solutions
Member
Posts: 100
Registered: ‎11-18-2015

Re: connect to powerbi desktop

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

View solution in original post

Super Contributor
Posts: 1,025
Registered: ‎09-06-2015

Re: connect to powerbi desktop

There we go:

Lines.FromBinary(Table.SelectRows(Folder.Files(“C:\Users\..YourUserName..\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces”), each ([Name] = “msmdsrv.port.txt”))[Content]{0},null,null,1200){0} 

:-)

 

Thank you @Brian_M, your source was golddust: http://www.thebiccountant.com/2016/04/09/hackpowerbi/

& thanks to @mim for forwarding :-)

View solution in original post


All Replies
Member
Posts: 100
Registered: ‎11-18-2015

Re: connect to powerbi desktop

With VBA, could you read the port number in the msmdsrv.port.txt file in the PBI desktop temp directory?

 

http://biinsight.com/connect-to-power-bi-desktop-model-from-excel-and-ssms/

 
You'll find details under the heading "Finding Power BI Desktop local port from Power BI Desktop temp directory"

 

Hope that helps!

Highlighted
mim Member
Member
Posts: 117
Registered: ‎04-26-2016

Re: connect to powerbi desktop

sorry if i was not clear, i know how to get the powerbi desktop port number, i am using a batch file that when i click on it,it give me the port number, my question is, if it was possible to do the same thing using VBA

Member
Posts: 100
Registered: ‎11-18-2015

Re: connect to powerbi desktop

Understood. Are you looking to just display the port number in Excel using VBA or are you looking to establish the connection using VBA, or both?

mim Member
Member
Posts: 117
Registered: ‎04-26-2016

Re: connect to powerbi desktop

[ Edited ]

I want just to display the port number in Excel using VBA, for the rest i am using Powerquery see this blog post

 

 

Member
Posts: 100
Registered: ‎11-18-2015

Re: connect to powerbi desktop

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

mim Member
Member
Posts: 117
Registered: ‎04-26-2016

Re: connect to powerbi desktop

hm, very interesting Smiley Happy thank you very much

 

so there is a file with a port number,  I did not know that,  after your reply, i don't need vba for that, I will just get the data using Powerquery.

 

thanks again that was really helpful 

Member
Posts: 100
Registered: ‎11-18-2015

Re: connect to powerbi desktop

Why oh why didn't I think of PowerQuery, doh... ! 

 

Although you probably don't need it, here's the VBA I wrote anyways... You'd need to include Microsoft Scripting Runtime in Tools > Reference in VBA Editor

http://stackoverflow.com/questions/3233203/how-do-i-use-filesystemobject-in-vba

 

Replace %%%% in the folder path with your UserName

 

Sub GetASPortNumbers()

Dim fname As TextStream
Dim fso As FileSystemObject
Dim FSfolder As Folder
Dim strStartPath As String
     
strStartPath = "C:\Users\BrianMather\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces"
    
'Clear first column
Columns(1).Clear
Columns(2).Clear

'Select Cell A1
Range("A1").Select


Set fso = CreateObject("Scripting.FileSystemObject")
Set FSfolder = fso.GetFolder(strStartPath)

For Each subfolder In FSfolder.SubFolders
        DoEvents
        i = i + 1
        ' Drop out the AnalysisServices Random Folder Name for each open pbix file
        Cells(i, 1) = Replace(subfolder, strStartPath & "\", "")
        
        ' Drop out the port number from the msmdsrv.txt file
        'Open the file
        Set fname = fso.OpenTextFile(subfolder & "\Data\msmdsrv.port.txt", ForReading, True)
        
        'Read the text
        text = fname.ReadLine
        
        'Replace the pesky Chr(0)
        Cells(i, 2).Value = Replace(text, Chr(0), "")
        
        'Close the file
        fname.Close
        
Next subfolder
     
    
Set FSfolder = Nothing
Set fname = Nothing
Set fso = Nothing

End Sub
Member
Posts: 100
Registered: ‎11-18-2015

Re: connect to powerbi desktop

I've built a Power BI Desktop template for doing exactly this but I think one of my steps required a manual step to get details from DAX Studio.

 

I'll look into this, you might have just made it very easy to open the template and analyse the open (or all open!!) pbix.

 

I'll let you know!

Super Contributor
Posts: 1,025
Registered: ‎09-06-2015

Re: connect to powerbi desktop

There we go:

Lines.FromBinary(Table.SelectRows(Folder.Files(“C:\Users\..YourUserName..\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces”), each ([Name] = “msmdsrv.port.txt”))[Content]{0},null,null,1200){0} 

:-)

 

Thank you @Brian_M, your source was golddust: http://www.thebiccountant.com/2016/04/09/hackpowerbi/

& thanks to @mim for forwarding :-)