[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


>>> Lambert.Heenan at chartisinsurance.com 19-11-2010 22:27 >>>
x-posted to Access-L and AccessD

I have a function which returns True or False depending on whether a specific named range exists in an Excel file. It looks like this...

Function Excel_NamedRangeExists(xlApp As Excel.Application, nSheet As Integer, sRangeName As String) As Boolean
'return True if the range exists, else return false
Dim result As Variant

    On Error Resume Next
    With xlApp
        result = .Sheets(nSheet).Range(sRangeName)' <== does the actual test
        Excel_NamedRangeExists = (Err.Number = 0)
    End With
    On Error GoTo 0
End Function

So the parameters for this function are

xlApp: an Excel.Application object that is already initialized with the file in question opened
nSheet: an Integer - the number (1 to n) of the worksheet to examine for the named range
sRangeName: a string - the name of the actual range to look for.

It is annoying that this routine requires the sheet number so I thought I'd try to re-write it so that the sheet number can be discovered at run time. This is what I came up with...

Function Excel_Named_Range_Exists(xlApp As Excel.Application, sRangeName As String) As Boolean
'return True if the range exists, else return false
Dim result As Variant
Dim bHit As Boolean
Dim nSheet As Integer

    On Error Resume Next
    bHit = False
    With xlApp
        For nSheet = 1 To .Sheets.Count
            result = .Sheets(nSheet).Range(sRangeName) ' <== does the actual test
            bHit = (Err.Number = 0)
            If bHit Then
                Exit For
            End If
        Next nSheet
    End With
    On Error GoTo 0
    Excel_Named_Range_Exists = bHit
End Function

The idea is simple enough: loop through all the sheets in the workbook looking for the named range.

Note that the indicated lines in each function are identical, using the exact same parameters and data types. When I run both routines against the same Excel file with a named range in it. the old method works but yet the new one (Excel_Named_Range_Exists) simply does not find the named range. How can that be?

Puzzled (again) Lambert