open range (within workbook) as ado recordset - excel vba
 

Rather than fill the ADO recordset with a query, you might consider creating
a disconnected recordset and filling it by iterating through the rows and
columns of your sheet. Once you've done this, the recordset may be easily
passed around and used like any other recordset.

Dim rs As New ADODB.Recordset

rs.Fields.Append "Field1Name, adInteger
rs.Fields.Append "Field2Name", adInteger
etc
rs.Open
rs.AddNew
rs!Field1Name = mysheet.cells(iRow,iCol)
rs!Field2Name = mysheet.cells(iRow,iCol+1)
etc.
rs.Update save the record



"brian" wrote in message
om...
Hi

I have a table of data on an excel worksheet and i would like to put
the table into an ado recordset to perform some data manipulation.
It's easy to find out how to do this from a vb app using an external
xls file, but how do i do it from vba within the spreadsheet?

I want to do something like this (see below) without opening an
external file - is there something you can set the Data Source to
which lets you access worksheets in the currently open workbook? Maybe
an equivalent of Currentproject.Connection in Access??? Or am i
barking up the wrong tree?

Thanks
Brian

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
Dim strFilePath As String

strFilePath = "C:\whatever.xls"

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
"Extended Properties=""Excel 8.0;HDR=No;"""

szSQL = "SELECT * FROM [enquiries$B7:N500]"

Set rsData = New ADODB.Recordset

rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText