How to read the function of Excel file with vbs
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!
