Function getTestdata( strFilePath,strSheetName,colNumber,flag,parmNumbers)
‘定義變數
Dim ExcelApp,ExcelBook,ExcelSheet,rowcount,colcount,array(),arra(),k
‘創建EXCEL程式，打開作業簿，設定當前活動sheet
Set ExcelApp = CreateObject(“Excel.Application”)
Set ExcelBook = ExcelApp.WorkBooks.Open(strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets(strSheetName)
‘取得EXCEL表共有幾行、幾列
rowcount=ExcelBook.ActiveSheet.UsedRange.Rows.Count
colcount=ExcelBook.ActiveSheet.UsedRange.Columns.Count
‘確定哪些行的資料需要執行，存在一維陣列中
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
‘重定義二緯陣列，第一列存放每條測驗資料行號，及測驗資料的引數
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
‘回傳值
getTestdata=https://bbs.csdn.net/topics/array
‘關閉Ecxel
closeExcelSheet ExcelBook,ExcelApp,ExcelSheet
End Function
-------------------------------------------------------------------------------------------------------------
Sub setResultByArrdata(strFilePath,strSheetName,arrData,resultColname,arrResult)
Dim ExcelApp,ExcelBook,ExcelSheet,notNullNumber,intCol
‘創建EXCEL程式，打開作業簿，設定當前活動sheet
Set ExcelApp = CreateObject(“Excel.Application”)
Set ExcelBook = ExcelApp.WorkBooks.Open(strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets(strSheetName)
‘取得EXCEL表共有幾行、幾列
rowcount =ExcelBook.ActiveSheet.UsedRange.Rows.Count
colcount=ExcelBook.ActiveSheet.UsedRange.Columns.Count
intCol =getColByValue(strFilePath,strSheetName,resultColname)
‘統計結果所在的列有多少行不為空
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
uj5u.com熱心網友回復：
好像是倒騰資料用的。。。。。。。轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/125056.html
標籤：VBA