The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
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
http://biinsight.com/connect-to-power-bi-desktop-model-from-excel-and-ssms/
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
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
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
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...
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 🙂
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
exactly!
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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
270 | |
129 | |
123 | |
100 | |
92 |