Defining variables'
Dim ExcelApp ExcelBook, ExcelSheet, rowcount, colcount, array (), arra (), k
'create EXCEL program, open the workbook, set the current activity sheet
The Set ExcelApp=CreateObject (" Excel. Application ")
The Set ExcelBook=ExcelApp. WorkBooks. Open (strFilePath)
The Set ExcelSheet=ExcelBook. WorkSheets (strSheetName)
'get the EXCEL table there are a few lines, a few columns
Rowcount=ExcelBook. ActiveSheet. UsedRange. Rows. Count
Colcount=ExcelBook. ActiveSheet. UsedRange. Columns. The Count
'to determine what data need to be performed, if there is a dimension in the 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 the If
Next
'redefine two weft array, the first column for each test data, 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=https://bbs.csdn.net/topics/array
'close the Ecxel
CloseExcelSheet ExcelBook ExcelApp, ExcelSheet
End the Function
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Sub setResultByArrdata (strFilePath, strSheetName arrData, resultColname, arrResult)
Dim ExcelApp ExcelBook, ExcelSheet notNullNumber, intCol
'create EXCEL program, open the workbook, set the current activity sheet
The Set ExcelApp=CreateObject (" Excel. Application ")
The Set ExcelBook=ExcelApp. WorkBooks. Open (strFilePath)
The Set ExcelSheet=ExcelBook. WorkSheets (strSheetName)
'get the EXCEL table there are a few lines, a few columns
Rowcount=ExcelBook. ActiveSheet. UsedRange. Rows. Count
Colcount=ExcelBook. ActiveSheet. UsedRange. Columns. The Count
IntCol=getColByValue (strFilePath strSheetName, resultColname)
'statistical results in the columns of the how many line isn't empty
NotNullNumber=0
For I=1 To rowcount
If ExcelSheet. Cells (I, intCol) "" Then
NotNullNumber=notNullNumber + 1
End the If
Next
If notNullNumber=1 Then
For I=0 To UBound (arrResult)
ExcelSheet. Cells (arrData (I, 0), intCol). The value=https://bbs.csdn.net/topics/arrResult (I)
Next
The Else
For I=0 To UBound (arrResult)
ExcelSheet. Cells (arrData (I, 0), colcount + 1). The value=https://bbs.csdn.net/topics/arrResult (I)
Next
End the If
ExcelApp. DisplayAlerts=false
ExcelApp. Save
CloseExcelSheet ExcelBook ExcelApp, ExcelSheet
End Sub
CodePudding user response:
Seems to be carted to use,,,,,,,,,