Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening Excel, Book1 opens, remains open with other workbook open | Excel Discussion (Misc queries) | |||
query a recordset | Excel Discussion (Misc queries) | |||
excel 2003 saved file will not open without a blank workbook open | Excel Discussion (Misc queries) | |||
Open Workbook - Select Range as table for vlookup | Excel Discussion (Misc queries) | |||
Excel workbook does not open in open window on desktop | Excel Discussion (Misc queries) |