writing to Excel spreadsheet

I'd like to have my app write values to specific cells in an existing,
pre-formatted Excel spreadsheet.  Can anyone provide example code on how to
do this?

My app queries a database and writes out to a text file with comma separated
values, which Excel can import.  I'd like to write directly to the Excel
file without going through the csv step.

Thanks in advance.

Ted

"Ted Shen" <[hidden email]> wrote in message
news:[hidden email]...
> I'd like to have my app write values to specific cells in an existing,
> pre-formatted Excel spreadsheet.  Can anyone provide example code on how
to
> do this?
>
> My app queries a database and writes out to a text file with comma
separated
> values, which Excel can import.  I'd like to write directly to the Excel
> file without going through the csv step.

See AXTypeLibraryAnalyzer class<<example3 for an example.  I have used Excel
from Dolphin quite a bit, it works well.  I generated the classes with the
ActiveX Wizzard.  I recall having to modify a few methods, it was a while
ago so the wizard may have improved.  If you do a http://groups.google.com
search limited to c.l.s.d for "Excel" I expect you will find a lot of
information.

Chris

Chris,

    I tested the example3 in office XP find no excel9.olb so I put directly
the excel.exe thatgenerated a lot of constants. Then the makes the excel to
open a sheet, changes it's caption and fails doing this:

"Add a title in the sheet itself"

range := (excel range: 'A1' cell2: VARIANT unspecified) value: caption

It thows a FACILITY_DISPATCH telling the type missmatch error.

    Any clue to make it working with office XP?

regards,


--
Sebastián Sastre
[hidden email]
www.seaswork.com.ar
















"Christopher J. Demers" <[hidden email]> escribió en el
mensaje news:bsd0nl$c0303$[hidden email]... Excel
> from Dolphin quite a bit, it works well.  I generated the classes with the
> ActiveX Wizzard.  I recall having to modify a few methods, it was a while
> ago so the wizard may have improved.  If you do a http://groups.google.com
> search limited to c.l.s.d for "Excel" I expect you will find a lot of
> information.
>
> Chris
>
>

Ted,

> > My app queries a database and writes out to a text file with comma
> separated
> > values, which Excel can import.  I'd like to write directly to the Excel
> > file without going through the csv step.
>
> See AXTypeLibraryAnalyzer class<<example3 for an example.  I have used
Excel
> from Dolphin quite a bit, it works well.  I generated the classes with the
> ActiveX Wizzard.  I recall having to modify a few methods, it was a while
> ago so the wizard may have improved.  If you do a http://groups.google.com
> search limited to c.l.s.d for "Excel" I expect you will find a lot of
> information.

The TypeLibraryAnalyzer is a very impressive system.  As good as it is, it
can only work with what it is given.  The Microsoft "object models" are
probably not as well designed as they might be (how's that for a diplomatic
statement?).  As a result, the generated classes are numerous and big, and
IMHO, not at all helpful.

There is a Dummies book (from the * for Dummies series) on VB (or perhaps
VBA) that I found very helpful in assembling my WordAutomation package.
It's not handy at the moment, but my recollection is that it would be useful
to you also.  It includes a CD with some additional documentation, which is
probably the most useful part of the book.

I start with a "blank" word document, populate some cells in a table in a
header, and then add headings and paragraphs to fill in the body of
document.  The goal was to be able to "borrow" word's printing, which I plan
to replace with my own code at some point in the future.  Despite the broad
functionality, the non-generated package is quite small and easy to
understand and extend.  I suspect your experience would be similar.

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

I had the same 'type mismatch' result after substituting xl5en32.olb for
excel9.olb on my system.  example3 sends #value: to anExcelRange where the
argument of #value: is aString or anArray, but the argument in
ExcelRange>>value: is described as rangeValueDataType.  Is
ExcelRange>>value: expecting some object that wraps the actual value?

Ted


"Sebastian Sastre" <[hidden email]> wrote in message
news:bsf2p2$c6fat$[hidden email]...
> Chris,
>
>     I tested the example3 in office XP find no excel9.olb so I put
directly
> the excel.exe thatgenerated a lot of constants. Then the makes the excel
to el
> mensaje news:bsd0nl$c0303$[hidden email]...
> > "Ted Shen" <[hidden email]> wrote in message
> > news:[hidden email]...
> > > I'd like to have my app write values to specific cells in an existing,
> > > pre-formatted Excel spreadsheet.  Can anyone provide example code on
how
> > to
> > > do this?
> > >
> > > My app queries a database and writes out to a text file with comma
> > separated
> > > values, which Excel can import.  I'd like to write directly to the
Excel
> > > file without going through the csv step.
> >
> > See AXTypeLibraryAnalyzer class<<example3 for an example.  I have used
> Excel
> > from Dolphin quite a bit, it works well.  I generated the classes with
the
> > ActiveX Wizzard.  I recall having to modify a few methods, it was a
while
> > ago so the wizard may have improved.  If you do a
http://groups.google.com
> > search limited to c.l.s.d for "Excel" I expect you will find a lot of
> > information.
> >
> > Chris
> >
> >
>
>

