Using MS VC++ to access MS Excel Spreadsheet objects 
Author Message
 Using MS VC++ to access MS Excel Spreadsheet objects

Can anyone tell my why I wouldn't want to use MS VC++ to
access Microsoft Excel objects, such as spreadsheets,
ranges, charts, and drawing objects, from the Microsoft
Visual C++ environment? I understand that languages such
as python allow are easy to use for this purpose. Why not
C++?

Tom

Quote:
-----Original Message-----

Sent: 10/30/2001 7:53:11 AM
Subject: Re: Accessing MS objects from MS VC++

Of course its possible, but this is what webster's
dictionary says about it:

Main Entry: {*filter*}
Pronunciation: 'ma-s&-"ki-z&m, 'ma-z&- also 'mA-
Function: noun
1 : a {*filter*} perversion characterized by pleasure in being
subjected to pain or {*filter*} especially by a love
object
2 : pleasure in being abused or dominated : a taste for
suffering



> Can I access Microsoft Excel objects, such as
> spreadsheets, ranges, charts, and drawing objects, from
> the Microsoft Visual C++ environment?

> Does this environment provide statements like the Visual
> Basic for Excel 'For Each/Next' statement, that work on
> object collections?

> I have programmed Excel spreadsheets from Visual Basic
for
> Excel for years but would like to switch to pure Visual
> C++ if possible because I am now doing a lot of
> programming in that environment.

> Anyone have a simple C++ program to show me how to let's
> say open a spreadsheet and modify the value of cell?

> Thanks!
> Tom



Sun, 18 Apr 2004 00:54:20 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects

Quote:
> Can anyone tell my why I wouldn't want to use MS VC++ to
> access Microsoft Excel objects, such as spreadsheets,
> ranges, charts, and drawing objects, from the Microsoft
> Visual C++ environment?

I think that all of MS office products provide a COM automation interface so
that they may be driven by external applications. COM is language neutral.
If you are using COM, and if C/C++ is your language of choice, there is no
reason not to go this route.

Excel spread sheets can be regarded as relational database tables by means
of an ODBC driver that MS has for excel. You can then use the database
classes in MFC or the ODBC API in C to manipulate tables. (Note that I don't
know how much manipulation is permitted by means of the driver).

The office applications can be scripted as well. Visual Basic script is most
often used. That's an option too.

You'd probably be better off posting in another group (thiugh I don't know
ehere) as offfice apps are not discussed much here.

Regards,
Will



Sun, 18 Apr 2004 01:37:56 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects
There's no reason you can't.  Just more learning, if you don't know
VC++, and probably more typing.

It's just faster (to code) and more fun in VB.


Can anyone tell my why I wouldn't want to use MS VC++ to
access Microsoft Excel objects, such as spreadsheets,
ranges, charts, and drawing objects, from the Microsoft
Visual C++ environment? I understand that languages such
as Python allow are easy to use for this purpose. Why not
C++?

Tom

Quote:
-----Original Message-----

Sent: 10/30/2001 7:53:11 AM
Subject: Re: Accessing MS objects from MS VC++

Of course its possible, but this is what webster's
dictionary says about it:

Main Entry: {*filter*}
Pronunciation: 'ma-s&-"ki-z&m, 'ma-z&- also 'mA-
Function: noun
1 : a {*filter*} perversion characterized by pleasure in being
subjected to pain or {*filter*} especially by a love
object
2 : pleasure in being abused or dominated : a taste for
suffering



> Can I access Microsoft Excel objects, such as
> spreadsheets, ranges, charts, and drawing objects, from
> the Microsoft Visual C++ environment?

> Does this environment provide statements like the Visual
> Basic for Excel 'For Each/Next' statement, that work on
> object collections?

> I have programmed Excel spreadsheets from Visual Basic
for
> Excel for years but would like to switch to pure Visual
> C++ if possible because I am now doing a lot of
> programming in that environment.

> Anyone have a simple C++ program to show me how to let's
> say open a spreadsheet and modify the value of cell?

> Thanks!
> Tom



