Solved

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

Posted on 2013-06-24
31
807 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Synchronize a new Active Directory domain with an existing Office 365 tenant
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

813 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

16 Experts available now in Live!

Get 1:1 Help Now