Hi Ted,

> I'd like to have my app write values to specific cells in an existing,
> pre-formatted Excel spreadsheet.  Can anyone provide example code on how
to
> do this?
>
> My app queries a database and writes out to a text file with comma
separated
> values, which Excel can import.  I'd like to write directly to the Excel
> file without going through the csv step.

If you should not make cell formatting or something similiar try ODBC access
to Excel as alternative.

--
Dmitry Zamotkin

No, message

 range := (excel range: 'A1' cell2: VARIANT unspecified) value:
 caption

get value from excel cell.

For set value of cell need use message value2:

example:

 range := (excel range: 'A1' cell2: VARIANT unspecified) value2: caption


Valdimir Musulainen

Thanks, #value2: works well in setting the value of the cell.  I think
#value2 gets the value from the cell.

Ted

"Vladimir Musulainen" <[hidden email]> wrote in message
news:bsgrnc$1255$[hidden email]...

I'd appreciate any advice on how to open an existing spreadsheet.  The customer
will create the Excel spreadsheet with the tables laid out the way he wants it;
I just need to fill in the appropriate cells with the query results.  Thanks.

Ted

"Atshens2" <[hidden email]> wrote in message
news:[hidden email]...
> I'd appreciate any advice on how to open an existing spreadsheet.  The
customer
> will create the Excel spreadsheet with the tables laid out the way he
wants it;
> I just need to fill in the appropriate cells with the query results.
Thanks.

I added the method bellow to the class that the ActiveX wizard generated,
essentially it defaults all the optional parameters to VARIANT unspecified.
However I am not sure if this is still needed.  I think Blair was thinking
about automatically generating methods like this, but I am not sure if that
has been done or not.

Consider looking at the Excel VBA help, it will document all the features
Excel exposes.  Another trick I like to use when I want to do something
complex is to do it in Excel while recording a macro.  That will generate
VBA code that I can translate to Smalltalk.  It is a great way to see how to
do things in Excel.

============
ExcelWorkbooks<<open: filename
"CJD 8-27-2001 Added to default optional arguments.
Answer the <Excel_Workbook> result of invoking the COM Object's Open()
method."
| answer |
answer := Excel_Workbook newPointer.
self
Open: filename
UpdateLinks: VARIANT unspecified
ReadOnly: VARIANT unspecified
Format: VARIANT unspecified
Password: VARIANT unspecified
WriteResPassword: VARIANT unspecified
IgnoreReadOnlyRecommended: VARIANT unspecified
Origin: VARIANT unspecified
Delimiter: VARIANT unspecified
Editable: VARIANT unspecified
Notify: VARIANT unspecified
Converter: VARIANT unspecified
AddToMru: VARIANT unspecified
lcid: nil
RHS: answer.
^answer asObject
============
Here is how to use it:
============
xlApp := Excel_Application new.
xlWorkbook := xlApp workbooks open: filePath.
============

Chris

"Christopher J. Demers" <[hidden email]> wrote in
message news:bssjoa$10182$[hidden email]... unspecified.
> However I am not sure if this is still needed.  I think Blair was thinking
> about automatically generating methods like this, but I am not sure if
that
> has been done or not.

As of 5.02 the analyzer generates a wrapper that defaults all optional
parameters.

Regards

Blair

I put an underscore before the Open as follows:
> ExcelWorkbooks<<open: filename
> "CJD 8-27-2001 Added to default optional arguments.
> Answer the <Excel_Workbook> result of invoking the COM Object's Open()
> method."
> | answer |
> answer := Excel_Workbook newPointer.
> self
> _Open: filename
etc.

and then it worked great.  Thanks.

Ted


"Christopher J. Demers" <[hidden email]> wrote in
message news:bssjoa$10182$[hidden email]... unspecified.
> However I am not sure if this is still needed.  I think Blair was thinking
> about automatically generating methods like this, but I am not sure if
that
> has been done or not.
>
> Consider looking at the Excel VBA help, it will document all the features
> Excel exposes.  Another trick I like to use when I want to do something
> complex is to do it in Excel while recording a macro.  That will generate
> VBA code that I can translate to Smalltalk.  It is a great way to see how
to