Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 8th, 2006, 07:32 AM
Registered User
 
Join Date: Mar 2006
Location: Geneva, Ge, Switzerland.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Ouptut from object Recordset (ADO)

Hi!
I have the following questions

I) how can attribute the result from a query to an array?
  
Code:
Sheets(shtData).ActiveCell.CopyFromRecordset rsData

works fine to output in a cell but I would need something among the lines
 
Code:
Dim myArr() As Variant
Code:
myArr <- rsData '<- attribution of values
II) I also would like to parameter the output of a query on a given range but the following gives me an error (propriety or method not available for this object)
Code:
Dim rng As Range
Set rng = Range(Cells(2, nbCol), Cells(2, nbCol))
  Sheets(shtData).rng.CopyFromRecordset rsData
I guess there is something with the range property I misunderstood. Any idea?
Thanks

Anne-Olga
  #2 (permalink)  
Old March 30th, 2006, 06:17 AM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

(I)
There is no quick feature to do this (such as copyfrom recordset) but it can be done. My question is why do you want to move it into an array, when you can manipulate the recordset object to return the data held within it

Code:
Dim varRecordset() As Variant

... code to open recordset

ReDim varRecordset(rs.RecordCount - 1, rs.Fields.Count - 1)
Do Until rs.EOF
   For a = 0 To rs.Fields.Count - 1
       varRecordset(rs.AbsolutePosition - 1, a) = rs(a)
    Next
    rs.MoveNext
Loop
(II)
I'm sure someone else can come up with something better tha this, but the code below works for me

Code:
Dim rng As Range
Sheets(shtdata).Select
Set rng = Range(Cells(2, nbCol), Cells(2, nbCol))

 rng.CopyFromRecordset rsData




Similar Threads
Thread Thread Starter Forum Replies Last Post
View an ado recordset in a form feets Access VBA 5 June 20th, 2007 06:19 AM
Clone DAO Recordset into ADO Recordset kamrans74 VB How-To 0 March 6th, 2007 11:57 AM
ADO recordset to table kanoorani VB Databases Basics 3 May 16th, 2006 02:59 AM
ADO Recordset Scootterp Access VBA 4 February 27th, 2006 06:44 PM
Convert ADO recordset to DAO recordset andrew_taft Access 1 May 5th, 2004 02:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.