' ExcelSpreadsheets-SearchingAndLookups.vbs
' 
' Questions about scripting in SecureCRT with VBScript?
' Consult the SecureCRT VBScript User Guide online:
'    https://www.vandyke.com/support/tips/scripting/index.html
'
' Description:
' ------------
' This example Script shows how to search for (look up) data
' within an Excel spreadsheet using VBScript running within
' SecureCRT, and read data from other cells within the row
' that contained the data that was found.
' 
' This specific example uses an excel spreadsheet that has the
' following general format:
'          A       |   B   |     C    |     D    |     E     |
'   +--------------+-------+----------+----------+-----------+
' 1 | IP Address   | Port  | Protocol | Username | Password  |
'   +--------------+-------+----------+----------+-----------+
' 2 | 192.168.0.1  | 22    | SSH2     | admin    | p4$$w0rd  |
'   +--------------+-------+----------+----------+-----------+
' 3 | 192.168.0.2  | 23    | Telnet   | root     | NtheCl33r |
'   +--------------+-------+----------+----------+-----------+
' 4 | 192.168.0.3  | 22    | SSH2     | root     | s4f3rN0w! |
'   +--------------+-------+----------+----------+-----------+
' ... and so on
' 
'   - A User is prompted for text to search for (in Column "A", as represented
'     by the 'strSearchFor' variable in Sub Main()).
'     
'   - When the user provides a search term, the workbook specified in the
'     'g_strWkBkPath' variable is opened using Excel COM object API calls, and
'     the first sheet in the workbook is searched for the term provided by
'     calling the Lookup() function.  See the Lookup() function definition below
'     for more details on what parameters are needed, etc.
'     
'   - If the user-provided text is found in column "A" of the first spreadsheet
'     in the workbook, the Lookup() function returns as its value the number of
'     the row on which the text was found, and an array is populated with the
'     column values for that row (up to a specified column passed to the Lookup
'     function.)  If the user-provided text is not found, the user is notified
'     and allowed to enter another search term.
'     
'   - Using the information from the array, another function is
'     called: DoWorkWithRowData().  In this example, this function:
'        - Connects to the remote machine using the information in the vRowData
'          array that it's provided,
'        - Detects the shell prompt automatically using the GetShellPrompt()
'          function defined below,
'        - Runs a command on the remote machine,
'        - Captures the output of the command within a script variable using the
'          Screen.ReadString method, and
'        - Displays the results of the command within a popup message box.
'     
'   - Once a search has been done, and the appropriate work has been done in the
'     DoWorkWithRowData() function, the script loops back up to prompting the
'     user for a search term until the search prompt is cancelled.

Dim g_shell
Set g_shell = CreateObject("WSCript.Shell")

Dim g_objExcel
Set g_objExcel = Nothing

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

' g_strError is a global variable that is used to store error messages
' within called functions so that the error messages are available to
' other (calling) functions or subroutines.
Dim g_strError

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

    ' Loop, prompting for input until either the user cancels, or until
    ' a match is found within the spreadsheet.
    Dim strSearchFor, strSearchCol
    strSearchFor = "192.168.0.3"
    
    ' Specify the column in which the data you're searching for is expected
    ' to be found
    strSearchCol = "A"
    
    Do
        ' Prompt user for data to find
        strSearchFor = crt.Dialog.Prompt(_
            "Specify what to look for", _
            "Search Spreadsheet For...", _
            strSearchFor)
        
        ' Check for "cancel"
        If strSearchFor = "" Then Exit Do
        
        Dim nRowFound, vData
        ' Call routine to load spreadsheet and look for 'strSearchFor' text
        ' in 'strSearchCol' and retrieve corresponding row data into
        ' vRowData.
        ' Note: When calling this Lookup function, you should remember to
        '       change the last parameter to match the letter that represents
        '       the last (right-most) column of data that you need to be
        '       included as part of the vRowData that is returned when a match
        '       is found.  In this example, the last column of data in the
        '       Excel spreadsheet that is needed is "E".  If the last data
        '       you care about in your spreadsheet is "F", for example, make
        '       sure you change the "E" below to an "F".
        nRow = Lookup(g_strWkBkPath, strSearchFor, strSearchCol, vRowData, "E")
        
        ' Check the return value to see if a match was found on any of the
        ' rows within the search column:
        If nRow = 0 Then
            crt.Dialog.MessageBox _
                """" & strSearchFor & """ was not found in column " & _
                """" & strSearchCol & """ in the specified spreadsheet."
        Else
            ' Now that an array exists with all the data on the row that
            ' was found with the search term, call a function to do the
            ' necessary work based on the data on that row...
            DoWorkWithRowData(vRowData)
            
        End If
        
    Loop
    
    ' Close Excel (only if it was ever opened)
    If Not g_objExcel Is Nothing Then g_objExcel.Quit
    
