0

i have an excel file with buttons which launch powershell to execute scripts (adding members to groups, connecting to exchange server to setting up email account etc.), there is like 3 files with more than 50 buttons and we need to execute it every few days

The script is actually calling powershell and execute the command such as adding members to group based on the value of the cell in the excel files. It was working fine and the below is the example for the existing code:

Dim pid As Variant
Dim a As String
Dim i As Long

For i = 1 To ThisWorkbook.Worksheets("Add AD Group & DL").Range("a9999").End(xlUp).Row - 8
a = "powershell -noprofile -command ""&{Add-ADGroupMember -Identity dgroup -members uname}"""
a = Replace(a, "uname", Range("a" & i + 8).Value)
a = Replace(a, "dgroup", Range("g" & i + 8).Value)


exe1 = a

pid = Shell(exe1, vbHide)
Debug.Print exe1

Next i

and below is another example which need to connect to exchange server:

Dim pid As Variant
Dim a As String
Dim i As Long

For i = 1 To ThisWorkbook.Worksheets("Create Email Account").Range("b9999").End(xlUp).Row - 8

a = "powershell -noprofile -command ""&{Set-ExecutionPolicy -Scope CurrentUser unrestricted;$serverURI = 'http://hkg-mrs.abcdm001.corp.domain.com/PowerShell/';$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri $serverURI -Authentication Kerberos;Import-PSSession $Session}""" _
& ";Get-Mailbox -identity username | Set-Mailbox -RetentionPolicy 'CPA MailBox Folder Policy for General Users'" _
& ";Enable-Mailbox username -Archive" _
& ";Set-Mailbox -Identity username -ArchiveQuota 5GB -ArchiveWarningQuota 4.5GB -emailaddresspolicyenabled $false" _
& ";Set-CASMailbox username -PopEnabled:$False -ImapEnabled:$False" _
& ";Set-Mailbox -Identity username -UserPrincipalName eaddress" _
& ";Remove-PSSession -Session (Get-PSSession)"""

a = Replace(a, "username", Range("b" & i + 8).Value)
a = Replace(a, "eaddress", Range("f" & i + 8).Value)
a = Replace(a, "fname", Range("c" & i + 8).Value)
a = Replace(a, "lname", Range("e" & i + 8).Value)



exe1 = a

        pid = Shell(exe1, vbHide)
        Debug.Print exe1
        Range("h" & i + 8).Interior.Color = XlRgbColor.rgbGreen
        Application.Wait Now + TimeSerial(0, 0, 3)


Next i

however, we are now required to use a different accounts (domain admin) to execute the script while we are logging into the PC using our own account. The way i would like to achieve is to ask user to type in the username and password in a specific cell (let say B1 and B2 on Sheet1) in the excel and I would like to modify the VBA script to refer the username and password in that specific cells and to run the scripts.

I know i can prompt the credential windows to get the domain admin credential, but since the script is launching like 20 powershell almost at the same time, so it is not easy for user to type in the password 20 times. Is there any easy way to achieve that??

Thank you so much.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • https://www.windows-commandline.com/windows-runas-command-prompt/ – Tim Williams May 05 '20 at 05:19
  • Also - https://ss64.com/ps/syntax-elevate.html – Tim Williams May 05 '20 at 05:29
  • 2
    No matter how inconvenient, never write down a domain admin credential in plain text. Ever. Ever. And especially not in a spreadsheet. The correct way here is to prompt for the PS credential object once and use that object as a parameter in all your scripts for subsequent operations. Yes, that means amending all your scripts to accept a credential object, – Scepticalist May 05 '20 at 05:58
  • You can create a service or a scheduled task that runs with an elevated account. That provides secure credential storage for the elevated account and you can grant access to restricted users/groups. – ComputerVersteher May 05 '20 at 07:07
  • @ComputerVersteher You can, but probably impractical for 50 buttons (and presumably therefore 50 scripts) as you'd have to create a task for each one. – Scepticalist May 05 '20 at 07:40
  • @Scepticalist you can [pass arguments to a service](https://stackoverflow.com/questions/1488851/how-to-pass-a-parameter-to-a-windows-service-once-and-for-all-at-install-instead). – ComputerVersteher May 05 '20 at 08:02

0 Answers0