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