' ExcelSpreadsheets-ReadingAndWriting.vbs
' 
' This example Script shows how to read (and write) data to (and from) an
' Excel spreadsheet using VBScript running within SecureCRT.
'
' This specific example uses an excel spreadsheet that has the
' following general format:
'          A       |   B   |     C    |     D    |     E     |    F    |  G   |    H    |
'   +--------------+-------+----------+----------+-----------+---------+------+---------+
' 1 | IP Address   | Port  | Protocol | Username | Password  | Active? | Date | Results | 
'   +--------------+-------+----------+----------+-----------+---------+------+---------+
' 2 | 192.168.0.1  | 22    | SSH2     | admin    | p4$$w0rd  |   Yes   | 1/11 |  Succ   |
'   +--------------+-------+----------+----------+-----------+---------+------+---------+
' 3 | 192.168.0.2  | 23    | Telnet   | root     | NtheCl33r |   No    | 1/11 |  Fail   |
'   +--------------+-------+----------+----------+-----------+---------+------+---------+
' 4 | 192.168.0.3  | 22    | SSH2     | root     | s4f3rN0w! |   Yes   | 1/11 |  Succ   |
'   +--------------+-------+----------+----------+-----------+---------+------+---------+
'
Dim g_shell
Set g_shell = CreateObject("WSCript.Shell")

Dim g_strMyDocs, g_strSpreadSheetPath
g_strMyDocs = g_shell.SpecialFolders("MyDocuments")
g_strSpreadSheetPath = g_strMyDocs & "\MyExcelData.xls"

Dim g_objExcel

Dim g_IP_COL, g_PORT_COL, g_USER_COL, g_PASS_COL, g_ACT_COL