End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub DoWorkWithRowData(vRowData)
    ' This function is responsible for doing all the work that was needed
    ' to be done with the data on the row that was found in the search.

    ' Use data from the array for connecting to the remote system. Make sure
    ' that you are only using Colum specifications that are available to you
    ' within the array that was returned from the Lookup() function. Remember
    ' that the Lookup function is provided a parameter that indicates the last
    ' column of data that the script will extract from the matching row.  In
    ' this example, "E" is the last column of data the Lookup() function was
    ' told to extract.
    ' 
    ' If you need to access rows beyond "E", you must make sure that you've
    ' called the Lookup() function in Main() above with the appropriate column
    ' specification for the last column of data you need from the spreadsheet's
    ' matching row.  For example, if you need access to data in column "AC", you
    ' must have already called Lookup() earlier with a last column argument of
    ' "AC".  Otherwise, attempting to access the data you need by calling
    ' vRowData(Col("AC")) will result in a "Subscript out of range" error.
    strIP = vRowData(Col("A"))
    strPort = vRowData(Col("B"))
    strProtocol = vRowData(Col("C"))
    strUsername = vRowData(Col("D"))
    strPassword = vRowData(Col("E"))
    
    ' Connect to the remote machine using the information from the array
    ' provided by the Lookup() function:
    If Not Connect(_
        strIP, _
        strPort, _
        strProtocol, _
        strUsername, _
        strPassword) Then
        
        ' Log an error, send e-mail?  For now, just display the connection
        ' error in a msgbox
        crt.Dialog.MessageBox "Failure: " & g_strError
    Else
        ' Use the GetShellPrompt() function defined below within this script
        ' to automatically detect the shell prompt using a heuristic as
        ' demonstrated in a couple of example script solutions in the SecureCRT
        ' VBScript Guide:
        '   https://www.vandyke.com/support/tips/scripting/index.html
        strPrompt = GetShellPrompt()
        
        ' Turn off "---More---" prompts on the Cisco device.  Note that if
        ' you're connecting to a PIX firewall or other device, you'll likely
        ' need to issue a different command ('pager 0', or something similar):
        crt.Screen.Send "term len 0" & vbcr
        crt.Screen.WaitForString strPrompt
        
        ' Issue command(s) to remote machine (modify according to your scenario)
        ' For example:
        crt.Screen.Send "sh term" & vbcr
        
        ' Read data received until the prompt is displayed once again, storing
        ' text results in 'strResults' variable:
        strResults = crt.Screen.ReadString(strPrompt)
        
        crt.Dialog.MessageBox _
            "Here are the results:" & vbcrlf & vbcrlf & _
            strResults
        
        crt.Session.Disconnect
        
    End If

