Welcome to the VanDyke Software Forums

Join the discussion today!


Go Back   VanDyke Software Forums > Scripting

Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 4.50 average. Display Modes
  #1  
Old 08-17-2010, 02:18 PM
olivebranch2006 olivebranch2006 is offline
Registered User
 
Join Date: Apr 2010
Posts: 35
Question Using ReadString output for Logfile name

Hello,

I am trying to use the hostname of a Cisco switch to name a logfile by reading up to the # symbol with ReadString. The Readstring occurs after I enter the username, password and vbcr. Here is the code:

#$language = "VBScript"
#$interface = "1.0"
' This is a Visual Basic Script written for use in SecureCRT.
' The goal in this script is to automate the copying of switch configurations for a set of ip addresses provided by the user.
' Author: Christo Michaelides
' Email: cmichaelides@ams.net
' Phone: (925) 245-6174
' Date: August 12, 2010
' Mood: Serene

crt.Screen.Synchronous = True

Dim IPAddresses, IPArray, a, logfile, hostname
'Dim szvlans, vVlanArray, hostname, staffrange, studentrange, voicevlan, studentvlan, aprange, grange, trange, mgmtip, asset, model, mediatype

Sub Main

'Enable Privileged Mode
crt.Screen.Send "Enable" & chr(13)
crt.screen.waitforstring "#"

'Ask User for IP Addresses
IPAddresses = crt.Dialog.Prompt("Enter IP Addresses:", _
"Enter Seperated by Comma", _
"10.3.99.2,10.3.99.3,10.3.99.10")
IPArray = split(IPAddresses, ",")
For a = 0 to Ubound(IPArray)

