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.