VBA excel - create skype account using powershell script

Question

I want to construct a script that will pass commands from VBA in Excel to PowerShell to establish Skype accounts. in fact, it is labor. However, since my script is only contacting one PowerShell for one account, an error will appear in powershell stating that I have exceeded the maximum number of connections when I try to establish five or more accounts.

Here is my modified script that only executes the last row of data after I tried to change it to call 1 powershell to create all the accounts by sending the command line to powershell after powershell connected to the Skype server module.

HERE IS THE SCREENSHOT OF MY EXCEL, THE SCRIPT NEED TO GET THE VALUE FROM COLUMN B 

Any help would be appreciated, thank you.

Sub createskype()

    Dim pid As Variant
    Dim command As String
    Dim command0 As String   
    Dim command1 As String
    Dim command2 As String
    Dim command3 As String
    Dim command4 As String    
    Dim username As String
    Dim exe1 As String
    Dim exe2 As String
    Dim sleep As String
    Dim call1 As String
    Dim call2 As String
    Dim call3 As String
    Dim call4 As String
    Dim call5 As String
    Dim call6 As String
    Dim call7 As String
    Dim exe1a As String
    Dim exe2a As String  
    Dim i As Long

    sleep = "Start-Sleep -s 60"

    command = "Enable-CsUser"
    command0 = "-RegistrarPool 'LyncPrimaryFrontPool.abc.com' -SipAddress 'sip:"
    command1 = "@abc.com'"    
    command2 = "Set-CsUser"
    command3 = "-AudioVideoDisabled $True" 
    command4 = "@abcdsss.com'"
    exitcmd = "Remove-PSSession -Session (Get-PSSession)" 
    call0 = "powershell -noprofile -command ""&{"
    call1 = "$username = 'xxxxxx'"
    call2 = "$password = '12345678'"
    call3 = "$secstr = New-Object -TypeName System.Security.SecureString"
    call4 = "$password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}"
    call5 = "$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr"
    call6 = "$session = New-PSSession -ConnectionUri https://MyServer/OcsPowershell -Credential $cred"
    call7 = "Import-PSSession -Session $session}"""

    exe1 = call0 & ";" & call1 & ";" & call2 & ";" & call3 & ";" & call4 & ";" & call5 & ";" & call6 & ";" & call7 & ";"
    exe2 = call0 & ";" & call1 & ";" & call2 & ";" & call3 & ";" & call4 & ";" & call5 & ";" & call6 & ";" & call7 & ";"

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

        username = Range("b" & i + 8).Value
        exe1a = command & " " & username & " " & command0 & username & command1 & ";" & sleep & ";" & command2 & " " & username & " " & command3
        exe2a = command & " " & username & " " & command0 & username & command4 & ";" & sleep & ";" & command2 & " " & username & " " & command3

    Next i

    If Range("a" & i + 8).Value = "a" Then
        pid = Shell(exe1 & exe1a, vbNormalFocus)
        Debug.Print exe1 & exe1a
        Range("h" & i + 8).Interior.Color = XlRgbColor.rgbGreen
        Application.Wait Now + TimeSerial(0, 0, 10)

    Else

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

    End If

    MsgBox "Skype account(s) has/have been created."

End Sub

Answer 1

Although the PowerShell portion has not been thoroughly tested, based on your data, this should provide the right PowerShell commands. If it is the correct PowerShell script, uncomment several lines. I deleted the private URL for the business.

It would be better to utilize #users instead of concatenating the PowerShell commands for each user into a single remote PowerShell session.

Option Explicit

Sub createskype2()

    Dim pid As Variant
    Dim command1 As String
    Dim command2 As String
    Dim command3 As String
    Dim exitcmd As String
    Dim username As String
    Dim exe0 As String
    Dim exe1a As String, exe2a As String
    Dim sleep As String
    Dim call0 As String
    Dim call1 As String
    Dim call2 As String
    Dim call3 As String
    Dim call4 As String
    Dim call5 As String
    Dim call6 As String
    Dim call7 As String

    Dim i As Long

    sleep = "Start-Sleep -s 60"

