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
• 12,240 points
11 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
• 20,880 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,360 points
196 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 18 hours ago in Others by narikkadan
• 20,880 points
5 views
0 votes
0 answers

Remove formulas from all worksheets in Excel using VBA

I'm having trouble getting Excel to remove ...READ MORE

2 hours ago in Others by Kithuzzz
• 12,240 points
1 view
0 votes
0 answers

How to create Dropdown list in excel using php

I need to use PHP to construct ...READ MORE

2 hours ago in Others by Kithuzzz
• 12,240 points
1 view
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
402 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,095 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
• 18,960 points
41 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
134 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
• 20,880 points
22 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
• 20,880 points
18 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