Post reply

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

  • September 12, 2004 at 3:09 pm

    #83975

    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

  • September 13, 2004 at 6:29 am

    #522713

    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

  • September 13, 2004 at 7:10 am

    #522724

    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

     

     

  • September 13, 2004 at 9:15 am

    #522776

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

  • September 13, 2004 at 9:54 am

    #522791

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

    1. I create a Time collection that vill be used to create all the columns in the RS
    2. 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)
    3. Then fill the RS the usual way
    4. Send the result to Excel
    5. 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).

    1. 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?
    2. 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?

     

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

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