Sun, 18 Apr 2004 02:05:05 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects
Excel's interface is all variants and the like, it doesn't fit very well
with C++'s strong typing. When you program Excel from C++ you are forever
packing things into and extracting them from variants, or casting pointers
around. There are cases when it's not clear what types are supposed to be in
the variants etc and you are forever recompiling to try and figure out how
it's supposed to work, it really slows you down compared to something like
VB. Also beware of the 'smart' pointers, they can end up doing very stupid
things silently - on one occasion I spent hours reading the language spec to
convince myself that the compiler was behaving correctly and the pointer was
badly designed.

What I sometimes do if I want to automate Excel from a C++ program (without
having some external VB dll) is to prototype the code in VB first till I am
happy it's working and then code it up in C++

Paul


Can anyone tell my why I wouldn't want to use MS VC++ to
access Microsoft Excel objects, such as spreadsheets,
ranges, charts, and drawing objects, from the Microsoft
Visual C++ environment? I understand that languages such
as Python allow are easy to use for this purpose. Why not
C++?

Tom



Sun, 18 Apr 2004 05:04:03 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects

Quote:

> Can anyone tell my why I wouldn't want to use MS VC++ to
> access Microsoft Excel objects, such as spreadsheets,
> ranges, charts, and drawing objects, from the Microsoft
> Visual C++ environment? I understand that languages such
> as Python allow are easy to use for this purpose. Why not
> C++?

> Tom

It only works if the data in the spreadsheet is very "clean." That is,
every column contains only one kind of data and the format of each cell's
contents match the format of all the other cells in that column. The
interface decides (not very intelligently, IMHO) the format of each field
(column). If a cell in the column contains another kind of data, the
program throws an exception and there is no way that I've discovered to
read the data from that cell.

