cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mim
Advocate V
Advocate V

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 🙂

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

 

cheers 

2 ACCEPTED SOLUTIONS
Brian_M
Responsive Resident
Responsive Resident

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

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 🙂

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

14 REPLIES 14
Brian_M
Responsive Resident
Responsive Resident

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!

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

Brian_M
Responsive Resident
Responsive Resident

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?

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

 

 

Brian_M
Responsive Resident
Responsive Resident

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!

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 🙂

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Brian_M
Responsive Resident
Responsive Resident

Ha ha, you beat me to it! Much more succinct than mine too. I forgot that Folder.Files would get everything in the subfolders, I was away scrapping the subfolder names to search through those!

 

If you had the need, you could probably anlayse multiple open files at once and be able to switch / slice between them in a single PBI documenter template.

 

The only thing that is really missing is the ability to find the user appdata directory based on whoever is logged in. Yet another reason to have...

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13926666-provide-username-functio... 

 

Can't wait to be able to access my VM to get at my PBI Documenter Template to drop these new bits in! 

if you use Excel as a front end, you can use dax function CALCULATE(USERNAME()) as measure, then create a linked table in a sheet then we have everything 🙂

Brian_M
Responsive Resident
Responsive Resident

Yes, although I'm really trying to go all Power BI Desktop for my pbix analyser tool.

 

I've figured out a way of getting the port numbers for all the open workbooks without knowing the username just using Power BI Desktop, but I'm trying to rationalise the M code to make it usable. I'll keep you posted.

you are right we don't need the user name, just select the folder users and filter, in my case, i am only interested in one instance.

 

that's really good, now the connection to PowerBI desktop is fully automated, no manual steps.

 

thank you very much, that really made a big difference 

 

time to celebrate

Brian_M
Responsive Resident
Responsive Resident

exactly!

 

Brian_M
Responsive Resident
Responsive Resident

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

hm, very interesting 🙂 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 

Brian_M
Responsive Resident
Responsive Resident

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.