1

I have this batch file that look for the process name msmdsrv.exe, get the PID number using tasklist then get the port number using netstat where the connection type is established.

At the end it will export the result into a file PBD_Port.csv 

for /f "tokens=2 delims=," %%F in ('tasklist /nh /fi "imagename eq msmdsrv.exe" /fo csv') do ( 
set var=%%F
)

for /f "tokens=2 delims= " %%h in ('netstat -ano ^| findstr ESTABLISHED ^|   findstr %var%') do ( 
set var1=%%h
)

echo %var1% > PBD_Port.csv

I use this code to get the port number of the PowerBi Desktop, as it do change every time when we launch Power Bi Desktop.

Is it possible to have a macro in Excel that do the same thing ? so instead of using a batch file where the user has to click on it, i want a macro to give the same result and write the result not in a CSV but in a worksheet.

edit : i built the code for the batch, I wanted a general advice how to translate this to VBA, thanks for your comment

edit : actually, i was over complicating things, the port number is already stored in a temporary text file generated by the new SSAS instance, see different solution here

http://community.powerbi.com/t5/Desktop/connect-to-powerbi-desktop/td-p/87310

Mim
  • 999
  • 10
  • 32
  • 1
    Your question is far too broad here; narrow it by showing your attempts! Note that CSV files do not have multiple worksheets, it is a simple text file only, hence there is no "active worksheet"! – aschipfl Nov 08 '16 at 11:18
  • This site is about helping people who try, not to spoon feed everyone. Show us some attempts and we will give in. Good luck :) – NizonRox Nov 08 '16 at 12:20
  • if you know what the batch script is doing, in VBA you can get the output of a shell command in a string variable with something like `stringResult = CreateObject("WScript.Shell").Exec("tasklist /nh /fi ""imagename eq msmdsrv.exe"" /fo csv").StdOut.ReadAll` http://stackoverflow.com/questions/2784367/capture-output-value-from-a-shell-command-in-vba – Slai Nov 08 '16 at 12:25
  • thanks for that, i will try that – Mim Nov 08 '16 at 12:34

0 Answers0