Excel command Center with powershell #2 - execute all command lines with 1Click

Hi,

the commands you can see below in excel, i paste to Exchange Management Shell.
The variables you see in the screenshot above. If is change this above this will change
in the powershell commands below.

All these commands create an exchange object "Mailbox". Now every command line
i've to paste into powershell. What i like is to execute the complete lines of commands
with a active_X button in excel.

How exactly i can code the embedded excel button which executes all command lines
step by step with a pause between the lines.

example_1
button
button2
LVL 2
Mandy_Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Meir RivkinConnect With a Mentor Full stack Software EngineerCommented:
try this:
Private Sub CommandButton1_Click()
Counter = 15
Do
        Dim Command
        Command = Worksheets("ext2010").Cells(Counter, 2).Value
 Shell ("C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -noexit icm -ScriptBlock {" & Command & "}")

Counter = Counter + 1
Loop Until Command = ""

End Sub

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
this is the basic command line to run powersehll command expression:
powersehll iex "command-to-run"

Open in new window

iex stands for invoke-expression (alias).

so, upon button click, you want to loop the worksheet cells which have all the command and run something like:

   For Counter = 1 To whatever
        Set curCell = Worksheets("Sheet1").Cells(Counter, 1)
        command = curCell.Value
 Call Shell("powershell -noexit iex ' & command & '", vbHide)
    Next Counter

Open in new window

this is just ane xample, u need to update the counter values and put the correct row number and column index.
0
 
Mandy_Author Commented:
hi, thanks. Could you explain that with example below?


="Add-MailboxPermission -identity "&B18&" -user "&C18&" -AccessRights “FullAccess”"
Cell B35:C35 Add-ADPermission Z021470 -User S021470 -ExtendedRights “Send as”

Open in new window

0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
Meir RivkinFull stack Software EngineerCommented:
command = "Add-MailboxPermission -identity '" & Worksheets("Data").Range("B18").Value & "' -user '" & Worksheets("Data").Range("C18").Value & '" -AccessRights 'FullAccess'"

 Call Shell("powershell -noexit iex ' & command & '", vbHide)

Open in new window

0
 
Mandy_Author Commented:
hi,



the shell EMS i'm calling from desktop:
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -noexit -command ". 'C:\Exchsrvr\Bin\RemoteExchange.ps1'; Connect-ExchangeServer -auto"

Open in new window


with this code below i'm getting runtime error 424  object missing


Private Sub CommandButton1_Click()
Command = "Add-MailboxPermission -identity '" & Worksheets("EX2010 Commands").Range("A93").Value & "' -user '" & Worksheets("EX2010 Commands").Range("A94").Value & "' -AccessRights 'FullAccess'"
Call Shell("C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -noexit iex ' & command & '", vbHide)

Open in new window


pic
0
 
Mandy_Author Commented:
What you think could be the problem?  "424  object missing"

How can i call the EMS?

What means Counter?  Where i should include the Cell info like A1,  C12....

For Counter = 1 To whatever
        Set curCell = Worksheets("Sheet1").Cells(Counter, 1)
0
 
Meir RivkinFull stack Software EngineerCommented:
counter used to loop through the cells which contains the commands that u wanna run.
the initial screenshot i posted in your question, the commands starts from row 29 in the second column:

Counter = 29
do
        command = Worksheets("Sheet1").Cells(Counter, 2).Value
Call Shell("C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -noexit iex ' & command & '", vbHide)

Counter=Counter+1
while command <> "" 

Open in new window

0
 
Mandy_Author Commented:
Starts in row29  until which row?  i see Counter=Counter+1
That means only the next row?  And if i like all commands from row29 until 37  ?


thx
0
 
Meir RivkinFull stack Software EngineerCommented:
i use  while command <> "", meaning that the first row with no text i exit the loop.
so if 38 row is empty, all the commands from 29 to 37 will be processed.
0
 
Mandy_Author Commented:
Counter = 29
do
        command = Worksheets("Sheet1").Cells(Counter, 2).Value
Call Shell("C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -noexit iex ' & command & '", vbHide)

Counter=Counter+1
while command <> ""



1st i'm getting error "while without wend"
2nd do without loop

and if i'm grabbing only 1 row then again:

runtime error 424. missing object
0
 
Meir RivkinFull stack Software EngineerCommented:
do
        command = Worksheets("Sheet1").Cells(Counter, 2).Value
 Shell ("C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -noexit iex " & command)

Counter=Counter+1
Loop Until command = ""

Open in new window

0
 
Mandy_Author Commented:
unfortunately: runtime error 424. missing object
0
 
Meir RivkinFull stack Software EngineerCommented:
i just run this code on excel and it worked like a charm.
do u see powershell window opened?
0
 
Mandy_Author Commented:
no nothing happens
0
 
