Solved

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

Posted on 2013-06-24
31
785 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_
  • 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

705 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now