Scripts and Automation: Automate Microsoft Excel from dScope
This is a very small and simple automation script illustrating the principle of reading from and writing to a Microsoft Excel spreadsheet.The primary application for this is for reading data from and saving results to a spreadsheet. The basic principle is that you use ActiveX controls to be able to access the Excel methods and properties of the spreadsheet.
Essentially, all this script does is read the contents of cell A1 of a set spreadsheet and write something you enter into the first available row after row 7. The name and location of the spreadsheet are set in the script, but can be changed to anything you like.
To use this script, you need firstly to have a copy of Excel installed on the computer where the script will run. You then need to either create a blank spreadsheet called "Test.xls" and place it in the root of your "c" drive, or point the script to an existing spreadsheet by editing the values of the default spreadsheet which appear in the script as below:
strXLfile = "C:test.xls" strSheet1 = "Sheet1" |
The first line is the path and name of the spreadsheet, the second one is the name of the worksheet (page) within that spreadsheet that you want to access. You can then run the script. It will simply bring up a series of message boxes and an input box which demonstrate reading from the spreadsheet and writing to it.
The script itself is commented to describe what each step is doing. It is very easy to modify it to make it usefully save results from tests. To this end, there is a routine within the script which finds the first free row. This is essential if you want to be able to record multiple tests and place each one on the next successive free line. The routine is set to start looking for the first free line after row 7, but could be edited to anything you like by changing the value of the line:
iRow1 = 7 |
This is set to 7 to allow headers and titles to be placed above the rows of data. Using the principles illustrated in this script, it is possible to read or write to any cell in any worksheet in any spreadsheet. You can format the rows and columns in Excel to improve your presentation and provide analysis of the data (place the analysis cells at the top of the worksheet and they can continue to calculate as more data is added).
| Resource Type: Scripts and Automation
(File Download) Download Link (https:): Link_to_Excel.zip Size: 2 KiB Date 2005-09-23 Relevant Products:
|
We're using a few cookies to make your visit here run smoothly.
If you're happy with this, just continue to use the site as normal.
To find out more, visit our