' Convert Letter column indicators to numerical references
g_IP_COL           = Asc("A") - 64
g_PORT_COL         = Asc("B") - 64
g_PROTO_COL        = Asc("C") - 64
g_USER_COL         = Asc("D") - 64
g_PASS_COL         = Asc("E") - 64
g_ACT_COL          = Asc("F") - 64
g_ACT_DATE_COL     = Asc("G") - 64
g_ACT_RES_COL      = Asc("H") - 64


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Main()

    Set g_objExcel = CreateObject("Excel.Application")

    Dim objWkBook
    Set objWkBook = g_objExcel.Workbooks.Open(g_strSpreadSheetPath)
    
    Dim objSheet
    Set objSheet = objWkBook.Sheets(1)

    Dim nRowIndex
    ' Skip the header row.  If your sheet doesn't have a header row, change the
    ' value of nRowIndex to '1'.
    nRowIndex = 2
    
    ' Loop continues until we find an empty row
    Dim strIP, strPort, strProtocol, strUsername, strPassword, strActive
    Do
        ' If you find an empty value in column #1, exit the loop
        strIP = Trim(objSheet.Cells(nRowIndex, g_IP_COL).Value)
        If strIP = "" Then Exit Do
        
        strActive = Trim(objSheet.Cells(nRowIndex, g_ACT_COL).Value)
        'If Not Continue("Debug: Active(" & nRowIndex & ") = " & strActive) Then
        '    Exit Do
        'End If
            
        ' Connect to machines using the information we've gathered... only on
        ' condition of value in the g_ACT_COL column.
        If LCase(strActive) = "yes" Then
            strPort     = Trim(objSheet.Cells(nRowIndex, g_PORT_COL).Value)
            strProtocol = Trim(objSheet.Cells(nRowIndex, g_PROTO_COL).Value)
            strUsername = Trim(objSheet.Cells(nRowIndex, g_USER_COL).Value)
            strPassword = Trim(objSheet.Cells(nRowIndex, g_PASS_COL).Value)
            
            ' For debugging output (msgbox), uncomment the following group of
            ' lines:
            'If Not Continue("Debug: Here's the information from row #" & _
            '    nRowIndex & ":" & vbcrlf & _
            '    "Column #" & g_IP_COL & ": " & strIP & vbcrlf & _
            '    "Column #" & g_PORT_COL & ": " & strPort & vbcrlf & _
            '    "Column #" & g_USER_COL & ": " & strUsername & vbcrlf & _
            '    "Column #" & g_PASS_COL & ": " & strPassword) Then Exit Do
            
            If Not Connect(_
                strIP, _
                strPort, _
                strProtocol, _
                strUsername, _
                strPassword) Then
                
                ' Log an error, send e-mail?  For now, just mark it in the
                ' spreadsheet.
                objSheet.Cells(nRowIndex, g_ACT_RES_COL).Value  = _
                    "Failure: Unable to Connect"
            Else
                ' Heuristically determine the shell's prompt
                Do
                    ' Simulate pressing "Enter" so the prompt appears again...
                    crt.Screen.Send vbcr
                    ' Attempt to detect the command prompt heuristically by
                    ' waiting for the cursor to stop moving... (the timeout for
                    ' WaitForCursor above might not be enough for slower-
                    ' responding hosts, so you will need to adjust the timeout
                    ' value above to meet your system's specific timing
                    ' requirements).
                    Do
                        bCursorMoved = crt.Screen.WaitForCursor(1)
                    Loop Until bCursorMoved = False
                    ' Once the cursor has stopped moving for about a second,
                    ' we'll assume it's safe to start interacting with the
                    ' remote system. Get the shell prompt so that we can know
                    ' what to look for when determining if the command is
                    ' completed. Won't work if the prompt is dynamic (e.g.,
                    ' changes according to current working folder, etc.)
                    nRow = crt.Screen.CurrentRow
                    strPrompt = crt.screen.Get(nRow, _
                                               0, _
                                               nRow, _
                                               crt.Screen.CurrentColumn - 1)
                    ' Loop until we actually see a line of text appear:
                    strPrompt = Trim(strPrompt)
                    If strPrompt <> "" Then Exit Do
                Loop

                ' Issue command(s) to remote machine (modify according to your
                ' scenario) For example:
                crt.Screen.Send "sh config" & vbcr
                
                ' Check for success of command(s) (modify according your
                ' scenario), capturing the output of the command for storing in
                ' the excel spreadsheet.  Make sure that the success case is the
                ' first string in the array.
                vStringsToWaitFor = Array(_
                    strPrompt, _
                    "Type help or '?' for a list of available commands.")
                strResults = crt.Screen.ReadString(vStringsToWaitFor)
                
                ' Get the current line so that we can peel it off of from the
                ' results that were captured from the data sent by the remote.
                strCurLine = crt.Screen.Get(_
                    crt.Screen.CurrentRow, _
                    0, _
                    crt.Screen.CurrentRow, _
                    crt.Screen.CurrentColumn)
                strResults = Left(strResults, Len(strResults) - Len(strCurLine))
                
                If crt.Screen.MatchIndex = 1 Then bSuccess = True
                
                Set objCell = objSheet.Cells(nRowIndex, g_ACT_RES_COL)
                If bSuccess Then
                    objCell.Value  = "Success"
                    objCell.ClearComments
                    objCell.AddComment strResults
                    objCell.Comment.Shape.Width = 300
                    objCell.Comment.Shape.Height = 100
                Else
                    objCell.Value  = _
                        "Failure: Command failed. Matchindex = " & _
                        crt.Screen.MatchIndex
                    objCell.AddComment strResults
                    objCell.Comment.Shape.Width = 200
                    objCell.Comment.Shape.Height = 100
                End If
            End If
        Else
            ' mark the skipped ones in the spreadsheet
            objSheet.Cells(nRowIndex, g_ACT_RES_COL).Value  = "Skipped"
            
        End If
        
        ' We always record the date of action status
        objSheet.Cells(nRowIndex, g_ACT_DATE_COL).Value = Now
        
        ' move down to the next row in the spreadsheet
        nRowIndex = nRowIndex + 1
    Loop
    
    objWkBook.Save
    objWkBook.Close
        
    g_objExcel.Quit
    Set g_objExcel = Nothing
    
    g_shell.Run Chr(34) & g_strSpreadSheetPath & Chr(34)
    
End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Connect(strIP, strPort, strProtocol, strUsername, strPassword)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Workaround that uses "On Error Resume Next" VBScript directive to detect
' Errors that might occur from the crt.Session.Connect call and instead of
' closing the script, allow for error handling within the script as the
' script author desires.
    On Error Resume Next

    ' First disconnect if we're already connected.
    If crt.Session.Connected Then crt.Session.Disconnect

    g_strError = ""
    Err.Clear
    
    Dim strCmd
    Select Case UCase(strProtocol)
        Case "TELNET"
            strCmd = "/TELNET " & strIP & " " & strPort
            crt.Session.Connect strCmd
            crt.Screen.Synchronous = True
            
            If crt.Session.Connected <> True Then Exit Function
            
            ' Look for username and password prompts
            crt.Screen.WaitForString("ogin:")
            crt.Screen.Send strUsername & vbcr
            crt.Screen.WaitForString("ssword:")
            crt.Screen.Send strPassword & vbcr
            Connect = True
            
        Case "SSH2", "SSH1"
            strCmd = _
                " /" & UCase(strProtocol) & " " & _
                " /L " & strUsername & _
                " /PASSWORD " & strPassword & _
                " /P " & strPort & _
                " /ACCEPTHOSTKEYS " & _
                strIP
            crt.Session.Connect strCmd
            If crt.Session.Connected <> True Then Exit Function
            crt.Screen.Synchronous = True
            Connect = True
           
           
        Case Else
            ' Unsupported protocol
            g_strError = "Unsupported protocol: " & strProtocol
            Exit Function
    End Select
    
    If Err.Number <> 0 Then
        g_strError = Err.Description
    End If

    On Error Goto 0
End Function ' End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Continue(strMsg)
    Continue = True
    WScript.Sleep 200
    If msgBox(strMsg, vbYesno) <> vbYes Then Continue = False
    WScript.Sleep 400
End Function