Using CopyFromRecordset To Insert Formulas? 2

[DatabaseDude]

I'm trying something that may not be possible.

I'm trying to insert a formula into an ADO recordset (along with other columns) using the CopyFromRecordset range function.

Let's say I have these values in my recordset:

5
10
"=A1+B1"

If these were pasted into range A1 of a worksheet, I hoped to see this:

5     10     15

Instead, I see this:

5     10     =A1+B1

Is there a way to get what I'm looking for?

Reason I'm asking: we're trying to maintain the power of CopyFromRecordset in a wide spreadsheet, without having to insert the formulas programmatically in VBA. I realize there are alternatives to insert field values individually, but I wanted to see if this was a way to maximize CopyFromRecordset.

Thanks in advance!
Bryant

 

[PHV]

Why not playing with a QueryTable and its FillAdjacentFormulas property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DatabaseDude]

On this project, Excel is being used more as an output for reporting, rather than to its obvious capabilities. All the processing is being directed from within Access, pulling data from SQL Server, finally inserting it into Excel. Sounds like your idea would work if the process were being directed from within Excel?

Bryant

 

[SkipVought]

Try this formula instead
[tt]
5
10
=INDIRECT("A"&ROW())+INDIRECT("B"&ROW())
[/tt]


Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[DatabaseDude]

Still not getting it.

Let me post my code, as that will illustrate things better.

As noted before, this process will be initiated from within Access & SQL per project requirements; no other options, such as initiating data query from within Excel. However, I'm using Excel-based VBA code here for simplicity.

    Dim rst As New ADODB.Recordset
    
    With rst.Fields
        .Append "SomeField", adCurrency
        .Append "SomeOtherField", adCurrency
        .Append "SomeFormula", adVarChar, 20
    End With
    
    With rst
        .Open
        .AddNew
        !SomeField = 5
        !SomeOtherField = 10
        !SomeFormula = "=A1+B1"
        .Update
    End With
    
    ActiveWorkbook.Sheets("Sheet1").Range("A1").CopyFromRecordset rst
    
    rst.Close
    Set rst = Nothing

Here's what that produces, in row 1 columns A-C ...

5	10	=A1+B1

...rather than what I'd hoped for:

5	10	15 (as a calculated result of the formula)

My hopes were that the 3rd column would take the value of field "SomeFormula" and treat it as a formula - works out ok if typed in by hand.

Appreciate everyone's input and assistance.

Bryant

 

[SkipVought]

    With rst
        .Open
        .AddNew
        !SomeField = 5
        !SomeOtherField = 10
        !SomeFormula = !SomeField + !SomeOtherField
        .Update
    End With

???

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[SkipVought]

BTW,

USUALLY, these kind of things are performed with variables, so, yould just sum the variables.

I don't get the PROBLEM???

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[DatabaseDude]

I'm not trying to sum the fields in the recordset - I know how to do that, and how to assign variables.

What we're trying to do, giving the constraint of starting from within Access and outputting to Excel, to insert formulas quickly and efficiently at the time data is inserted (variable rows for each sheet).

The reason for wanting to insert the formula is to allow Excel to recalculate the cell value (C1 in my example above) if either A1 or B1 change. If we merely inserted a value that's the sum of the two fields, then if the user changed A1 or B1, C1 would not change.

I know how to put in cell values one at a time, and to insert formulas using VBA. Since there's a large block of data (several columns, variable rows) being inserted, I wanted to see if CopyFromRecordset could save us a lot of time and coding.

Thanks in advance,
Bryant

Bryant

 

[SkipVought]

Did you try...

=INDIRECT("A"&ROW())+INDIRECT("B"&ROW())

???

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[DatabaseDude]

Skip,

Would that be the value of recordset field SomeFormula, rather than "=A1+B1"?

Tried it that way, and it didn't work; anything in the field SomeFormula appears in the cell verbatim.

If I had to do things the longer way (insert values one cell at a time from recordset, insert formulas into cells as needed etc) then that's cool. I know several ways of doing that; and for anything that needs summed or other calculations prior to being inserted in Excel, I can do that in the stored procedure or VBA with no problem.

Again, just trying to save time, using what may be inelegant ("ghetto" as I've seen it called it in aquarium design - but that's another topic entirely!).

Thanks!
Bryant

 

[xlbo]

Think I get the issue - the manual workaround would be to 'edit' the cells with the formulae to get them to work

In code, you could do something like:

For Each c In ActiveSheet.UsedRange
    c.Formula = c.Value
Next

which will convert a textual formula to an actual formula.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DatabaseDude]

Hi Geoff,

Just found something very similar to what you mentioned in this article:

http://groups.google.com/group/micr...8f34d2fcbf9/68d90b1ca17a643e#68d90b1ca17a643e

If a cell contains a formula pasted in from a recordset, according to the article it doesn't "realize" it is to be treated as a formula until programmatically informed. So either setting its value to cell.Value (as you suggested) or cell.Formula (per the article) works.

Personally I like yours, because it's easier to follow.