If users will be entering data manually in the spreadsheet, you have a
potential bomb in your program. The best I've been able to do is write a
utility to check the spreadsheet and tell the user which cells are
mis-formatted. The user has to go back into Excel and change the format of
the bad cells and then manually re-enter their contents (simply
re-formatting the cell or column won't help).

You can force the format of each column in Excel but a user can undo this.
Of course if all the data in the spreadsheet is entered by a program rather
than by hand, none of this applies and using C++ to read the contents works
fine (if a little slowly).

Bob



Sun, 18 Apr 2004 05:32:21 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects


Can anyone tell my why I wouldn't want to use MS VC++ to
access Microsoft Excel objects, such as spreadsheets,
ranges, charts, and drawing objects, from the Microsoft
Visual C++ environment? I understand that languages such
as Python allow are easy to use for this purpose. Why not
C++?

Tom

This can be done quite easily by using the #import directive. I don't thonk
it is much harder than in VB. (except perhaps for manlpulating SafeArrays).

Arnaud



Sun, 18 Apr 2004 01:23:10 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects

Quote:



> Can anyone tell my why I wouldn't want to use MS VC++ to
> access Microsoft Excel objects, such as spreadsheets,
> ranges, charts, and drawing objects, from the Microsoft
> Visual C++ environment? I understand that languages such
> as Python allow are easy to use for this purpose. Why not
> C++?

> Tom

> This can be done quite easily by using the #import directive. I don't thonk
> it is much harder than in VB. (except perhaps for manlpulating SafeArrays).

> Arnaud

Goto http://msdn.microsoft.com/library/default.asp/url=/libary/en-us/dno97...

watch the line break in there.

There are samples to download etc.  you can decide for yourself.



Mon, 19 Apr 2004 18:44:19 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects

Quote:
> It only works if the data in the spreadsheet is very "clean." That is,
> every column contains only one kind of data and the format of each cell's
> contents match the format of all the other cells in that column. The
> interface decides (not very intelligently, IMHO) the format of each field
> (column). If a cell in the column contains another kind of data, the
> program throws an exception and there is no way that I've discovered to
> read the data from that cell.

I don't think you'll see any exceptions if you are careful, but that
involves either being very paranoid every time you read data or checking
everying over before you begin as you suggest below. I think it's the one
time when I actually like VB's squidgy data typing. Excel certainly has
curious rules about what the data types are - my personal favourite is to
sum a column of number and get '0' as the result, not because they add up to
zero... but because Excel thinks they are text...
Quote:
> If users will be entering data manually in the spreadsheet, you have a
> potential bomb in your program. The best I've been able to do is write a
> utility to check the spreadsheet and tell the user which cells are
> mis-formatted. The user has to go back into Excel and change the format of
> the bad cells and then manually re-enter their contents (simply
> re-formatting the cell or column won't help).



Tue, 20 Apr 2004 06:48:52 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects

Quote:

> > It only works if the data in the spreadsheet is very "clean." That is,
> > every column contains only one kind of data and the format of each cell's
> > contents match the format of all the other cells in that column. The
> > interface decides (not very intelligently, IMHO) the format of each field
> > (column). If a cell in the column contains another kind of data, the
> > program throws an exception and there is no way that I've discovered to
> > read the data from that cell.

> I don't think you'll see any exceptions if you are careful, but that
> involves either being very paranoid every time you read data or checking
> everying over before you begin as you suggest below. I think it's the one
> time when I actually like VB's squidgy data typing. Excel certainly has
> curious rules about what the data types are - my personal favourite is to
> sum a column of number and get '0' as the result, not because they add up to
> zero... but because Excel thinks they are text...

Here's a way to test your theory. Format a column as text only and enter a
series of numbers. Now reformat the column as "general" and enter another
number somewhere in the column. Now try to read the data in that cell using
VB or C. As far as I know, you can't.

Obviously, this isn't likely to happen in real life but here's how it can.
Take a column of numbers formatted as text and cut and paste them into a
spreadsheet column formatted as "general." Now reenter some of the numbers
or edit them. The new numbers are now formatted as "number" even though the
others are formatted as text. Reading or writing to those cells will throw
an exception and, worse yet, there is no way to get the data from them that
I know of.

Bob

Bob



Thu, 22 Apr 2004 05:34:18 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects

Quote:
> Here's a way to test your theory. Format a column as text only and enter a
> series of numbers. Now reformat the column as "general" and enter another
> number somewhere in the column. Now try to read the data in that cell
using
> VB or C. As far as I know, you can't.

I'm probably drifting off topic here, but I don't read any VB ngs... oh well

I have tried your example (in VB, I'm lazy :) ) and didn't encounter  any
problems. I can read all the numbers, the numbers entered with 'text' format
are reported to be of type String and the numbers entered with 'general'
format are reported to be doubles - no surprises there. Moreover because of
VB's implicit conversion rules the strings returned can be used as numbers
in arithmetic and return True when passed to IsNumeric. The same could be
achieved in VC (but taking more time to write of course).

Quote:
> Obviously, this isn't likely to happen in real life but here's how it can.

Oh I'm sure it is, end users are very good at breaking Excel spreadsheets
which are meant to the automatically processed.

Quote:
> Take a column of numbers formatted as text and cut and paste them into a
> spreadsheet column formatted as "general." Now reenter some of the numbers
> or edit them. The new numbers are now formatted as "number" even though
the
> others are formatted as text. Reading or writing to those cells will throw
> an exception and, worse yet, there is no way to get the data from them
that
> I know of.

The format of a cell is not the same as the data type of it's contents -
though the two are related and Excel derives the data type from the text
entered and the formatting applied at the time. Formatting applied
afterwards only changes what you see, not what is stored. This is
complicated further when you use automation, for example when manipulating a
cell with the Date format the Value Value2 and Text properties can all give
different results, so it's difficult to say what the "real" contents of the
cell are. Personally I dislike the way that numbers are *almost* equivalent
to the string that represents them, but not quite. They should either be
indistinguishable so that their appearance is merely a function of the
current formatting, or they should be clearly separate data types that you
have control over.

Paul



Fri, 23 Apr 2004 01:01:18 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects

Quote:

> > Here's a way to test your theory. Format a column as text only and enter a
> > series of numbers. Now reformat the column as "general" and enter another
> > number somewhere in the column. Now try to read the data in that cell
> using
> > VB or C. As far as I know, you can't.

> I'm probably drifting off topic here, but I don't read any VB ngs... oh well

> I have tried your example (in VB, I'm lazy :) ) and didn't encounter  any
> problems. I can read all the numbers, the numbers entered with 'text' format
> are reported to be of type String and the numbers entered with 'general'
> format are reported to be doubles - no surprises there. Moreover because of
> VB's implicit conversion rules the strings returned can be used as numbers
> in arithmetic and return True when passed to IsNumeric. The same could be
> achieved in VC (but taking more time to write of course).

Fascinating. I've never tried using VB for this. In C++, it has always
caused a numeric overflow error when reading the numbers that are not text.
The COleVariant comes back as "other" with no data in it. Perhaps it's C's
exception handling that causes the problem. Were you using DAO to read the
cells?

- Show quoted text -

Quote:

> > Obviously, this isn't likely to happen in real life but here's how it can.

> Oh I'm sure it is, end users are very good at breaking Excel spreadsheets
> which are meant to the automatically processed.

> > Take a column of numbers formatted as text and cut and paste them into a
> > spreadsheet column formatted as "general." Now reenter some of the numbers
> > or edit them. The new numbers are now formatted as "number" even though
> the
> > others are formatted as text. Reading or writing to those cells will throw
> > an exception and, worse yet, there is no way to get the data from them
> that
> > I know of.

> The format of a cell is not the same as the data type of it's contents -
> though the two are related and Excel derives the data type from the text
> entered and the formatting applied at the time. Formatting applied
> afterwards only changes what you see, not what is stored. This is
> complicated further when you use automation, for example when manipulating a
> cell with the Date format the Value Value2 and Text properties can all give
> different results, so it's difficult to say what the "real" contents of the
> cell are. Personally I dislike the way that numbers are *almost* equivalent
> to the string that represents them, but not quite. They should either be
> indistinguishable so that their appearance is merely a function of the
> current formatting, or they should be clearly separate data types that you
> have control over.

Right. IIRC, neither Quattro nor Lotus did it this way. In them, changing
the format of a cell actually changed the format of the cell's current
contents. IMHO, this makes more sense although I'm sure MS would tell us
that Excel's behavior is "by design."

DAO, through Jet, decides on the format of each column not from the actual
column format, but by sampling the first few cells in the column. If later
cells in the column don't match up, I've always gotten the "fatal"
exception.

Bob



Fri, 23 Apr 2004 16:01:24 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects

Quote:
> Fascinating. I've never tried using VB for this. In C++, it has always
> caused a numeric overflow error when reading the numbers that are not
text.
> The COleVariant comes back as "other" with no data in it. Perhaps it's C's
> exception handling that causes the problem. Were you using DAO to read the
> cells?

I was just using plain old automation. I've read 'numbers that are not text'
from Excel using VC before and not had any problems, I've never tried it
with DAO though.

Quote:
> Right. IIRC, neither Quattro nor Lotus did it this way. In them, changing
> the format of a cell actually changed the format of the cell's current
> contents. IMHO, this makes more sense although I'm sure MS would tell us
> that Excel's behavior is "by design."

Looks like that, I can't think of any good reason for Excel to do what it
does. I guess we just have to live with it. There are several KB articles
about this Q181298 for Q141765 for example.

Quote:
> DAO, through Jet, decides on the format of each column not from the actual
> column format, but by sampling the first few cells in the column. If later
> cells in the column don't match up, I've always gotten the "fatal"
> exception.

I can well believe that DAO chokes when strange things are done to cells
half way down a column. I wouldn't expect it's a 'fatal' exception that you
couldn't continue from (well as long as you catch it that is!). How about
locking the worksheet to prevent users from altering the cell formatting,
but leaving the cells themselves unlocked so that data can still be typed
into them. Other alternatives would be to use Excel's object model to read
the data, or alternately pre-processing the workbook to make sure everything
in it is sensible before usinh DAO on it.

Paul



Sat, 24 Apr 2004 04:38:33 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects

Quote:
> Fascinating. I've never tried using VB for this. In C++, it has always
> caused a numeric overflow error when reading the numbers that are not
text.
> The COleVariant comes back as "other" with no data in it. Perhaps it's C's
> exception handling that causes the problem. Were you using DAO to read the
> cells?

I tried the same with VC (using #import), managed to read all the numbers
fine. Every number entered with a number (or general format) shows up as a
double, everything entered with a Text format shows up as a BSTR.

It also reminded me of another very good reason to not try automating Excel
with VC though. Excel has some rather weird things when not used from VB
(related to lcids and such). I also had problems using the VC-specific
propgets/puts tho the Getxxx() version of the properties that appear as
normal funtion calls work ok.

Paul



Sat, 24 Apr 2004 07:18:05 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects

Quote:

> > Fascinating. I've never tried using VB for this. In C++, it has always
> > caused a numeric overflow error when reading the numbers that are not
> text.
> > The COleVariant comes back as "other" with no data in it. Perhaps it's C's
> > exception handling that causes the problem. Were you using DAO to read the
> > cells?

> I was just using plain old automation. I've read 'numbers that are not text'
> from Excel using VC before and not had any problems, I've never tried it
> with DAO though.

> > Right. IIRC, neither Quattro nor Lotus did it this way. In them, changing
> > the format of a cell actually changed the format of the cell's current
> > contents. IMHO, this makes more sense although I'm sure MS would tell us
> > that Excel's behavior is "by design."

> Looks like that, I can't think of any good reason for Excel to do what it
> does. I guess we just have to live with it. There are several KB articles
> about this Q181298 for Q141765 for example.

> > DAO, through Jet, decides on the format of each column not from the actual
> > column format, but by sampling the first few cells in the column. If later
> > cells in the column don't match up, I've always gotten the "fatal"
> > exception.

> I can well believe that DAO chokes when strange things are done to cells
> half way down a column. I wouldn't expect it's a 'fatal' exception that you
> couldn't continue from (well as long as you catch it that is!). How about
> locking the worksheet to prevent users from altering the cell formatting,
> but leaving the cells themselves unlocked so that data can still be typed
> into them. Other alternatives would be to use Excel's object model to read
> the data, or alternately pre-processing the workbook to make sure everything
> in it is sensible before usinh DAO on it.

I should have guessed you were using automation. My program needs to read
from a variety of file types so automation isn't a good solution. Locking
isn't a good solution, unfortunately, because the users create the
spreadsheets themselves. I do have a utility that checks cell formats and
warns the user about bad cells.

You're right that the exceptions aren't really fatal (that's why I put
"fatal" in quotation marks). They're fatal for me, however, since I can't
get the contents of the cells and can't proceed without the information.

Bob



Sat, 24 Apr 2004 12:03:14 GMT  
 Using MS VC++ to access MS Excel Spreadsheet objects

Quote:
> I should have guessed you were using automation. My program needs to read
> from a variety of file types so automation isn't a good solution. Locking
> isn't a good solution, unfortunately, because the users create the
> spreadsheets themselves. I do have a utility that checks cell formats and
> warns the user about bad cells.

If the user has that much freedom in creating the spreadsheet then you will
always get 'broken' spreadsheets. The contents of a spreadsheet created like
that should be treated as user input (i.e. with extreme paranoia). It isn't
DAO's purpose in life to be able to flexibly read arbitrary user input, so
little wonder it goes wrong. Even if you warn the user, unless they are
actually *forced* to correct any problems (e.g. they can't submit it until
it is fixed) they'll ignore the warnings.
Maybe you can provide a template or a wizard of some sort to help them in
the creation of the spreadsheet ? Would having an xls->xls converter (as an
input filter before DAO touches it) to ensure the output spreadsheet is
correct help?

Quote:
> You're right that the exceptions aren't really fatal (that's why I put
> "fatal" in quotation marks). They're fatal for me, however, since I can't
> get the contents of the cells and can't proceed without the information.

If  your program has been given bad input, the the best it can do is spit it
out gracefully really (until they invent keyboards that give users mild
electric shocks when they do silly things!). The user's and programmer's
idea of 'bad input' are of course worlds apart.

Paul



Sun, 25 Apr 2004 04:29:32 GMT  
 
 [ 19 post ]  Go to page: [1] [2]

 Relevant Pages 

1. How export listview-data to ms access or ms excel

2. Accessing MS Access Objects in VC

3. Accessing an Ms Access 2000 Query using VC++, via ODBC

4. Error Accessing an Excel spreadsheet using ODBC in an ISAPI

5. Using COM to access an Excel spreadsheet

6. Using MS Access forms in VC++

7. version 6 of MS VC++ and Access 2000 using dao

8. Using VC++ 6.0 as an automation client to view and print reports from MS Access 97

9. version 6 of MS VC++ and Access 2000 using dao

10. Error using MS Access with SQL DISTINCT in VC++

11. MS VC++ 4.0 and MS VC++ 1.52 Coexistance?

12. Error Accessing MS-Access using ADO..

 

 
Powered by phpBB® Forum Software