Function ReadExcel( myXlsFile, mySheet, blnHeader,sqlquery )
' Function : ReadExcel
' Version : 3.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.
' Connection string updated by Marcel Niƫnkemper to open Excel 2007 (.xslx) files.
objExcel.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
myXlsFile & ";Extended Properties=""Excel 12.0;IMEX=1;" & _
strHeader & """"
' Open a recordset object for the sheet and range
Set objRS = CreateObject( "ADODB.Recordset" )
strRange = mySheet
objRS.Open "Select * from [" & strRange & "]", objExcel, adOpenStatic
objRS.MoveFirst
Do Until objRS.EOF
'WScript.Echo objRS.Fields(0) & " " & objRS.Fields(1)
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
sText = Replace(objRS.Fields(0), "'", "''")
myCommand.CommandText = sqlquery & " values ('" & sText & "','" & objRS.Fields(1) & "') "
myCommand.Execute
myConn.Close
objRS.MoveNext
Loop
' Read the data from the Excel sheet
i = 0
' Close the file and release the objects
objRS.Close
objExcel.Close
Set objRS = Nothing
Set objExcel = Nothing
' Return the results
ReadExcel = arrData
End Function
Tuesday, May 3, 2016
Method to read EXCEL file in VB Script
Subscribe to:
Post Comments (Atom)
No comments:
Write comments