Meir RivkinFull stack Software EngineerCommented:
can u post the whole code?
0
 
Mandy_Author Commented:
Sub Button_click()
Counter = 1
Do
        Command = Worksheets("sheet1").Cells(Counter, 1).Value
 Shell ("C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -noexit iex " & Command)

Counter = Counter + 1
Loop Until Command = ""

End Sub

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
first of all make sure line 4 returns the right command.
put log or MsgBox after line 4 to display command value.
if that works fine, takes an example of command and try to run line 5 in a command prompt (just the PS part).
for example, if the command value is Add-MailboxPermission -identity xxx, run the following:
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -noexit iex Add-MailboxPermission -identity xxx
0
 
Mandy_Author Commented:
Private Sub CommandButton1_Click()
Shell ("C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -noexit iex -Command { Import-Module ActiveDirectory; Add-ADGroupMember " & [User1] & "  " & [group1] & " }")
End Sub

Open in new window


the erros i'm getting in ps window:
Invoke-Expression : Cannot evaluate parameter 'Command' because its argument is
 specified as a script block and there is no input. A script block cannot be ev
aluated without input.
At line:1 char:13
+ iex -Command <<<<  { Import-Module ActiveDirectory; Add-ADGroupMember }
    + CategoryInfo          : MetadataError: (:) [Invoke-Expression], Paramete
   rBindingException
    + FullyQualifiedErrorId : ScriptBlockArgumentNoInput,Microsoft.PowerShell.
   Commands.InvokeExpressionCommand

Open in new window



this above opens the windows powershell window but gives error but for exchange commands
have to be open the ExchangeManagementShell. From Desktop open the EMS with this parameter:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -noexit -command ". 'C:\Exchsrvr\Bin\RemoteExchange.ps1'; Connect-ExchangeServer -auto"

Open in new window


how can i open the ems from vba?
0
 
Meir RivkinFull stack Software EngineerCommented:
can u post the first command from the excel please?
i think u need to manipulate the command before apply it into the powersehll script
0
 
Mandy_Author Commented:
Add-MailboxPermission -identity user1 -user group1 -AccessRights “FullAccess”		

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
can u post the excel?
0
 
Mandy_Author Commented:
example

counter = 35
do
        command = Worksheets("EX2010 Commands").Cells(Counter, 2).Value
 Shell ("C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -noexit iex " & command)

Counter=Counter+1
Loop Until command = ""

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
i mean the whole excel file, i'll try to run the code  against the excel.
0
 
Mandy_Author Commented:
ok! here's the excel
test1.xlsm
0
 
Mandy_Author Commented:
oh nice now so far it's working. Thank you.  But it's still only windows powershell not EMS

open as below:







C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -noexit -command ". 'C:\Exchsrvr\Bin\RemoteExchange.ps1'; Connect-ExchangeServer -auto"

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
to run the Exchange Management Shell EMS in PowerShell u need first to the following:
1. create a session to the exchange server:
$s = new-PSsession -configurationName microsoft.exchange -connectionUri http://exchange.xxx.local/powershell

Open in new window

replace http://exchange.xxx.local/powershell with your exchnage uri.

2. import the exchange session variables:
Import-PSsession $s

Open in new window


in this point u can run the code as usual.
you can integrate this 2 lines of code into to your excel sheet so the vba code will run this first and then the rest of the commends.
0
 
Mandy_Author Commented:
ok so far.  every line in excel opens a separate powershell window. So every command
only working in its own ps session

example in sheet

$s = new-PSsession -configurationName microsoft.exchange -connectionUri http://XX-XXXX-XXX.XXX.XXX.XX/powershell
Import-PSsession $s
Import-module ActiveDirectory
Add-ADGroupMember -Identity Z017873 -Member P2332824


What we can do?
0
 
Mandy_Author Commented:
Private Sub CommandButton1_Click()
Counter = 19
Do
        Dim Command
        Command = Worksheets("ext2010").Cells(Counter, 2).Value
        
Shell ("C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -PSConsoleFile ""C:\Exchange\bin\RemoteExchange.ps1"" -noexit icm -ScriptBlock {" & Command & "}")
Counter = Counter + 1
Loop Until Command = ""

End Sub

Open in new window


I tried this above but nothing happens


How can i open every row with code in the same windws?
0
 
Mandy_Connect With a Mentor Author Commented:
this code below is working

Shell ("C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -PSConsoleFile ""C:\Program Files\Microsoft\Exchange Server\bin\ExShell.psc1"" -noexit icm -ScriptBlock {" & Command & "}")

Open in new window

0
 
Mandy_Author Commented:
to resolve at all you need to find my own code
0
 
Meir RivkinFull stack Software EngineerCommented:
Grade C? Really?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.