'    command = "Enable-CsUser "
'    command0 = " -RegistrarPool 'LyncPrimaryFrontPool.abc.com' -SipAddress 'sip:"
'    command1 = "@abc.com'"
'    command2 = "Set-CsUser "
'    command3 = " -AudioVideoDisabled $True"
'    command4 = "@abcdsss.com'"
    command1 = "Enable-CsUser <USER> -RegistrarPool 'LyncPrimaryFrontPool.abc.com' -SipAddress 'sip:<USER>@abc.com'"
    command2 = "Enable-CsUser <USER> -RegistrarPool 'LyncPrimaryFrontPool.abc.com' -SipAddress 'sip:<USER>@abcdsss.com'"
    command3 = "Set-CsUser <USER> -AudioVideoDisabled $True"

    exitcmd = "Remove-PSSession -Session (Get-PSSession)"

    call0 = "powershell -noprofile -command ""&{"
    call1 = "$username = 'xxxxxx'"
    call2 = "$password = '12345678'"
    call3 = "$secstr = New-Object -TypeName System.Security.SecureString"
    call4 = "$password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}"
    call5 = "$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr"
    call6 = "$session = New-PSSession -ConnectionUri https://MyServer/OcsPowershell -Credential $cred"
    call7 = "Import-PSSession -Session $session}"""

'    exe1 = call0 & ";" & call1 & ";" & call2 & ";" & call3 & ";" & call4 & ";" & call5 & ";" & call6 & ";" & call7 & ";"
    exe0 = Join(Array(call0, call1, call2, call3, call4, call5, call6, call7), ";") & ";"
    Debug.Print "exe0:", exe0

    With ThisWorkbook.Worksheets("Create Email Account")
        For i = 8 To .Cells(Rows.Count, "B").End(xlUp).Row
            If Not IsEmpty(.Cells(i, "B")) Then
                username = .Cells(i, "B").Value
                exe1a = Replace(command1, "<USER>", username) & ";" & sleep & ";" & Replace(command3, "<USER>", username) & ";" & exitcmd
                exe2a = Replace(command2, "<USER>", username) & ";" & sleep & ";" & Replace(command3, "<USER>", username) & ";" & exitcmd
'    exe1a = command & " " & username & " " & command0 & username & command1 & ";" & sleep & ";" & command2 & " " & username & " " & command3
'    exe2a = command & " " & username & " " & command0 & username & command4 & ";" & sleep & ";" & command2 & " " & username & " " & command3
                If LCase(.Cells(i, "A")) = "a" Then
                    Debug.Print exe0 & exe1a
'                    pid = Shell(exe0 & exe1a, vbNormalFocus)
'                    .Cells(i, "H").Interior.Color = XlRgbColor.rgbGreen
                Else
                    Debug.Print exe0 & exe2a
'                    pid = Shell(exe0 & exe2a, vbNormalFocus)
'                    .Cells(i, "H").Interior.Color = XlRgbColor.rgbGreen
                End If
            End If
'            Application.Wait Now + TimeSerial(0, 0, 10)
        Next i
    End With
    MsgBox "Skype account(s) has/have been created."

End Sub

Immediate Window Output:

exe0:         powershell -noprofile -command "&{;$username = 'xxxxxx';$password = '12345678';$secstr = New-Object -TypeName System.Security.SecureString;$password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)};$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr;$session = New-PSSession -ConnectionUri https://MyServer/OcsPowershell -Credential $cred;Import-PSSession -Session $session}";
powershell -noprofile -command "&{;$username = 'xxxxxx';$password = '12345678';$secstr = New-Object -TypeName System.Security.SecureString;$password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)};$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr;$session = New-PSSession -ConnectionUri https://MyServer/OcsPowershell -Credential $cred;Import-PSSession -Session $session}";Enable-CsUser KKLM -RegistrarPool 'LyncPrimaryFrontPool.abc.com' -SipAddress 'sip:KKLM@abc.com';Start-Sleep -s 60;Set-CsUser KKLM -AudioVideoDisabled $True;Remove-PSSession -Session (Get-PSSession)
powershell -noprofile -command "&{;$username = 'xxxxxx';$password = '12345678';$secstr = New-Object -TypeName System.Security.SecureString;$password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)};$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr;$session = New-PSSession -ConnectionUri https://MyServer/OcsPowershell -Credential $cred;Import-PSSession -Session $session}";Enable-CsUser KSTG -RegistrarPool 'LyncPrimaryFrontPool.abc.com' -SipAddress 'sip:KSTG@abc.com';Start-Sleep -s 60;Set-CsUser KSTG -AudioVideoDisabled $True;Remove-PSSession -Session (Get-PSSession)
powershell -noprofile -command "&{;$username = 'xxxxxx';$password = '12345678';$secstr = New-Object -TypeName System.Security.SecureString;$password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)};$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr;$session = New-PSSession -ConnectionUri https://MyServer/OcsPowershell -Credential $cred;Import-PSSession -Session $session}";Enable-CsUser PCLU -RegistrarPool 'LyncPrimaryFrontPool.abc.com' -SipAddress 'sip:PCLU@abc.com';Start-Sleep -s 60;Set-CsUser PCLU -AudioVideoDisabled $True;Remove-PSSession -Session (Get-PSSession)