Not quite so simple for generating the value of field SomeFormula within the stored procedure side, for multiple rows ... but I think that if I pass an input parameter for starting row number (row number of insert point), increment a counter with each row, and return an output parameter of the last row number ... then the formulas will match the proper worksheet rows on output. From there, CopyFromRecordset, and do range.Formula = range.Value.

Here's an emulation of interaction with the ADO recordset returned from SQL/Access (inserting records here manually for illustration of what happens in T-SQL code):

    Dim rst As New ADODB.Recordset
    Dim intStart As Integer
    Dim intRow As Integer
    Dim intRecordcount As Integer
    Dim wks As Worksheet
    Dim rng_Insert As Range
    Dim rng_Start As Range
    Dim rng_End As Range
    
    With rst.Fields
        .Append "SomeFieldName", adCurrency
        .Append "SomeOtherFieldName", adCurrency
        .Append "SomeFormula", adChar, 500
    End With
    
    ' Change start point as needed
    intStart = 9
    intRow = 0
    
    ' intStart passed to stored procedure as input parameter
    ' the recordset inserts below mimic records on SQL Server
    With rst
        .Open
        .AddNew
        If intRow = 0 Then
            intRow = intStart
        Else
            intRow = intRow + 1
        End If
        !SomeFieldName = 50
        !SomeOtherFieldName = 50
        !SomeFormula = "=A" & intRow & "+B" & intRow
        .Update
        

        .AddNew
        If intRow = 0 Then
            intRow = intStart
        Else
            intRow = intRow + 1
        End If
        !SomeFieldName = 10
        !SomeOtherFieldName = 20
        !SomeFormula = "=A" & intRow & "+B" & intRow
        .Update

        .AddNew
        If intRow = 0 Then
            intRow = intStart
        Else
            intRow = intRow + 1
        End If
        !SomeFieldName = 44
        !SomeOtherFieldName = 55
        !SomeFormula = "=A" & intRow & "+B" & intRow
        .Update

    End With
        
    rst.MoveFirst
    
    ' intRow returned from stored procedure as output parameter
    
    Set wks = ActiveWorkbook.Worksheets("Sheet1")
       
    Set rng_Insert = wks.Range("A" & intStart)
    
    rng_Insert.CopyFromRecordset rst
    
    With wks
        Set rng_Start = .Range("C" & intStart)
        Set rng_End = .Range("C" & intRow)
        .Range(rng_Start, rng_End).Formula = .Range(rng_Start, rng_End).Value
    End With
    
    rst.Close
    Set rng_End = Nothing
    Set rng_Start = Nothing
    Set rng_Insert = Nothing
    Set rst = Nothing

... THEN we get what I'd hoped for: column C contains formulas and their resulting values, and no looping thru rows.

If you all like, I can post the entire code (VBA and SQL) once I've gotten it implemented.

Thanks to one and all for their pointers and assistance.

Bryant

 

[DatabaseDude]

Following up my own post with a more efficient method ... if I use an R1C1 formula, then that makes creating the formula that will be inserted in the cell from the recordset much easier:

    Dim rst As New ADODB.Recordset
    Dim intStart As Integer
    Dim intRow As Integer
    Dim intRecordcount As Integer
    Dim wks As Worksheet
    Dim rng_Insert As Range
    Dim rng_Start As Range
    Dim rng_End As Range
    
    With rst.Fields
        .Append "SomeFieldName", adCurrency
        .Append "SomeOtherFieldName", adCurrency
        .Append "SomeFormula", adChar, 500
    End With
    
    ' Change start point as needed
    intStart = 4
    intRow = 0
    
    ' intStart passed to stored procedure as input parameter
    ' the recordset inserts below mimic records on SQL Server
    With rst
        .Open
        .AddNew
        If intRow = 0 Then
            intRow = intStart
        Else
            intRow = intRow + 1
        End If
        !SomeFieldName = 50
        !SomeOtherFieldName = 50
        !SomeFormula = "=RC[-2]+RC[-1]"
        .Update
        
        .AddNew
        If intRow = 0 Then
            intRow = intStart
        Else
            intRow = intRow + 1
        End If
        !SomeFieldName = 10
        !SomeOtherFieldName = 20
        !SomeFormula = "=RC[-2]+RC[-1]"
        .Update

        .AddNew
        If intRow = 0 Then
            intRow = intStart
        Else
            intRow = intRow + 1
        End If
        !SomeFieldName = 44
        !SomeOtherFieldName = 55
        !SomeFormula = "=RC[-2]+RC[-1]"
        .Update

    End With
        
    rst.MoveFirst
    
    ' intRow returned from stored procedure as output parameter
    
    Set wks = ActiveWorkbook.Worksheets("Sheet1")
       
    Set rng_Insert = wks.Range("A" & intStart)
    
    rng_Insert.CopyFromRecordset rst
    
    With wks
        Set rng_Start = .Range("C" & intStart)
        Set rng_End = .Range("C" & intRow)
        .Range(rng_Start, rng_End).FormulaR1C1 = .Range(rng_Start, rng_End).Value
    End With
    
    rst.Close
    Set rng_End = Nothing
    Set rng_Start = Nothing
    Set rng_Insert = Nothing
    Set rst = Nothing

 

[xlbo]

nice - glad you got it working

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question