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」的原創(chuàng)文章，遵循 CC 4.0 BY-SA 版權協(xié)議，轉載請附上原文出處鏈接及本聲明。

原文鏈接：https://blog.csdn.net/erix1991/article/details/17683433