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

Thank you Duane!

This looks very promising indeed (not that I would expect anything less from you), and I will check it out on Monday when I get bal to the Office. 

Lambert


-----Original Message-----
From: Microsoft Access Database Discussion List [mailto:ACCESS-L at PEACH.EASE.LSOFT.COM] On Behalf Of Duane Hookom
Sent: Friday, November 19, 2010 6:06 PM
To: ACCESS-L at PEACH.EASE.LSOFT.COM
Subject: Re: Odd Excel Behavior - so what's new?

You shouldn't have to look at worksheets with your code since range names should be unique in the workbook.
 
Function Excel_Named_Range_Exists(sRangeName As String) As Boolean
    Dim result As Variant
    On Error Resume Next
    result = Range(sRangeName)
    If Err.Number = 0 Then
        Excel_Named_Range_Exists = True
     Else
        Excel_Named_Range_Exists = False
    End If
End Function

I use this simple code to document all the range names in a workbook
 
Sub GetRangeNames()
    Dim rng As Name
    For Each rng In ThisWorkbook.Names
        Debug.Print rng.Name, rng.RefersTo
    Next
End Sub

The results are like:
selIndCode    =Validation!$V$2:$V$62
selIndCodes   =Validation!$K$2:$K$61
selIndicators =Validation!$J$2:$J$61
tblFactories  =Validation!$A$3:$C$38
tblIndCodes   =Validation!$K$2:$L$61
tblIndicators =Validation!$J$2:$K$61
tblIndNames   =Validation!$V$2:$W$62

 
Duane Hookom
MS Access MVP
 
> From: Lambert.Heenan at CHARTISINSURANCE.COM
> 
> 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

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