Super tool - VBA

Public privateKey As String

 

Public puttyPath As String

Public plinkPath As String

Public winSCPPath As String

Public pscpPath As String

 

Public Const instanceIdColNum As Integer = 6

Public Const svrColNum As Integer = 4

Public Const acctColNum As Integer = 5

Public Const lPathColNum As Integer = 8

Public Const rPathColNum As Integer = 9

 

Public list_instance As String

 

Sub init()

 

privateKey = Sheets(2).Cells(4, 2).Value

puttyPath = Sheets(2).Cells(5, 2).Value

plinkPath = Sheets(2).Cells(6, 2).Value

winSCPPath = Sheets(2).Cells(7, 2).Value

pscpPath = Sheets(2).Cells(8, 2).Value

 

End Sub

 

Function getServerRowNum(ByVal rowNum)

 

    Dim currentRowNum, currentServerRowNum

    

    If rowNum = 0 Then

        currentRowNum = ActiveCell.Row

    Else

        currentRowNum = rowNum

    End If

    

    currentServerRowNum = currentRowNum

    

    Do While Cells(currentServerRowNum, svrColNum) = ""

        currentServerRowNum = currentServerRowNum - 1

    Loop

    

    getServerRowNum = currentServerRowNum

    

End Function

 

Sub runPutty()

 

    init

    

    Call Shell("""" & puttyPath & """" & " -i " & """" & privateKey & """ " & Cells(ActiveCell.Row, acctColNum) & "@" & Cells(getServerRowNum(0), svrColNum), vbNormalFocus)

 

End Sub

 

Sub runWinSCP()

 

    init

    

    Call Shell(winSCPPath & " scp://" & Cells(ActiveCell.Row, acctColNum) & "@" & Cells(getServerRowNum(0), svrColNum) & ":22 /privatekey=""" & privateKey & """", vbNormalFocus)

    

End Sub

 

Sub runCommand(ByVal cmd)

 

    init

    

    getInstanceList

    

    Call Shell("""" & plinkPath & """ -i """ & privateKey & """ " & Sheets(2).Cells(1, 2).Value & "@" & Sheets(2).Cells(1, 1).Value & " " & """" & cmd & " " & list_instance & "; read;""", vbNormalFocus)

    Rem MsgBox ("""" & plinkPath & """ -i """ & privateKey & """ " & Sheets(2).Cells(1, 2).Value & "@" & Sheets(2).Cells(1, 1).Value & " " & """" & cmd & " " & list_instance & """")

 

End Sub

 

Sub getInstanceList()

 

    Dim rows_selected

     

    For Each area In Selection.Areas

        For Each r In area.Rows

            If Not r.Row = 1 And Not r.Hidden Then

                rows_selected = r.Row & "," & rows_selected

            End If

        Next

    Next

     

    Rem MsgBox (rows_selected)

     

    Dim list_rows

    

    i = 3

    Do While i < 200

        If Not InStr(rows_selected, "," & i & ",") = 0 Or InStr(rows_selected, i & ",") = 1 Then

            list_rows = list_rows & i & " "

        End If

        i = i + 1

    Loop

     

    list_rows = RTrim(list_rows)

    

    list_instance = ""

    

    For Each i In Split(list_rows, " ")

        list_instance = list_instance & Cells(i, instanceIdColNum).Value & " "

    Next

    list_instance = RTrim(list_instance)

    

    Rem MsgBox (list_instance)

 

End Sub

 

Sub runDownload()

 

    runCommand ("./util/1_getEAR.sh " & Sheets(2).Cells(2, 2).Value & " '" & Sheets(2).Cells(3, 2).Value & "'")

 

End Sub

 

Sub runStop()

 

    runCommand ("./util/2_stop.sh")

 

End Sub

 

Sub runDeploy()

 

    runCommand ("./util/3_deploy.sh")

 

End Sub

 

Sub runStart()

 

    runCommand ("./util/4_start.sh")

 

End Sub

 

Sub runStopNodeAgent()

 

    runCommand ("./util/stopNodeAgent.sh")

 

End Sub

 

Sub runStartNodeAgent()

 

    runCommand ("./util/startNodeAgent.sh")

 

End Sub

 

Sub runStopDmgr()

 

    runCommand ("./util/stopDmgr.sh")

 

End Sub

 

Sub runStartDmgr()

 

    runCommand ("./util/startDmgr.sh")

 

End Sub

 

Sub runKill()

 

    runStopNodeAgent

    

    runCommand ("./util/killInstances.sh")

 

    runStartNodeAgent

 

End Sub

 

Sub uploadProperty()

 

    init

    

    Dim rows_selected

     

    For Each area In Selection.Areas

        For Each r In area.Rows

            If Not r.Row = 1 And Not r.Hidden Then

                rows_selected = r.Row & "," & rows_selected

            End If

        Next

    Next

     

    Rem MsgBox (rows_selected)

     

    Dim list_rows

    

    i = 3

    Do While i < 200

        If Not InStr(rows_selected, "," & i & ",") = 0 Or InStr(rows_selected, i & ",") = 1 Then

            Call Shell("""" & pscpPath & """" & " -i " & """" & privateKey & """ -p -r """ & Cells(i, lPathColNum) & """ " & Cells(i, acctColNum) & "@" & Cells(getServerRowNum(i), svrColNum) & ":""" & Cells(i, rPathColNum) & """", vbNormalFocus)

            Rem MsgBox ("""" & pscpPath & """" & " -i " & """" & privateKey & """ -p -r """ & Cells(i, lPathColNum) & """ " & Cells(i, acctColNum) & "@" & Cells(getServerRowNum(i), svrColNum) & ":""" & Cells(i, rPathColNum) & """")

        End If

        i = i + 1

    Loop

    

End Sub