Function Xls_CreateExcelApp(boolVisible)
On Error Resume Next
Dim excelShet
Set ExcelApp = CreateObject("Excel.Application")
If(boolVisible = "True") Then
ExcelApp.Visible = True
ElseIf(boolVisible = "False") Then
ExcelApp.Visible = False
Else
Xls_CreateExcelApp = "call the method params is incorrect"
End If
'return
Set Xls_CreateExcelApp = ExcelApp
'destory
Set ExcelApp = Nothing
End Function
Function Xls_OpenWorkbook(ByRef ExcelApp,filepath)
On Error Resume Next
Set NewWorkbook = ExcelApp.Workbooks.Open(filepath)
Set Xls_OpenWorkbook = NewWorkbook
Set NewWorkbook = nothing
End Function
Function Xls_ActivateWorkbook(ByRef ExcelApp, strWorkBookName)
On Error Resume Next
ExcelApp.Workbooks(strWorkBookName).Activate
End Function
Function Xls_GetSheet(ByRef ExcelApp, strSheetName)
Err.Clear
Dim worksheet
On Error Resume Next
Set worksheet = ExcelApp.Worksheets.Item(strSheetName)
Set Xls_GetSheet = worksheet
Set worksheet = nothing
End Function
Function Xls_GetSheetUsedColumnsCount(strFileName, strSheetName)
Dim oExcel
Dim workbook
Dim worksheet
Set oExcel = Xls_CreateExcelApp("False")
Set workbook = Xls_OpenWorkbook(oExcel,strFileName)
Set worksheet = Xls_GetSheet(oExcel,strSheetName)
Xls_GetSheetUsedColumnsCount = worksheet.UsedRange.Columns.Count
oExcel.Quit
Set worksheet = Nothing
Set workbook = Nothing
Set oExcel = Nothing
End Function
Function Xls_GetSheetUsedRowsCount(strFilename, strSheetName)
Dim oExcel, workbook, worksheet
Set oExcel = Xls_CreateExcelApp("False")
Set workbook = oExcel.Workbooks.Open(strFileName)
Set worksheet = Xls_GetSheet(oExcel,strSheetName)
Xls_GetSheetUsedRowsCount = worksheet.UsedRange.Rows.Count
oExcel.Quit
Set worksheet = Nothing
Set workbook = Nothing
Set oExcel = Nothing
End Function
Function Xls_GetCellvalue(ByRef ExcelSheet, intRow, intColumn)
'On Error Resume Next
Xls_GetCellvalue = ExcelSheet.Cells(intRow, intColumn)
End Function
Function Xls_GetSheetData2Array(strFileName, strSheetName)
Dim Columnscount, RowsCount
Columnscount = Xls_GetSheetUsedColumnsCount(strFileName,strSheetName)
RowsCount = Xls_GetSheetUsedRowsCount(strFileName, strSheetName)
Dim oExcel, workbook, worksheet
Set oExcel = Xls_CreateExcelApp("False")
Set workbook = oExcel.Workbooks.Open(strFileName)
Xls_ActivateWorkbook oExcel,strSheetName
Set worksheet = Xls_GetSheet(oExcel,strSheetName)
ReDim scriptItemArray(RowsCount-1,Columnscount-1)
Dim Actual
Actual = 0
For i=2 To RowsCount-1
number = Trim(Xls_GetCellvalue(worksheet,i,1))
If(IsEmpty(number) Or number = "" Or Not (IsNumeric(number))) Then
WSH.Echo number
Exit For
End If
Actual = Actual + 1
For j=1 To Columnscount-1
scriptItemArray(i-2,j-1) = Trim(Xls_GetCellvalue(worksheet,i,j))
WSH.Echo Xls_GetCellvalue(worksheet,i,j)
Next
Next
ReDim actualScriptItemArray(Actual-1, Columnscount-1)
For i=0 To Actual-1
For j = 0 To Columnscount-1
actualScriptItemArray(i,j) = scriptItemArray(i,j)
Next
Next
oExcel.Quit
Set worksheet = Nothing
Set workbook = Nothing
Set oExcel = Nothing
Xls_GetSheetData2Array = actualScriptItemArray
End Function
Dim strFileName, strSheetName
strFileName = "D:\VBS Libary\EOM\Case.xlsx"
strSheetName = "script"
Dim arrData
arrData = Xls_GetSheetData2Array(strFileName, strSheetName)
————————————————
版权声明：本文为CSDN博主「erix1991」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：/erix1991/article/details/17683433
