Range in Excel to DAO Recordset

[cgraeter]

Is it possible to assign a range in Excel to a DAO recordset? I have found a means of assinging a range to an ADO recordset, but the searching capabilities of ADO recordsets is not the greatest. I would like to be able to use the FindFirst methods and such that DAO offers. Any help you can provide would be greatly appreciated!!

 

[SkipVought]

Assign a range?

You can use the CopyFromRecordset to WRITE the recordset to a range.

In the Recordset Object, you can use methods like MoveFirst, MoveNext.

Not sure what you are looking for.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

To assign a range to a DAO recordset you must query the range using the appropriate connection

Alternatively you can assign a range to an array and then use array processing...



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cgraeter]

xlbo, Can you offer an example of how to query a range using an appropriate connection? For instance, if I'm trying to get the range B7:G62 into a DAO recordset, what would the code look like?

I'm used to working with these recordsets in Access, which I have no problem with at all and use quite often. I just can't seem to figure out the appropriate syntax to get this accomplished in Excel.

 

[cgraeter]

OK, I'm almost there. I've determined how to pull an entire worksheet into a DAO recordset. Here's what I've used to pull the entire worksheet in...

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = OpenDatabase(fileNameAndPath, False, True, "Excel 8.0;HDR=Yes;")
Set rs = db.OpenRecordset("SELECT * FROM [SheetName$]")

I'm still trying to figure out how to limit it down to a specific range though....like B7:G62. Any ideas anyone?

 

[PHV]

You may use a WHERE clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cgraeter]

Thanks for the response, PHV. I was assuming that I'd be able to use the WHERE clause as part of the SELECT statement, but that's not going to accomplish what I'm aiming for.

The code that I posted above assumes that the column headers begin in cell A1 of the worksheet that's being assigned to the recordset. If the worksheet I was trying to execute against were set up like that, this would work great. However, the sheets I'm trying this with have multiple "tables" on each worksheet (none of which start in cell A1). I need to be able to pull the data from a range of cells within a worksheet into a recordset and then do my evaluations.

Any other thoughts anyone?

 

[PHV]

You may try to use a named range instead of the sheet name.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cgraeter]

That did the trick! I switched out SheetName$ with the name of a range and it worked like a charm. You can also simply populate a range in its place like B7:G21. I knew it would be something easy...almost always is!! Thanks PHV!! I really appreciate your help!