'For each strLine In IPArray
hostname = ""
crt.Session.LogFileName = ""
crt.screen.send "telnet " & IPArray(a) & vbCr
crt.screen.waitforstring "Username:"
crt.screen.send "******" & vbCr
crt.Screen.WaitForString vbcr
crt.screen.waitforstring "Password:"
crt.screen.send "******" & vbCr
crt.Screen.WaitForString vbCr, 3
crt.sleep 1000
hostname = crt.Screen.ReadString("#")
MsgBox hostname
' crt.Screen.Send "terminal length 0" & vbcr
crt.sleep 1000
logfile = ("C:\" & hostname & ".txt")
MsgBox logfile
crt.sleep 1000
crt.Session.LogFileName = logfile
' crt.Session.Log True
' crt.Screen.Send "show run" & vbcr
' crt.Screen.WaitForString "hostname"
' crt.Screen.WaitForString "ntp server"
' crt.Screen.WaitForString "end"
' crt.Screen.WaitForString "#"
' crt.Session.Log False
' crt.Session.LogFileName = ""
' crt.Screen.Send "terminal no length" & vbcr
' hostname = ""

Next
'Alert User the configurations are logged
MsgBox "Configurations have been logged to c:\ with the switch hostname."

End Sub

I have marked over the username and password for security reasons and obviously the commented out lines are how far I've troubleshoot the issue. The issue is a square box symbol showing up in the logfile location. When I try to run "crt.Session.Log True" the script fails because the location and file name is invalid. I have checked the location by looking at session options after running this script. The MsgBox lines are for my troubleshooting... I suspect the boxes are CRs because this is what the log file location looks like after pasting:

C:\

ST-MDF-Rm100-3750Stack.txt

Any ideas how to get ReadString to ignore CRs if these are the square symbols?

I've attached the screenshot of the symbols.

Thanks!

Christo
Attached Images
File Type: jpg scriptSS.JPG (128.8 KB, 1088 views)
Reply With Quote
  #2  
Old 08-17-2010, 03:25 PM
rtb rtb is offline
VanDyke Technical Support
 
Join Date: Aug 2008
Posts: 4,306
Hi Christo,

Thanks for the question. It will be necessary to use some method to extract only the data you need (Screen object's Get method, regular expression), or determine what the characters are.

To determine what the characters are, you could write the data to a file and open the file in a text editor that has the ability to display non-printable characters.

Another option would be to just try replacing the bad characters.

For example:
Code:
hostname = Replace(crt.Screen.ReadString("#"), vbCR, "")
Do any of these options help you fix your script?
__________________
--Todd

VanDyke Software
Technical Support
support@vandyke.com
505-332-5730

Last edited by rtb; 03-14-2012 at 11:03 AM.
Reply With Quote
  #3  
Old 08-17-2010, 05:47 PM
jdev's Avatar
jdev jdev is offline
VanDyke Technical Support
 
Join Date: Nov 2003
Location: Albuquerque, NM
Posts: 1,098
Here's some more tips in addition to what Todd mentioned.

The block of code in your current script that is responsible for attempting to grab the hostname of the secondary machine to which you've connected with the 'telnet' command is as follows:

Code:
crt.screen.waitforstring "Password:"
crt.screen.send "******" & vbCr
crt.Screen.WaitForString vbCr, 3
crt.sleep 1000
hostname = crt.Screen.ReadString("#")
MsgBox hostname
Using crt.Sleep here is probably an attempt at timing the subsequent ReadString() call to avoid getting any of the "extra" stuff that might appear between the time that you submit your password, and when the prompt (#) appears. Unfortunately, it probably won't work to try and time the Read since with Synchronous = True, all data received after a prior call to ReadString(), Send(), SendSpecial(), SendKeys(), WaitForString(), WaitForKey(), WaitForCursor(), WaitForStrings() is considered and looked at for matching the text you instructed SecureCRT to look for.

Instead of trying to time it (timing is always different, and tricky, at best), it is suggested that you simply wait for the "#" to appear. Then, you can simulate pressing "Enter" so that the prompt shows up on its own line. I'd wait for the carriage return to be echoed (as a result of "pressing" <Enter>, and then I'd do a ReadString("#"), which would likely get me just the prompt (which in your case contains the host name).

Here's a sample replacement for the block I've included above:
Code:
crt.Screen.WaitForString "Password:"
crt.Screen.Send "******" & vbcr
crt.Screen.WaitForString "#"

' Now "press" the <enter> key...
crt.Screen.Send vbcr
' And then wait for the <enter> to result in a CR echoed back:
crt.Screen.WaitForString vbcr

' Now, we can probably just "Read" up to the point that a "#" appears and it
' should contain just the hostname:
hostname = crt.Screen.ReadString("#")

MsgBox hostname
What Todd said about there being an opportunity for extra (non-valid as a file name in Windows) characters being stored as part of the variable is still valid; you'll want to perhaps even run through saving a raw log of doing it manually (no script involved), or perhaps even just start with the SecureCRT script recorder and record a script of how you would do this manually so that you can see exactly what it is that the remote is sending after you successfully telnet to another machine.

If it turns out that the "blocks" you are seeing aren't CR or LF characters (and instead they are ESC characters), you can set the crt.Screen.IgnoreEscape property to True as explained in section 4.2 (sub-section titled, "Ignoring Escape Codes (Or Not)") of the SecureCRT Scripting Manual. The IgnoreEscape property works the same way with ReadString() as it does with WaitForString() and WaitForStrings().

When you get the success case worked out, you may want to revisit your code surrounding the entire "telnet <secondary host>" command. For example, what does a failure look like, and what would you want to do to handle the case where the connection associated with your telnet command fails, or the authentication fails, etc.? In an ideal world, we'd always successfully connect to all the servers we tried, but contrary to a common phrase/cliche one hears in many movies, "Failure is *always* an option" .

Oh, I almost forgot... another trick one can use to detect the shell prompt heuristically is to wait for the cursor to stop moving. This technique shows up in a couple of places within example code in the scripting manual as well (Sections 7.2, and 8.2). Using this technique instead of the one I've shown above, you'd replace the original block of code I mentioned first, with the following block:
Code:
crt.Screen.WaitForString "Password:"
crt.Screen.Send "******" & vbcr
Do    
    ' Attempt to detect the command prompt heuristically...
    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 (the
    ' timeout for WaitForCursor above might not be enough
    ' for slower-responding hosts.
    strPrompt = Trim(strPrompt)
    If strPrompt <> "" Then Exit Do
Loop

MsgBox Hostname
The "WaitForCursor" technique can help in tricky situations where some devices might have a "#" for the ending prompt character, but other devices might display a ">", etc.

Hope this additional information helps.

--Jake
__________________
Jake Devenport
VanDyke Software
Technical Support
YouTube Channel: https://www.youtube.com/vandykesoftware
Email: support@vandyke.com
Web: https://www.vandyke.com/support
Reply With Quote
  #4  
Old 08-17-2010, 11:58 PM
olivebranch2006 olivebranch2006 is offline
Registered User
 
Join Date: Apr 2010
Posts: 35
Still broken

Hello Jake,

I appreciate both of your responses and just tested your revised script. The same result came from this test and you will find screen shots attached. Here is the new code:

#$language = "VBScript"
#$interface = "1.0"


crt.Screen.Synchronous = True

Dim IPAddresses, IPArray, a, logfile, hostname
'Dim szvlans, vVlanArray, hostname, staffrange, studentrange, voicevlan, studentvlan, aprange, grange, trange, mgmtip, asset, model, mediatype

Sub Main

'Enable Privileged Mode
crt.Screen.Send "Enable" & chr(13)
crt.screen.waitforstring "#"

'Ask User for IP Addresses
IPAddresses = crt.Dialog.Prompt("Enter IP Addresses:", _
"Enter Seperated by Comma", _
"10.3.99.2,10.3.99.3,10.3.99.10")
IPArray = split(IPAddresses, ",")
For a = 0 to Ubound(IPArray)

'For each strLine In IPArray
hostname = ""
crt.Session.LogFileName = ""
crt.screen.send "telnet " & IPArray(a) & vbCr
crt.screen.waitforstring "Username:"
crt.screen.send "******" & vbCr
crt.screen.waitforstring "Password:"
crt.screen.send "******" & vbCr
crt.Screen.WaitForString "#"
crt.Screen.Send vbCr
crt.Screen.WaitForString vbCr
hostname = crt.Screen.ReadString("#")
MsgBox hostname
' crt.Screen.Send "terminal length 0" & vbcr
logfile = ("C:\" & hostname & ".txt")
MsgBox logfile
crt.Session.LogFileName = logfile
' crt.Session.Log True
' crt.Screen.Send "show run" & vbcr
' crt.Screen.WaitForString "hostname"
' crt.Screen.WaitForString "ntp server"
' crt.Screen.WaitForString "end"
' crt.Screen.WaitForString "#"
' crt.Session.Log False
' crt.Session.LogFileName = ""
' crt.Screen.Send "terminal no length" & vbcr
' hostname = ""

Next
'Alert User the configurations are logged
MsgBox "Configurations have been logged to c:\ with the switch hostname."

End Sub

As you can see from the picture It appears to be a CR before the hostname. Any ideas? I'm at a loss as to what generates the character.

Thanks,

Christo
Attached Images
File Type: jpg TestedScript.JPG (78.6 KB, 1134 views)
File Type: jpg TestedScript2.JPG (114.9 KB, 1013 views)
Reply With Quote
  #5  
Old 08-18-2010, 09:23 AM
jdev's Avatar
jdev jdev is offline
VanDyke Technical Support
 
Join Date: Nov 2003
Location: Albuquerque, NM
Posts: 1,098
Did you try the alternate replacement code example I suggested to heuristically determine the prompt?

Did you try recording a script (to a different script file, of course -- you don't want to overwrite what you already have) as I suggested as one way of seeing exactly what the remote was sending?
For example:
  • Log on to your jump host manually.
  • Right before you type in the telnet command to go to another host, start the SecureCRT script recorder (Script / Start Recording Script).
  • Once the script recorder has been started (Once the script recorder has started, the Script menu will enable the Stop Recording Script... menu item as well as its Cancel... counterpart), type in the telnet <secondary_host> command to attempt the connection to the secondary host machine.
  • When prompted for login credentials, authenticate, and...
  • When successfully authenticated and you see the new shell prompt is being displayed (with the secondary host's name in it), press the <Enter> key once (so that the prompt shows up again).
  • Stop the SecureCRT script recorder (Script / Stop Recording Script...).
  • Save the recorded script to a file name of your choosing (for example, "C:\Temp\MyRecordedScriptTest.vbs")
  • Open it up in notepad or your favorite script editor, and look at the last part of your script code; right before the last crt.Screen.Send(), you'll see a crt.Screen.WaitForString that will contain the crucial elements that the remote is sending. If you see a chr(10) somewhere, it's an LF character; if you see a chr(13) somewhee, it's a CR character, and so on.
Seeing the recorded script line that occurrs after your password is sent should help you know exactly what is sent by the remote up to the point that your prompt line appears.
--Jake
__________________
Jake Devenport
VanDyke Software
Technical Support
YouTube Channel: https://www.youtube.com/vandykesoftware
Email: support@vandyke.com
Web: https://www.vandyke.com/support
Reply With Quote
  #6  
Old 08-18-2010, 10:55 AM
olivebranch2006 olivebranch2006 is offline
Registered User
 
Join Date: Apr 2010
Posts: 35
What I tried

Hello Jake,

I did not try the heuristic code because our environment always has the same prompt symbol of #

Here is what the script recording showed:
#$language = "VBScript"
#$interface = "1.0"

crt.Screen.Synchronous = True

' This automatically generated script may need to be
' edited in order to work correctly.

Sub Main
crt.Screen.Send chr(13)
crt.Screen.WaitForString "ST-MDF-Rm100-4900m-1#"
crt.Screen.Send "telnet 10.70.254.2" & chr(13)
crt.Screen.WaitForString "Username: "
crt.Screen.Send "******" & chr(13)
crt.Screen.WaitForString "Password: "
crt.Screen.Send "******" & chr(13)
crt.Screen.WaitForString "ST-MDF-Rm100-3750Stack#"
crt.Screen.Send chr(13)
End Sub

I then changed the script to use chr(13) instead of vbcr. Here is the altered script:

#$language = "VBScript"
#$interface = "1.0"
' This is a Visual Basic Script written for use in SecureCRT.
' The goal in this script is to automate the copying of switch configurations for a set of ip addresses provided by the user.
' Author: Christo Michaelides
' Email: cmichaelides@ams.net
' Phone: (925) 245-6174
' Date: August 12, 2010
' Mood: Serene

crt.Screen.Synchronous = True

Dim IPAddresses, IPArray, a, logfile, hostname
'Dim szvlans, vVlanArray, hostname, staffrange, studentrange, voicevlan, studentvlan, aprange, grange, trange, mgmtip, asset, model, mediatype

Sub Main

'Enable Privileged Mode
crt.Screen.Send "Enable" & chr(13)
crt.screen.waitforstring "#"

'Ask User for IP Addresses
IPAddresses = crt.Dialog.Prompt("Enter IP Addresses:", _
"Enter Seperated by Comma", _
"10.3.99.2,10.3.99.3,10.3.99.10")
IPArray = split(IPAddresses, ",")
For a = 0 to Ubound(IPArray)

'For each strLine In IPArray
hostname = ""
crt.Session.LogFileName = ""
crt.screen.send "telnet " & IPArray(a) & Chr(13)
crt.screen.waitforstring "Username:"
crt.screen.send "******" & Chr(13)
crt.screen.waitforstring "Password:"
crt.screen.send "******" & Chr(13)
crt.Screen.WaitForString "#"
crt.Screen.Send Chr(13)
crt.Screen.WaitForString Chr(13)
hostname = crt.Screen.ReadString("#")
MsgBox hostname
' crt.Screen.Send "terminal length 0" & vbcr
logfile = ("C:\" & hostname & ".txt")
MsgBox logfile
crt.Session.LogFileName = logfile
' crt.Session.Log True
' crt.Screen.Send "show run" & vbcr
' crt.Screen.WaitForString "hostname"
' crt.Screen.WaitForString "ntp server"
' crt.Screen.WaitForString "end"
' crt.Screen.WaitForString "#"
' crt.Session.Log False
' crt.Session.LogFileName = ""
' crt.Screen.Send "terminal no length" & vbcr
' hostname = ""

Next
'Alert User the configurations are logged
MsgBox "Configurations have been logged to c:\ with the switch hostname."

End Sub


The script produces the same problem. The new screenshot shows the same C:\ with the hostname.txt a line below it.

What is the difference between vbcr and chr(13)?

Thanks,

Christo
Attached Images
File Type: jpg scriptSS-2.JPG (133.0 KB, 1029 views)
Reply With Quote
  #7  
Old 08-18-2010, 02:44 PM
jdev's Avatar
jdev jdev is offline
VanDyke Technical Support
 
Join Date: Nov 2003
Location: Albuquerque, NM
Posts: 1,098
Quote:
Originally Posted by olivebranch2006
I did not try the heuristic code because our environment always has the same prompt symbol of #
I didn't mean to imply that you can't use the heuristic code if your prompt is always designated by a "#" character -- I wouldn't have suggested it I didn't think it would work for you .

Why don't you try out the heuristic code block as explained and see how it works for you?

If the heuristic code doesn't work for you, I'd play with the sleep value passed to the WaitForCursor to allow for some more time (say use a 1 instead of a 2).

If you're interested in having your script operate as quickly as possible, without the 1-2 second delay introduced by the heuristic way of waiting for the cursor to stop moving, read below for a suggestion on how you can determine what non-printing characters are showing up from the remote system as a result of ReadString().

Quote:
Originally Posted by olivebranch2006
What is the difference between vbcr and chr(13)?
There isn't any difference between vbcr and chr(13); vbcr is a built-in VBScript constant that means exactly the same thing as chr(13) (but vbcr is easier to type, in my opinion) just as vblf is a built-in constant that means exactly the same thing as chr(10), and vbtab is the same as chr(9), etc.


With respect to your results with the script recorder, unfortunately I failed to recognize that the script recorder works "too well" -- the script recorder ignores extra lines received, and only Waits for the line received immediately prior to performing any Sends(), so sorry for the extra work.

Looks like you may need to get a raw log of doing the telnet logon manually so that you can see from the raw log file what it is that has been received.

Alternatively, you can insert the block of code below at the very end of your script (after your Main's End statement and then use the provided RevealNonPrintingCharacters() function to show you what the non-printing characters are that ReadString is capturing. With the below RevealNonPrintingCharacters() function in place, you can pass the results of the ReadString through this function to reveal what each non-printing character is. For example you would replace your current MsgBox hostname with MsgBox RevealNonPrintingCharacters(hostname)

Once you know what non-printing character(s) are showing up, you can either wait for them to pass using WaitForString() prior to using ReadString("#") to capture the prompt, or you can use the Replace() function to remove them from your hostname variable. For example, if the msgbox that appears shows you that there's an "[LF]" character in the hostname string, you can remove it using the following line of code: hostname = Replace(hostname, vblf, "")

Here's the code to append to the end of your current script file's code if you decide to go this route:
Code:
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function RevealNonPrintingCharacters(strText)

    Set colCharNames = CreateObject("Scripting.Dictionary")
    colCharNames.Add 0, "[NUL]"
    colCharNames.Add 1, "[SOH]"
    colCharNames.Add 2, "[STX]"
    colCharNames.Add 3, "[ETX]"
    colCharNames.Add 4, "[EOT]"
    colCharNames.Add 5, "[ENQ]"
    colCharNames.Add 6, "[ACK]"
    colCharNames.Add 7, "[BEL]"
    colCharNames.Add 8, "[BS]"
    colCharNames.Add 9, "[HT]"
    colCharNames.Add 10, "[LF]"
    colCharNames.Add 11, "[VT]"
    colCharNames.Add 12, "[FF]"
    colCharNames.Add 13, "[CR]"
    colCharNames.Add 14, "[SO]"
    colCharNames.Add 15, "[SI]"
    colCharNames.Add 16, "[DLE]"
    colCharNames.Add 17, "[DC1]"
    colCharNames.Add 18, "[DC2]"
    colCharNames.Add 19, "[DC3]"
    colCharNames.Add 20, "[DC4]"
    colCharNames.Add 21, "[NAK]"
    colCharNames.Add 22, "[SYN]"
    colCharNames.Add 23, "[ETB]"
    colCharNames.Add 24, "[CAN]"
    colCharNames.Add 25, "[EM]"
    colCharNames.Add 26, "[SUB]"
    colCharNames.Add 27, "[ESC]"
    colCharNames.Add 28, "[FS]"
    colCharNames.Add 29, "[GS]"
    colCharNames.Add 30, "[RS]"
    colCharNames.Add 31, "[US]"
    For nIndex = 32 To 126
        colCharNames.Add nIndex, chr(nIndex)
    Next
    colCharNames.Add 127, "[DEL]"
    
    strRevealingText = ""
    For nPos = 1 To Len(strText)
        ' Get the current character (we're working left->right from the first
        ' character of the string to the last charcter of the string):
        strCurrentChar = Mid(strText, nPos, 1)
        
        ' Map the current character to a printable sequence (using either the
        ' actual character (if printable), or a substitute as defined in the
        ' colCharNames collection above:
        strRevealingText = strRevealingText & colCharNames(ASC(strCurrentChar))
        
    Next
    
    ' Tidy things up a bit so that they'll appear more "normalized" in the
    ' messagebox
    strRevealingText = Replace(strRevealingText, "[CR][LF]", "[CRLF]" & vbcrlf)
    strRevealingText = Replace(strRevealingText, "[CR]", "[CR]" & vbcr)
    strRevealingText = Replace(strRevealingText, "[LF]", "[LF]" & vblf)
    strRevealingText = Replace(strRevealingText, "[HT]", "[HT]" & vbtab)
    
    RevealNonPrintingCharacters = strRevealingText
End Function
--Jake
__________________
Jake Devenport
VanDyke Software
Technical Support
YouTube Channel: https://www.youtube.com/vandykesoftware
Email: support@vandyke.com
Web: https://www.vandyke.com/support

Last edited by jdev; 10-13-2016 at 09:04 AM. Reason: Updated script code for RevealNonPrintingCharacters
Reply With Quote
  #8  
Old 08-31-2010, 11:33 AM
olivebranch2006 olivebranch2006 is offline
Registered User
 
Join Date: Apr 2010
Posts: 35
Smile Trying the code

Sorry for the delay, I've been slammed with other projects. Trying your reveal hidden characters code now.
Reply With Quote
  #9  
Old 08-31-2010, 11:45 AM
olivebranch2006 olivebranch2006 is offline
Registered User
 
Join Date: Apr 2010
Posts: 35
Thumbs up It is [LF]

Your guess was spot-on because the function revealed [LF]. After inserting:
crt.Screen.WaitForString [LF]
before
hostname = crt.Screen.ReadString("#")
The log file name is now exactly how I want it... Thanks again! I'll be testing this script on multiple end devices now.
Reply With Quote
  #10  
Old 08-31-2010, 11:58 AM
jdev's Avatar
jdev jdev is offline
VanDyke Technical Support
 
Join Date: Nov 2003
Location: Albuquerque, NM
Posts: 1,098
Glad to hear that the culprit character has been identified and that your script is working as desired now.

Thank you for the report.

--Jake
__________________
Jake Devenport
VanDyke Software
Technical Support
YouTube Channel: https://www.youtube.com/vandykesoftware
Email: support@vandyke.com
Web: https://www.vandyke.com/support
Reply With Quote
  #11  
Old 08-31-2010, 01:01 PM
olivebranch2006 olivebranch2006 is offline
Registered User
 
Join Date: Apr 2010
Posts: 35
Lightbulb An addition

Hello,

I forgot about an addition to this script. Is there any way to use a column from an excel file to pull the ip addresses from?

Thanks
Reply With Quote
  #12  
Old 09-01-2010, 01:49 PM
jdev's Avatar
jdev jdev is offline
VanDyke Technical Support
 
Join Date: Nov 2003
Location: Albuquerque, NM
Posts: 1,098
Quote:
Originally Posted by olivebranch2006
Is there any way to use a column from an excel file to pull the ip addresses from?
Here are the basic steps:
  1. Determine where the spreadsheet (.xls) file is located on the system.
  2. Create an Excel Application object.
  3. Open/Load the workbook that corresponds to the .xls file.
  4. Get a reference to the Sheet object that has the data you need.
  5. Use the Sheet object's Cells() or Range() methods to extract data from the sheet.
In more detail, it looks like this:
  1. Determine where the spreadsheet (.xls) file is located on the system.
    This can look as simple as hard-coding a string, for example:
    Code:
    strPath = "C:\Users\MyUsername\Documents\MyExcelData.xls"
    However, I personally prefer to use some of the VBScript capabilities that allow for paths to work regardless of the user account. The spreadsheet .xls file still must be in place -- that's a given -- but it's nice not to have to edit the script file just because I'm running the script as user "bob" instead of user "robert". For example, I can easily ask the system to provide me with the path the current user's "My Documents" folder without hard-coding any part of the path:
    Code:
    Set g_shell = CreateObject("WScript.Shell")
    g_strMyDocs = g_shell.SpecialFolders("MYDOCUMENTS") & "\"
    Then, I can easily create a variable that has the name of the spreadsheet file I need to load (located in My Documents folder, of course):
    Code:
    g_strSpreadSheetPath = g_strMyDocs & "MyExcelData.xls"
  2. Create an Excel Application object.
    In order to work with a spreasheet, you'll need to get a reference to Excel's Application automation object. The following satement accomplishes this task:
    Code:
    Set objExcel = CreateObject("Excel.Application")
  3. Open/Load the workbook that corresponds to the .xls file.
    Once you have an Application object reference, you can use it to open the workbook represented in your .xls file. Opening a workbook is pretty simple, too:
    Code:
    Set objWkBook = objExcel.Workbooks.Open(g_strSpreadSheetPath)
  4. Get a reference to the Sheet object that has the data you need.
    Once you have a reference to a Workbook object, you can access any of the Sheets the workbook contains. For example, this code gets us a reference to the first Sheet in the workbook we've loaded:
    Code:
    Set objSheet = objWkBook.Sheets(1)
  5. Use the Sheet object's Cells() or Range() methods to extract data from the sheet.
    Now that we have a reference to a Sheet object, we can use its Cells() or Range() methods to return data that the sheet has. Here are a couple of examples using each method:
    Code:
    ' Get value using a Cell object (row,col) where row and col are numbers:
    '  Cells() Example #1: Get Value of "A1"
    strValue = objSheet.Cells(1, 1).Value
    MsgBox "The value in Cells(1,1) is: " & strValue
    '  Cells() Example #2: Get Value of "C4"
    strValue = objSheet.Cells(4, 3).Value
    MsgBox "The value in Cells(4,3) is: " & strValue
    
    ' Get value using a Range object ("ROW:COL"), where row is a letter/string, and
    ' column is a number.
    '  Range() Example #1: Get Value of "A1"
    strValue = objSheet.Range("A1").Value
    MsgBox "The value in Range(""A1"") is: " & strValue
    '  Range() Example #2: Get Value of "C4"
    strValue = objSheet.Range("C4").Value
    MsgBox "The value in Range(""C4"") is: " & strValue
A couple of additional tips for using Excel within a script... don't forget to:
  • Close the workbook (objWkBook.Close; you can pass an optional parameter for whether or not you'd like to save any changes made to the spreadsheet; True = Save; False = Discard changes; for example: objWkBook.Close False to close w/o saving any changes)
  • Close the reference to the Excel Application object you've created. Otherwise, you'll have zombie EXCEL.EXE processes hanging around in your task manager's process listing, taking up memory, etc... For example: objExcel.Quit
Putting these concepts all together (and adding examples of how to set cell values (and comments, too) in an excel spreadsheet), there's an example script that we've created and it is available in another post linked to on our script examples 'sticky': ExcelSpreadsheets-ReadingAndWriting.vbs.txt (See the other post for more info).

This example code shows a few techniques, one of which is reading data from cells in a spreadsheet:


Please note that we don't advise saving usernames and passwords in plain text within a spreadsheet; we provide the example as a proof of concept showing how to read/manipulate an Excel spreadsheet within the context of a SecureCRT script.

The ExcelSpreadsheets-ReadingAndWriting.vbs.txt example script not only shows how to "read" data, but it also exemplifies recording data received from a remote into the spreadsheet:


Does this additional information help you out?
Attached Images
File Type: jpg MyExcelData-Structure.jpg (78.2 KB, 2315 views)
File Type: jpg MyExcelData-Results.jpg (114.1 KB, 2363 views)
__________________
Jake Devenport
VanDyke Software
Technical Support
YouTube Channel: https://www.youtube.com/vandykesoftware
Email: support@vandyke.com
Web: https://www.vandyke.com/support

Last edited by jdev; 03-02-2018 at 05:29 PM.
Reply With Quote
  #13  
Old 10-15-2010, 04:07 PM
dean.spencer dean.spencer is offline
Registered User
 
Join Date: Oct 2010
Posts: 27
Hi, sorry for asking what might seem to be so easy to do. But is there a way I could change this script to automate a login.

I would like to store the IP / USER / PASS on excel sheet and pull this info based on the input e.g.

Input IP Address "192.168.0.1 etc" - Script looks up in excel for IP and sends back USER / PASS

With the above then output to CRT with something like below

crt.screen.Send "telnet" & IPADDRESS & Chr(10) & USER & Chr(10) & PASS & Chr(10)

Cheers

Last edited by jdev; 03-02-2018 at 05:30 PM.
Reply With Quote
  #14  
Old 10-17-2010, 03:06 PM
dean.spencer dean.spencer is offline
Registered User
 
Join Date: Oct 2010
Posts: 27
Hi

I am having problems getting this script to work, I need it to connect to device within the current working session.

Please could you advise what I need to change to achive this goal?
Reply With Quote
  #15  
Old 10-18-2010, 08:26 AM
jdev's Avatar
jdev jdev is offline
VanDyke Technical Support
 
Join Date: Nov 2003
Location: Albuquerque, NM
Posts: 1,098
Quote:
Originally Posted by dean.spencer
Hi

I am having problems getting this script to work, I need it to connect to device within the current working session.

Please could you advise what I need to change to achive this goal?
Without knowing any more details about the "problems" you mentioned, it would be difficult to provide any useful advice.

Can you provide more details as to the specifics of the problems you are seeing?

How are you launching the script?"

What exact error message(s) are you seeing?

What is the behavior that you see?

What is the exact structure of the spreadsheet you are using?

Please be as detailed as possible. If there are details that you feel aren't appropriate for a public forum such as this, please send an e-mail message to us with the details (support@vandyke.com). As always, try to put a subject that will help us know what it's about; for example, "ATTN Jake: Forum thread 5973" or something like that.

--Jake
__________________
Jake Devenport
VanDyke Software
Technical Support
YouTube Channel: https://www.youtube.com/vandykesoftware
Email: support@vandyke.com
Web: https://www.vandyke.com/support
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -6. The time now is 10:55 AM.