[分享] 写了一个从Excel里面拿数据的function，可以支持多个sheets

jraphicqesign

不过我是用英文写的，当时主要是因为用TestPartner有这个需求，所以写了一个。
发现用在QTP也可以。拿出来和大家分享。

原文在这里，除了代码，还有一些解释：
Get data from Excel for Test Partner and QTP

我把代码拷一下：

Since TestPartner uses VBA, but QTP uses VBScript, you need to make the following changes in the function script:
A, The function script needs to be created in the QTP Function Library (File – New – Function Library);
B, In the function script, delete anything related to the ErrorHandler (2 paragraphs);
C, In the function script, delete any definition for variable type, e.g., modify “(FilePath As String, SheetName As String)” to “(FilePath, SheetName)”, and change “Dim SheetCount As Integer” to “Dim SheetCount”

=======================================
01.Function GetExcelSheetData(FilePath As String, SheetName As String)
02.
03.'On error jump to "Error Handler:" line
04.On Error GoTo ErrorHandler
05.
06.'Variable declaration
07.Dim SheetCount As Integer
08.Dim RowCount As Integer
09.Dim ColumnCount As Integer
10.Dim CellArray() 'A dynamic array that is not sized in the Dim statement
11.
12.'Open the Excel file
13.Set ObjExcel = CreateObject("Excel.Application")
14.Set ObjWorkBook = ObjExcel.Workbooks.Open(FilePath)
15.
16.'Count how many sheets in the Excel file
17.SheetCount = ObjWorkBook.WorkSheets.Count
18.
19.'Store the data from a specific worksheet into an array
20.For i = 1 To SheetCount
21.    If ObjWorkBook.WorkSheets(i).Name = SheetName Then
22.
23.        'Count how many rows and columns in the Excel file (only for the used range)
24.        RowCount = ObjWorkBook.WorkSheets(i).UsedRange.Rows.Count
25.        ColumnCount = ObjWorkBook.WorkSheets(i).UsedRange.Columns.Count
26.
27.        'Array is sized with the ReDim statement
28.        'after RowCount and ColumnCount are given values.
29.        ReDim CellArray(RowCount, ColumnCount)
30.
31.        'Store the data from the specific worksheet into CellArray
32.        For j = 1 To RowCount
33.            For k = 1 To ColumnCount
34.               CellArray(j, k) = ObjWorkBook.WorkSheets(i).UsedRange.Cells(j, k)
35.            Next
36.        Next
37.
38.    End If
39.Next
40.
41.'Close the Excel file
42.ObjWorkBook.Close
43.
44.'Return CellArray
45.GetExcelSheetData = CellArray()
46.
47.Exit Function
48.
49.ErrorHandler: ' This is a normal VBA line label
50.    MsgBox Err.Description
51.    ObjWorkBook.Close
52.
53.End Function

至于怎么运行这个function，见Get data from Excel for Test Partner and QTP详解。

我也是初学，写得不好，大家不要见笑。

TIB

TestPartner用的是VBA，QTP用的是VBS，大部分语法是一样的，因此很多函数可以通用