[AccessD] FW: Odd Excel Behavior - so what's new?

Thanks for the clarification Darryl. I *did* wonder why there were two apparently equivalent collections. As it happens the workbooks I'm dealing with do not have any Chart sheets in them, though they do have hidden worksheets.

Lambert 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Sunday, November 21, 2010 6:10 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] FW: Odd Excel Behavior - so what's new?


_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________



(x-posted AccessL and AccessD as the original question was. Sorry for those who get this twice.)

Hi Lambert.

Just for your future reference re:  the .Sheets vs .Worksheets method in Excel VBA.

There is a difference:  In short the Worksheets collection contains a typical, visible and bog standard Excel worksheet: rows, columns, cells, and formulas etc.  What most users see and expect from an Excel Workbook.  Many workbooks only contain "worksheets".

The Sheets collection, on the other hand, consist of not only a collection of worksheets as above, but also all the other types of sheets including Chart sheets, Excel 4.0 macro sheets (also known as XLM files) and Excel 5.0 dialog sheets (allows you to create custom dialog boxes etc).

If you create a chart in Excel as a sheet the Chart sheets can be created so they take up an entire worksheet - this is then counted as a "sheet".  Be aware this is different to creating a chart that is inserted as part of a worksheet (which would be counted as "worksheet").

The Excel 4 and Excel 5 sheets are legacy items used to maintain backwards compatibility and ease the transition from older versions of Excel to new. And just to further muddy the waters, there is also a Charts collection that is made up of chart sheets - as you can begin to see, charts can complicate this process a fair bit.

The Count property of the Worksheets collection contains the number of worksheets in a workbook. The Count property of the Sheets collection contains the number of all sheets in a workbook to include chart sheets and worksheets.

If your workbook ONLY contains worksheets. the the count for .sheet and .worksheet will be the same.  Try inserting a chart as a sheet and a legacy marco sheet and you will begin to see the difference when counting using the two methods.

Regards
Darryl.