End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Lookup(strWkBkPath, _
                strSearchFor, _
                strColumnToSearch, _
                ByRef vRowDataArray, _
                nLastColData)
    ' Looks in spreadsheet found in strSpreadSheetPath for text held
    ' in strSearchFor and stores all the data in the found row in
    ' vRowDataArray which is accessible by caller afterwards
    ' 
    '       strWkBkPath:  String w/the full path to the Excel spreadsheet that
    '                     contains the data you're searching for.
    '                     
    ' strColumnToSearch:  String containing the range specification for the
    '                     area of the spreadsheet you expect the data to be
    '                     found.  For example, if you're keying off of column
    '                     'A' in your spreadsheet, you would pass in "A".
    '                     
    '     strLookupText:  String variable containing the text you're searching
    '                     for within the spreadsheet. For example: "192.168.0.1"
    '
    '     vRowDataArray:  Reference to a variable that will become an array
    '                     if this function successfully finds the search term
    '                     provided.
    '                     
    '      nLastColData:  String variable representing the column reference
    '                     that indicates the last column of the row that you
    '                     want returned as part of the data array representing
    '                     the columns on the row that was found. For examle, if
    '                     your spreadsheet has data up to column 'Y' you would
    '                     pass in "Y".
    ' 
    ' Returns row number on which data was found.
    '    If nothing was found, returns zero (which works great since in an Excel
    '    since there is no such thing as Row #0)
    
    ' First, load up the spreadsheet
    If g_objExcel Is Nothing Then
        On Error Resume Next
        Set g_objExcel = CreateObject("Excel.Application")
        nError = Err.Number
        strErr = Err.Description
        On Error Goto 0
        
        If nError <> 0 Then
            crt.Dialog.Prompt _
                "Error: " &  strErr & vbcrlf & _
                vbcrlf & _
                "Perhaps you need MS Excel to be installed or repaired."
            Exit Function
        End If
    End If
    
    ' Now load the workbook (Read-Only), and get a reference to the first sheet
    On Error Resume Next
    Set objWkBk = g_objExcel.Workbooks.Open(strWkBkPath, 0, True)
    nError = Err.Number
    strErr = Err.Description
    On Error Goto 0
    
    If nError <> 0 Then
        g_objExcel.Quit
        crt.Dialog.MessageBox _
            "Error loading spreadsheet """ & strWkBkPath & """:" & vbcrlf & _
            vbcrlf & _
            strErr
        Exit Function
    End If
    
    Set objSheet = objWkBk.Sheets(1)
    
    ' Look in specified column for the given data:
    Set objSearchRange = objSheet.Columns(strColumnToSearch)
    Set objFoundRange = Nothing
    Set objFoundRange = objSearchRange.Find(strSearchFor)
    If Not objFoundRange Is Nothing Then
        Lookup = objFoundRange.Row
        ReDim vRowDataArray(Col(nLastColData))
        For nColIndex = 1 To Col(nLastColData)
            vRowDataArray(nColIndex) = objFoundRange.Cells(1, nColIndex).Value
        Next
    Else
        Lookup = 0
        Exit Function
    End If
End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Col(strLetterRef)
    ' Converts any Excel letter combination into its corresponding column
    ' number. For example "A" is column #1, C is col #3, AA is column #27, etc.
    '
    '   strLetterRef: a string indicating which Excel Column (by letters)
    '                 needs to be converted to a number.  For example "AA".
    '                 Lower-case values are accepted.
    
    Dim nColumnValue
    nColumnValue = 0

    ' Performing base 26 arithmetic...
    For nLetterIndex = Len(strLetterRef) To 1 Step -1
        ' nMultiplier is the number of times 26 (number of letters in English
        ' alphabet) is multiplied by the ASCII value of the current letter.
        nMultiplier = Len(strLetterRef) - nLetterIndex
        
        ' nCurLetter is the ASCII value of the current letter being worked on
        ' (right->left since the right-most letters represent lower-significance
        ' values)
        nCurLetter = ASC(UCase(Mid(strLetterRef, nLetterIndex, 1))) - 64
        nColumnValue = nColumnValue + (nCurLetter * (26 ^ nMultiplier))
    Next
    
    Col = nColumnValue
End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Connect(strIP, strPort, strProtocol, strUsername, strPassword)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ' First disconnect if SecureCRT is already connected.
    If crt.Session.Connected Then crt.Session.Disconnect

    g_strError = ""
    
    Dim strCmd
    Select Case UCase(strProtocol)
        Case "TELNET"
            strCmd = "/TELNET " & strIP & " " & strPort
            ' 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
            crt.Session.Connect strCmd
            nError = Err.Number
            g_strError = Err.Description
            On Error Goto 0
            
            If crt.Session.Connected <> True Then Exit Function
            
            crt.Screen.Synchronous = True
            crt.Screen.IgnoreEscape = True
            
            ' 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
                
            ' 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
            crt.Session.Connect strCmd
            nError = Err.Number
            g_strError = Err.Description
            On Error Goto 0
            
            If crt.Session.Connected <> True Then Exit Function

            crt.Screen.Synchronous = True
            crt.Screen.IgnoreEscape = True
            
            Connect = True
           
        Case Else
            ' Unsupported protocol
            g_strError = "Unsupported protocol: " & strProtocol
            Exit Function
    End Select
    
    On Error Goto 0
End Function ' End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function GetShellPrompt()
    ' Heuristically dermine the shell's prompt.  Crt.Screen.Synchronous must
    ' already have been set to True.  In general, Crt.Screen.Synchronous should
    ' be set to True immediately after a successful crt.Session.Connect().  In
    ' This script, SecureCRT should already be connected -- otherwise, a script
    ' error will occur.
    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, it's assumed
        ' it's safe to start interacting with the remote system. Get the shell
        ' prompt so that it's known 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 a line of non-whitespace text actually appears:
        strPrompt = Trim(strPrompt)
        If strPrompt <> "" Then Exit Do
    Loop
    
    GetShellPrompt = strPrompt
    
End Function