Open Excel workbook using ADO
Quote:
> What is the ADO connection string syntax if I want to open an Excel
> workbook. If anyone has an example I would be grateful.
There's one approach on the list at at
http://www.able-consulting.com/ADO_Conn.htm
Here's a different example:
'*******************************************
'* ADO to open Excel97 sheet as db/recordset *
'*******************************************
Dim cnnDB As adodb.Connection
Dim rsADO As adodb.Recordset
Dim strXLPath As String
Set cnnDB = New adodb.Connection
Set rsADO = New adodb.Recordset
strXLPath = "C:\SomeFolder\Your.xls"
' Specify Excel 8.0 by using the Extended Properties property,
' and then open the Excel file specified by strDBPath.
With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0"
.Open strXLPath
End With
'open recordset from a worksheet - the name of the worksheet must be
' placed in brackets with a dollar sign appended to sheet name.
With rsADO
' use a lock type other than the default (adLockReadOnly) if you
' want to be able to update the recordset - activate next line
'.LockType = adLockOptimistic
' Open the recordset with the SQL command you want
.Open "SELECT * FROM [SomeSheet$] WHERE LastName = 'Doe'", _
cnnDB, adOpenDynamic, , adCmdText
'recordset is now open - perform any operations on it
'when you're done:
.Close
End With
--
Jim in Cleveland
If you're writing to me, in my address
change "REAL_Address.see.below" to "worldnet.att.net"
((("What's so funny 'bout peace, love & understanding?" - Nick Lowe)))
