Rocket Scientist Needed For Excel Export

[Tom Scholis]

The following exports to Excel nicely. The only problem is
the calculations in field F_4 don't kick in unless I edit the cells
manually in Excel. I know this is possible in other programs,
with and without the field titles, my accounting program
does this. F_4 is obviously a character field.

Field_1 F_2 F_3 F_4
Big Expense 122.00 355.00 =sum(b1:c1)
Little Expense 500.00 200.00 =sum(b2:c2)
Other Expense 800.00 150.00 =sum(b3:c3)

Any ideas?

[rtbtat]

Hi Tom:

Instead of dealing with export problems, why don't you simply open the A5 dbf file from within Excel and create a link so the data is always up to date?

Robert

[Mick Magnuson]

Hi Tom
When I export to Excel or 123 I create a summary database in A5 with all the correct filters and calculations I need. Then I export the summary .dbf to the spreadsheet. Not sure how you would export a function into a cell.

Mick

[Dwayne Atwood]

Greetings Tom,

In Excel you can copy the range of cells that contain the formula and then Paste Special from the edit menu and you should be able to select a radio button that says values.

This will past the values of those cells into the currently selected cell. This should export for you with no problem.

You may want to open a different Excel file and past the entire worksheet in there so that you don't mess up your current working file. Then just export that file.


Let me know how it works
Dwayne

[Tom Scholis]

This may seem like a stupid answer but the discussion is theoretical.
As I said, my accounting programs does this with row and column
totals fully functional. The result is a financial statement
that is easily subject to "what-if". I just wanted to see
if it could be done in A5 for future reference. Thanks for
your input.

[Tom Scholis]

Now there are two of us. Misery loves company you know.

[Tom Scholis]

Nice try, but sorry, doesn't seem to work for me. Thank you for
trying.

[David Boomer]

Is is possible that the export produces a text cell eg '=sum(b1:c1), instead of =sum(b1:c1)?

I wouldn't be surprized if this were the case as I am not aware of an alpha5 'export formulas to XL' function.

If so, you may have to write a short VBA (Visual Basic for Applications) script to strip off the leading '

The best way to do this is to record a macro that does this for the cells that you want changed, then edit the visual basic code that is generated...

I don't expect that this will make your day but I have done this sort of thing and it does work.
David

[Tom Scholis]

The problem is that the creation of the sheet overwrites the
older sheet and any vba macros. However, I already checked for
the " ' " prefix and that doesn't seem to be the problem. I'm
wondering if Version 5 will export to a more recent Excel that
may solve the problem. Thank you for your thoughts.