This article demonstrates how to create an Automation client for Microsoft Excel by using Micro Focus SilkTest 2010 .Net script.

Add a reference via the properties pane to the Microsoft.Office.Interop.Excel.dll. The following code creates and instance of Excel, opens a specific Workbook and Worksheet. (See KB Article 32535 for instructions on adding a Microsoft Office reference).

Note: This object is not visible to the user by design.

The Worksheet “sheet1” is assigned to the oWorksheet object and the used range of the worksheet is assigned to the oRange object. Each row and column in the used range is read and in this example is displayed in a messagebox.

Note: The messagebox is available as a result of adding the reference System.Windows.Forms.

It is at this point that the user could use string/integer arrays to assign the vaules found in the various cells in the Worksheet for later use.

Imports Microsoft.Office.Interop
Imports System.Windows.Forms
 
Public Module Main
      Dim _desktop As Desktop = Agent.Desktop
 
      Public Sub Main()
           
            Dim oExcel As New Excel.Application
            Dim oWorkbook As Excel.Workbook
            Dim oWorksheet As Excel.Worksheet
            Dim oRange As Excel.Range
           
            Dim iRowCnt As Integer
            Dim iColumnCnt As Integer
            Dim obj As Object
                       
            oExcel.Visible = False
           
            Try
                  oWorkbook = oExcel.Workbooks.Open("C:\Temp\Names.xls")
                  oWorksheet = oWorkbook.Worksheets("sheet1")
            Catch ex As Exception
                  MessageBox.Show(ex.ToString(),"Open workbook error")
                  Exit Sub
            End Try
     
            Try
                  oRange = oWorksheet.UsedRange
           
                  For iRowCnt = 1 To oRange.Rows.Count
                        For iColumnCnt = 1 To oRange.Columns.Count
                              obj = CType(oRange.Cells(iRowCnt,iColumnCnt), Excel.Range)
                       
                              MessageBox.Show(obj.Value)
                  Next             
            Next
           
            Catch ex As Exception
                 
                  MessageBox.Show(ex.ToString())
                  oWorkbook.Close
                  oExcel.Quit
                  Exit Sub
                 
            End Try
 
      oWorkbook.Close
      oExcel.Quit
                       
      End Sub
End Module