Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transfer record set to Excel (A97, XL2K)

    I am using VBA in Access 97 to dump records into Excel 2K. Currently I am looping through the recordsets and placing their field data directly into cells. This seems long-winded and I was looking for a way to just dump the recordset into a sheet directly, some thing like

    .Cells(lngRowCount, lngColCount) = rst

    where rst is a recordset created from an SQL string.

    Many Thanks

    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Transfer record set to Excel (A97, XL2K)

    The Excel Range object has a CopyFromRecordset method:

    .Cells(lngRowCount, lngColCount).CopyFromRecordset rst

    The method has two additional optional arguments MaxRows and MaxColumns to limit the number or rows and/or columns retrieved from the recordset.

    Note: CopyFromRecordset doesn't retrieve the field names, only the data, so if you want to display the field names as headers, you must call CopyFromRecordset from a cell one row down. See the online help for an example of retrieving the field names.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer record set to Excel (A97, XL2K)

    Many Thanks Hans. My code will now get remarkably shorter!

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •