************************************************** ****
'Function: read a value in Excel
'Input parameter:
'StrFilePath: Save Excel file path
'StrExcelSheetName: Excel to read the name in the Sheet
'IntRow: read what their data
'IntCol: which column to read the data
'For example: "E: \ a.xls", "Sheet1", 2,3
'Return: the value of taking into
'************************************************* *****
function getOneValue (strFilePath, strSheetName, intRow, intCol)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet
'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)
'Get excel in value, and returns
getOneValue = ExcelSheet.Cells (intRow, intCol)
'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
end function
'************************************************* *****
'Sub: to write a data in excel
'Input parameter:
'StrExcelSheetName: Excel to write the name in the Sheet
'IntRow: where to write data line
'IntCol: where to write a column
'StrValue: Value to write
'For example: "E: \ a.xls", "Sheet1", 2,3, "111"
'Return:
'************************************************* *****
sub setOneValue (strFilePath, strSheetName, intRow, intCol, strValue)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet
'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)
'Set value
ExcelSheet.cells (intRow, intCol). Value = strValue
'Write complete, save the EXCEL
ExcelApp.DisplayAlerts = False
ExcelApp.Save
'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
end sub
'************************************************* *****
'Function: read the value of a column in Excel
'Input parameter:
'StrFilePath: Save Excel file path
'StrExcelSheetName: Excel to read the name in the Sheet
'IntCol: Read a column of data which
'For example: "E: \ a.xls", "Sheet1", 2
'Return: the value of taking into
'************************************************* *****
function getColValues (strFilePath, strSheetName, intCol)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet, intRowscount, arrValues ()
'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)
'There are a few lines in excel
intRowscount = ExcelBook.ActiveSheet.UsedRange.Rows.Count
'Get value excel
Redim Preserve arrValues (intRowscount-1)
For i = 1 to intRowscount
arrValues (i-1) = ExcelSheet.Cells (i, intCol)
Next
'Return value
getColValues = arrValues
'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
end Function
'************************************************* *****
'Sub: Write the value of a column in Excel
'Input parameter:
'StrFilePath: Save Excel file path
'StrExcelSheetName: to write the name of Sheet
'IntCol: Write a column of data which
'IntFromrow: where the line began to write
'ArrValue: write value (array)
'For example: "E: \ a.xls", "Sheet1", 2,2, arrRes
'Return:
'************************************************* *****
Sub setColValues (strFilePath, strSheetName, intCol, intFromRow, arrValue)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet, intRowscount
Dim intArrColumnsCount, intColumnsCount
'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)
'ExcelSheet.activate
'Get the size of the array
intArrColumnsCount = UBound (arrValue)
'Where the last line of writing
intRowCount = intFromRow + intArrColumnsCount
'Set value
For i = intFromRow To intRowCount
ExcelSheet.cells (i, intCol). Value = arrValue (i-intFromRow)
Next
'Write complete, save the EXCEL
ExcelApp.DisplayAlerts = False
ExcelApp.Save
'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
End Sub
'************************************************* *****
'Function: read the value of a row in Excel
'Input parameter:
'StrFilePath: Save Excel file path
'StrExcelSheetName: Excel to read the name in the Sheet
'IntRow: read what their data
'For example: "E: \ a.xls", "Sheet1", 1
'Return: the value of taking into
'************************************************* *****
function getRowValues (strFilePath, strSheetName, intRow)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet, intColumnsCount, arrValues ()
'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)
'There are several columns in excel
intColumnsCount = ExcelBook.ActiveSheet.UsedRange.Columns.count
'Get value excel
Redim Preserve arrValues (intColumnsCount -1)
For i = 1 to intColumnsCount
arrValues (i-1) = ExcelSheet.Cells (intRow, i)
Next
'Return value
getRowValues = arrValues
'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
end Function
'************************************************* *****
'Sub: Write the value of a row in Excel
'Input parameter:
'StrFilePath: Save Excel file path
'StrExcelSheetName: to write the name of Sheet
'IntRow: write a line of data which
'IntFromCol: where to start writing the column
'ArrValue: write value (array)
'For example: "E: \ a.xls", "Sheet1", 5,2
'Return:
'************************************************* *****
Sub setRowValues (strFilePath, strSheetName, intRow, intFromCol, arrValue)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet, intColcount
Dim intArrColumnsCount, intColumnsCount
'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)
'Get the size of the array
intArrColumnsCount = UBound (arrValue)
'Last wrote which column
intColcount = intFromCol + intArrColumnsCount
'Set value
For i = intFromCol To intColcount
ExcelSheet.cells (intRow, i). Value = arrValue (i-intFromCol)
Next
'Write complete, save the EXCEL
ExcelApp.DisplayAlerts = False
ExcelApp.Save
'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
End Sub
'************************************************* *****
'Function: read all the values in Excel
'Input parameter:
'StrFilePath: Save Excel file path
'StrExcelSheetName: Excel to read the name in the Sheet
'For example: "E: \ a.xls", "Sheet1"
'Return: the value of taking into
'************************************************* *****
function getAllValues (strFilePath, strSheetName)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet, intRowscount, intColumnsCount, arrGetCellValue ()
'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)
'There are several columns in excel
intRowscount = ExcelBook.ActiveSheet.UsedRange.Rows.Count
intColumnsCount = ExcelBook.ActiveSheet.UsedRange.Columns.count
'Get value excel
Redim Preserve arrGetCellValue (intRowscount-1, intColumnsCount-1)
For i = 1 To intRowscount
For j = 1 to intColumnsCount
arrGetCellValue (i-1, j-1) = ExcelSheet.Cells (i, j)
Next
Next
'Return value
getAllValues = arrGetCellValue
'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
end Function
'************************************************* *****
'Function: read the value of the first occurrence of a line number
'Input parameter:
'StrFilePath: Excel file path
'StrSheetName: Excel to read the name in the Sheet
'Value: The value of the first occurrence of
'For example: "E: \ a.xls", "Sheet1", "root"
'Return: line number
'************************************************* *****
Function getRowByValue (strFilePath, strSheetName, Value)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet
Dim rowcount, colcount
'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)
'There are a few lines to obtain EXCEL table, columns
rowcount = ExcelBook.ActiveSheet.UsedRange.Rows.Count
colcount = ExcelBook.ActiveSheet.UsedRange.Columns.Count
'From the line to start the cycle
For i = 1 To rowcount
For j = 1 To colcount
'To find the desired value to determine whether
If ExcelSheet.cells (i, j) = Value Then
'Return value
getRowByValue = i
'If this value is found, exit loop
Exit for
End If
Next
'If this value is found, exit loop
If getRowByValue <> "" Then
Exit For
End If
Next
'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
End Function
'************************************************* *****
'Function: Read the first occurrence of a value column number
'Input parameter:
'StrFilePath: Excel file path
'StrSheetName: Excel to read the name in the Sheet
'Value: The value of the first occurrence of
'For example: "E: \ a.xls", "Sheet1", "root"
'Return: line number
'************************************************* *****
Function getColByValue (strFilePath, strSheetName, Value)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet
Dim rowcount, colcount
'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)
'There are a few lines to obtain EXCEL table, columns
rowcount = ExcelBook.ActiveSheet.UsedRange.Rows.Count
colcount = ExcelBook.ActiveSheet.UsedRange.Columns.Count
'From the line to start the cycle
For i = 1 To rowcount
For j = 1 To colcount
'To find the desired value to determine whether
If ExcelSheet.cells (i, j) = Value Then
'Return value
getColByValue = j
'If this value is found, exit loop
Exit for
End If
Next
'If this value is found, exit loop
If getColByValue <> "" Then
Exit For
End If
Next
'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
End Function
'************************************************* *****
'Function: initial data, access to test data
'Input parameter:
'StrFilePath: Excel test data file path
'StrSheetName: Excel to read the name in the Sheet
'ColNumber: identifier located list
'Flag: whether to implement identifier
'ParmNumbers: the number of test parameters
'For example: "D: \ test.xls", "login", 1, "x", 4
'Return: test data (two-dimensional array)
'The first column in each test data row number in excel, so that the results of the write-back
'************************************************* *****
Function getTestdata (strFilePath, strSheetName, colNumber, flag, parmNumbers)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet, rowcount, colcount, array (), arra (), k
'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)
'There are a few lines to obtain EXCEL table, columns
rowcount = ExcelBook.ActiveSheet.UsedRange.Rows.Count
colcount = ExcelBook.ActiveSheet.UsedRange.Columns.Count
'The data to determine which rows need to perform, there is one-dimensional array
m = 0
For i = 1 To rowcount
If ExcelSheet.cells (i, colNumber) = flag Then
ReDim PreServe arra (m)
arra (m) = i
m = m +1
End If
Next
'Re-definition of the second weft array, the first column of data stored for each test line number, and the parameters of the test data
ReDim PreServe array (m-1, parmNumbers)
For i = 0 To m-1
array (i, 0) = arra (i)
For j = 1 To parmNumbers
array (i, j) = ExcelSheet.cells (arra (i), j + colNumber)
Next
Next
'Return value
getTestdata = array
'Close Ecxel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
End Function
'************************************************* *****
'Sub: According to the results of filtering the test data is written
'Input parameter:
'StrFilePath: Excel test data file path
'StrSheetName: Excel to read the name in the Sheet
'ArrData: an array of test data storage
'StrColName: results of the column names
'ArrResult: an array of storage of test results
'For example: "D: \ 1.xls", "sheet1", arrData, "actualResult", arrResult
'Return:
'************************************************* *****
Sub setResultByArrdata (strFilePath, strSheetName, arrData, resultColname, arrResult)
Dim ExcelApp, ExcelBook, ExcelSheet, notNullNumber, intCol
'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)
'There are a few lines to obtain EXCEL table, columns
rowcount = ExcelBook.ActiveSheet.UsedRange.Rows.Count
colcount = ExcelBook.ActiveSheet.UsedRange.Columns.Count
intCol = getColByValue (strFilePath, strSheetName, resultColname)
'Statistics the number of rows where the column is not empty
notNullNumber = 0
For i = 1 To rowcount
If ExcelSheet.cells (i, intCol )<>"" Then
notNullNumber = notNullNumber +1
End If
Next
If notNullNumber = 1 Then
For i = 0 To UBound (arrResult)
ExcelSheet.cells (arrData (i, 0), intCol). Value = arrResult (i)
Next
Else
For i = 0 To UBound (arrResult)
ExcelSheet.cells (arrData (i, 0), colcount +1). Value = arrResult (i)
Next
End If
ExcelApp.DisplayAlerts = false
ExcelApp.Save
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
End Sub
'************************************************* *****
'Sub: Close Excel
'Input parameter:
'ExcelBook: Open the Excel
'ExcelApp: Create the Excel object
'ExcelSheet: currently active form
'For example: ExcelBook, ExcelApp, ExcelSheet
'Return:
'************************************************* *****
Sub closeExcelSheet (ExcelBook, ExcelApp, ExcelSheet)
ExcelBook.Close
ExcelApp.Quit
Set ExcelApp = Nothing
Set ExcelBook = Nothing
Set ExcelSheet = Nothing
End Sub