Shulou(Shulou.com)06/03 Report--

This article mainly introduces how to use vbs to read the Excel file function, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

The code is as follows:

Function ReadExcel (myXlsFile, mySheet, my1stCell, myLastCell, blnHeader)

'Function: ReadExcel

'Version: 2.00

'This function reads data from an Excel sheet without using MS-Office

'

'Arguments:

'myXlsFile [string] The path and file name of the Excel file

'mySheet [string] The name of the worksheet used (e.g. "Sheet1")

'my1stCell [string] The index of the first cell to be read (e.g. "A1")

'myLastCell [string] The index of the last cell to be read (e.g. "D100")

'blnHeader [boolean] True if the first row in the sheet is a header

'

'Returns:

'The values read from the Excel sheet are returned in a two-dimensional

'array; the first dimension holds the columns, the second dimension holds

'the rows read from the Excel sheet.

'

'Written by Rob van der Woude

'http://www.robvanderwoude.com

Dim arrData (), I, j

Dim objExcel, objRS

Dim strHeader, strRange

Const adOpenForwardOnly = 0

Const adOpenKeyset = 1

Const adOpenDynamic = 2

Const adOpenStatic = 3

'Define header parameter string for Excel object

If blnHeader Then

StrHeader = "HDR=YES;"

Else

StrHeader = "HDR=NO;"

End If

'Open the object for the Excel file

Set objExcel = CreateObject ("ADODB.Connection")

'IMEX=1 includes cell content of any format; tip by Thomas Willig

ObjExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

MyXlsFile & "; Extended Properties="Excel 8.0 × IMEX 1;" & _

StrHeader & "

'Open a recordset object for the sheet and range

Set objRS = CreateObject ("ADODB.Recordset")

StrRange = mySheet & $& my1stCell & ":" & myLastCell

ObjRS.Open "Select * from [" & strRange & "]", objExcel, adOpenStatic

'Read the data from the Excel sheet

I = 0

Do Until objRS.EOF

'Stop reading when an empty row is encountered in the Excel sheet

If IsNull (objRS.Fields (0) .Value) Or Trim (objRS.Fields (0) .Value) = "" Then Exit Do

'Add a new row to the output array

ReDim Preserve arrData (objRS.Fields.Count-1, I)

'Copy the Excel sheet's row values to the array "row"

'IsNull test credits: Adriaan Westra

For j = 0 To objRS.Fields.Count-1

If IsNull (objRS.Fields (j). Value) Then

ArrData (j, I) = ""

Else

ArrData (j, I) = Trim (objRS.Fields (j). Value)

End If

Next

'Move to the next row

ObjRS.MoveNext

'Increment the array "row" number

I = I + 1

Loop

'Close the file and release the objects

ObjRS.Close

ObjExcel.Close

Set objRS = Nothing

Set objExcel = Nothing

'Return the results

ReadExcel = arrData

End Function

How to use it:

The copy code is as follows:

Option Explicit

Dim arrSheet, intCount

'Read and display columns A, B, rows 2.. 6 of "ReadExcelTest.xls"

ArrSheet = ReadExcel ("ReadExcelTest.xls", "Sheet1", "A1", "B6", True)

For intCount = 0 To UBound (arrSheet, 2)

WScript.Echo arrSheet (0, intCount) & vbTab & arrSheet (1, intCount)

Next

WScript.Echo "="

'An alternative way to get the same results

ArrSheet = ReadExcel ("ReadExcelTest.xls", "Sheet1", "A2", "B6", False)

For intCount = 0 To UBound (arrSheet, 2)

WScript.Echo arrSheet (0, intCount) & vbTab & arrSheet (1, intCount)

Next

Thank you for reading this article carefully. I hope the article "how to use vbs to read Excel files" shared by the editor will be helpful to everyone. At the same time, I also hope you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.