get each row from recordset into excel worksheet

Posted on 2012-08-16
Last Modified: 2012-08-16
I have been using the vba command
wks.Range("A2").CopyFromRecordset qdf.OpenRecordset

to return the recordset but it appears to be missing some rows.

I have checked the parameters passed to the query and they are correct.

is there a way to get each row from the recordset one by one?

ie movefirst then get row then movenext and so on until the end of the recordset?

this way i can test better. I am in excel vba.

  For Each prm In qdf.Parameters
  Debug.Print prm.Name, prm.Value
Set wks = Sheets("CWCodesToMatch")
Dim iCols As Long

For iCols = 0 To qdf.OpenRecordset.Fields.Count - 1
    wks.Cells(1, iCols + 1).Value = qdf.OpenRecordset.Fields(iCols).Name
wks.Range(wks.Cells(1, 1), _
    wks.Cells(1, qdf.OpenRecordset.Fields.Count)).Font.Bold = True
wks.Range("A2").CopyFromRecordset qdf.OpenRecordset

Open in new window

Question by:PeterBaileyUk
    1 Comment
    LVL 13

    Accepted Solution

    You should be able to loop through the recordset using

    Do While Not qdf.OpenRecordset.EOF
    ' Loop through the fields again and copy the info in each cell


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now