How to create a dynamic Recordset to use .CopyFromRecordset in Excel2003?

Question

How to create a dynamic Recordset to use .CopyFromRecordset in Excel2003?

SSChasing Mays

Points: 653

September 12, 2004 at 3:09 pm

I want to present different reports in Excel. The reports will not have fixed quantity of columns. The columns are timeperiods and the rows keyvalues. I would like to use the method CopyFromRecordset in Excel2003, but I don't know how to create the dynamic recordset. If I understand it correct, to use this method the recordset has to have all the columns defined I will have in the report but the number of columns will vary. Currently I use a fiex recordset that I loop through on the VBA Client, but I would like to skip this to make the presentation faster by using this Copy method

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dean gross SSCrazy Points: 2632 More actions · Answer 1

Before you start writing this code you should check out the Data, Import External Data menu item. This will allow you to create a query or connect to an existing SQL Server View to get a recordset. The user can refresh the data when needed.

You can send your data (as pairs of key values and time periods) to a raw data sheet in your workbook and then create a Pivot Table on a separate sheet that will group your time periods automatically into columns. For example, it will automatically create years, quarters and month summary groups from weekly data. The number of groups will adjust automatically whenever the raw data is updated.

HTH

Dean

WSquared SSCrazy Points: 2393 More actions · Answer 2

CopyFromRecordset does not take out any headings into Excel, so you have 2 options,

1. Use a template with the column headings predefined

2. Loop through the fields collection of the recordset and enter the field name into the cells in the first row

Then set the cursor to cell where you want the data to be put and use the copyfromrecordset

This bit of code does the second option, you just pass an excel worksheet and an ADODB.recordset

Function RecordsetToExcelSheet(rst As ADODB.Recordset, xlSheet As Excel.Worksheet, Optional intFirstRow As Integer = 1)

Dim i As Integer

For i = 0 To rst.Fields.Count - 1

xlSheet.Cells(intFirstRow, i + 1) = rst.Fields(i).Name

Next i

xlSheet.Cells(intFirstRow + 1, 1).CopyFromRecordset rst

End Function

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 3

Just remember that in Excel, you have a limit of 65,536 rows of data that can be inserted.

marcopolo56 SSChasing Mays Points: 653 More actions · Answer 4

I've just dynamically created a recordset (RS):

I create a Time collection that vill be used to create all the columns in the RS
Create the fields (columns) in the RS by looping through all the time periods in the collection and theTimeperiods will be the headings, (Fields.Append (m_objTimeMember.TimeId), adVarWChar, 1000)
Then fill the RS the usual way
Send the result to Excel
use WkSheet.Range("A1").CopyFromRecordSet RS to fill Excel2003 sheet with data

I still have 2 problems

The first 50 % of the data that I send to the client will be double values but the rest will be string values (Formulas).

I tried to create the fields for these data as Variant to suit both, but this does not seem to work. So how Can I send both type of data? I think I need to make 2 RS but is it possible to send this as ONE RS so I do not need to call the server 2 times? Or can I change the Fields type dynamically as well?
In the first column I have specific row data and in this field I need to set Defined Names as well, Is this possible by using CopyFrom Recordset? Probably not, so I need to make this later on or?