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

OOPS!  Scratch my claim that the code works after a reboot. Amazing what you can forget in the last hour of the working week!

The reason it "worked" was that I had changed my code from
        For nSheet = 1 To .Sheets.Count
            result = .Sheets(nSheet).Range(sRangeName)
            bHit = (Err.Number = 0)
            If bHit Then
                Exit For
            End If
        Next nSheet
	   ...
To
        For nSheet = 1 To .Sheets.Count
            bHit = Excel_NamedRangeExists(xlApp, nSheet, sRangeName)
            If bHit Then
                Exit For
            End If
        Next nSheet
        ...

i.e. I just made a call to my worksheet number specific routine that does work. More overhead, but not significant if not being called thousands of times in a query.

Still puzzled,

Lambert

-----Original Message-----
From: Heenan, Lambert 
Sent: Friday, November 19, 2010 5:01 PM
To: 'Access Developers discussion and problem solving'
Cc: ACCESS-L Email (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