[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.



-----Original Message-----
From: Microsoft Access Database Discussion List [mailto:ACCESS-L at PEACH.EASE.LSOFT.COM] On Behalf Of Heenan, Lambert
Sent: Saturday, 20 November 2010 9:01 AM
To: ACCESS-L at PEACH.EASE.LSOFT.COM
Subject: Re: [AccessD] Odd Excel Behavior - so what's new?

Thanks for the quick response. Taking your points in order

The Sheets collection in Excel runs from 1 to n, not 0 to n. Another one of those nice consistency (lack of) issues with MS products. From the help file...

==============
Example
This example creates a new worksheet and then places a list of the active workbook's sheet names in the first column.

Set newSheet = Sheets.Add(Type:=xlWorksheet)
For i = 1 To Sheets.Count
    newSheet.Cells(i, 1).Value = Sheets(i).Name
Next i
==============

"result" is only in the function to receive (or not) the value of the named range. If the range exists then there should be no error, if it does not exist then an error is generated (1004).

Finally, according to the help file .Worksheets simple returns the .Sheets object in the active workbook.

But I thought I'd try using .Workseets anyway. Before doing so I just tested the existing code again...

NOW IT WORKS as expected. Go figure! So what did I do that was different: I had rebooted after sending the message to the list. Nothing else.


???

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, November 19, 2010 4:48 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Odd Excel Behavior - so what's new?

Hi Lambert

First, shouldn't it read:
        For nSheet = 0 To .Sheets.Count - 1

Second, you don't use result for anything. Wouldn't it change if the range is located or not?

Also, shouldn't you use .WorkSheets for .Sheets?

/gustav

--------------------------------------------------------------------------
The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft
international's LISTSERV(R) software.  For subscription/signoff info
and archives, see http://peach.ease.lsoft.com/archives/access-l.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L
_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended 
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review, 
retransmission, dissemination or other use of, or taking of any action in reliance 
upon this information, by persons or entities other than the intended recipient is 
prohibited.

If you have received this in error, please contact the sender and delete this e-mail 
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute 
the information contained in this e-mail and any attached files, with the permission 
of the sender.

This message has been scanned for viruses.
_______________________________________________________________________________________