View Single Post
  #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,099
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, 2691 views)
File Type: jpg MyExcelData-Results.jpg (114.1 KB, 2736 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