[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