Daniel Berger wrote in post #999984:
Yep, that's correct - I want the scripts to work across a variety of
platforms, with as little dependence on outside applications and
libraries as possible. This is partly because the scripts I'm writing
may be used by a few others; I don't know about what software they will
or won't have on their systems, and they will likely not be too willing
to resolve too many dependency issues. I do have office installed on one
of my systems, but don't have access to that one right now, and will not
buy another copy just for this task.
There are other use cases for what you want to do, which is why I'm
interested in any problems you come across and any ways you solve
them. For example: I distrust spreadsheets for making important
calculations (too easy to make errors in obscure cells without
noticing), but they are very useful for displaying data. So I make
some calculations using Ruby (or whatever), and then display the
results on a worksheet page, using formulas to generate some of the
displayed results for the same reasons you give in a later post "it's
good to have the formulas in there, in part so that whoever is viewing
the formulas can follow the process of
how something is derived (without too much effort)". An important part
of what I'm doing is that the worksheets will be usable in even quite
old versions of Excel, so I only want to use elementary features of
Excel.
Actually, the original version of this used Microsoft Excel
VisualBasic for Applications, with all the calculations being done in
VBA, and using VBA to generate the worksheet pages. But I'd rather use
Ruby for the calculations, so I'm currently rewriting it, hence my
interest in what you're doing.
I'm assuming you've looked at things like this
http://www.cpearson.com/excel/optimize.htm
which has a section on using VBA to force calculations: I haven't
tried adapting the VBA code to run from Ruby accessing Excel, but it
should be possible?
As a very orthogonal suggestion: one thing that was worrying me about
my approach was what if I couldn't manage to get Ruby to write
anything directly into Excel. (You haven't got that problem.) But a
possible solution occurred to me: use Ruby (or whatever) to generate a
text file which has a list of cells and values or formulas (and
formatting) to be entered into (or used by) each cell. Then write a
VBA function to read such text files and generate the worksheet(s): a
little messy, but fairly easy to do, and I was much happier once I had
a backup plan if directly accessing Excel through Ruby didn't work.
