Solved

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

Posted on 2013-06-24
31
836 Views
Last Modified: 2013-07-03
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
0
Comment
Question by:Mandy_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 14
31 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 39273813
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
 
LVL 2

Author Comment

by:Mandy_
ID: 39273829
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39273850
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 2

Author Comment

by:Mandy_
ID: 39274306
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
 
LVL 2

Author Comment

by:Mandy_
ID: 39277368
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39277432
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
 
LVL 2

Author Comment

by:Mandy_
ID: 39277675
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39277676
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
 
LVL 2

Author Comment

by:Mandy_
ID: 39277776
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39277804
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
 
LVL 2

Author Comment

by:Mandy_
ID: 39277853
unfortunately: runtime error 424. missing object
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39277884
i just run this code on excel and it worked like a charm.
do u see powershell window opened?
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39278187
no nothing happens
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39278196
can u post the whole code?
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39279254
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39280513
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
 
LVL 2

Author Comment

by:Mandy_
ID: 39280622
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39280626
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
 
LVL 2

Author Comment

by:Mandy_
ID: 39280653
Add-MailboxPermission -identity user1 -user group1 -AccessRights “FullAccess”		

Open in new window

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39280686
can u post the excel?
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39280723
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39280739
i mean the whole excel file, i'll try to run the code  against the excel.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39280838
ok! here's the excel
test1.xlsm
0
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 39280864
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
 
LVL 2

Author Comment

by:Mandy_
ID: 39281034
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39281075
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
 
LVL 2

Author Comment

by:Mandy_
ID: 39281409
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
 
LVL 2

Author Comment

by:Mandy_
ID: 39283705
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
 
LVL 2

Assisted Solution

by:Mandy_
Mandy_ earned 0 total points
ID: 39286334
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
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 39296035
to resolve at all you need to find my own code
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39296053
Grade C? Really?
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question