VBA excel - create skype account using powershell script

0 votes

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 enter image description here

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
Sep 22 in Others by Kithuzzz
• 20,660 points
64 views

1 answer to this question.

0 votes

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)
answered Sep 23 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Create a User Account in Windows 10 with PowerShell.

Hi@akhtar, To create a new user account without ...READ MORE

answered Oct 7, 2020 in Others by MD
• 95,380 points
227 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
129 views
0 votes
1 answer

How to create and download excel document using asp.net

First, download the Open XML Format SDK 2.0. It comes ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
167 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
108 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
452 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,223 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
69 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
209 views
0 votes
1 answer

Trying to create an enclosing bookmark after pasting a picture from Excel into Word using VBA

Try this: Sub IMPORT_TO_WORD() Dim ws1 As Worksheet, ws2 ...READ MORE

answered Oct 30 in Others by narikkadan
• 37,660 points
41 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
106 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP