Export working formulas to Excel

[briangriffin]

I'm surprised I haven't found the answer to this, even if it's "no".

I need to export data into Excel, and have the CR text become a working formula there.

CR Data:

1 2 "=A1+B1"


Excel Result

1 2 3



I could use XI or 2008, if it matters. Thanks for any ideas on this.

 

[SkipVought]

Hi,

Why can't you?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[briangriffin]

It just comes in as text: A1 + B1

I need to be able to change the value in A1 and have the cell with the formula update automatically - normal Excel behavior.

Am I missing something simple?

Thanks.

 

[SkipVought]

You must also set the FORMAT of the cell to GENERAL.

Then Edit the cell and hit ENTER.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[briangriffin]

I see, editing the cell works. If anyone knows of a way to do it without having to edit each cell, possible an Excel macro, please let me know. The report in question will have many records, with a couple of formulas in each record.

Thank you.

 

[SkipVought]

Paste this code in a MODULE in your Excel Workbook's VB Editor (ctr+F11 to toggle between sheet and VB Editor)

Sub Text2Num()
'select a cell in the column containing your formula text
'then RUN this macro
'SkipVought 2009 Apr 6
'========================================================
    Dim r As Range
    With ActiveSheet.UsedRange
        For Each r In Range(Cells(.Row, ActiveCell.Column), Cells(.Row + .Rows.Count - 1, ActiveCell.Column))
            With r
                .NumberFormat = "General"
                .Value = .Value
            End With
        Next
    End With
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[dgillz]

Why would you want to do this?

Typically people export to excel so they can further manipulate the data, and they feel comfortable doing this in excel.

I propose getting your desired output in Crystal. Then there is no need to export to excel. If you do not know how to get the desired output in Crystal, then start new threads with specific questions.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[briangriffin]

I agree completely, but in this case the client wants to do line by line 'what if', so I have to give them what they ask for.

On the other hand, it's a great opportunity to sell them an Xcelsius project...

Thanks.