[AccessD] Syntax for seeing named range in excel

Thanks for your feedback Gustav.

I think what you are saying is that if you know in advance what cells the
named range covers you can pass a range address in in the parameter
sRegionStart  with a value like "F5:J7" of "$G$5:$J$7" and then your
variation on the code will assign a name to that range.

However, my routine was written to handle the situation where you do not
know the extent of the range in advance. Instead you only know for sure that
some cell is within the range. Thus my code passes in an address in
sRegionStart like "A1", and then the execution of the line

xlApp.ActiveSheet.Range(sRegionStart).Select

causes Excel to highlight all the cells in the continuous (strictly -
'contiguous') region that includes the cell already selected (A1). The
subsequent line of code

 rng = xlApp.Selection.CurrentRegion.Address

Then retrieves the address of the complete region and uses this to set the
named range address.

All the highlighting and selecting usually takes place while  the workbook
is opened in hidden mode.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Saturday, January 21, 2006 8:26 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Syntax for seeing named range in excel


Hi Lambert

I've found that you don't need Select for anything else than if the
worksheet operates in interactive mode and you wish to display a range for
the user. 
Select exists in much code as it is what you get when you use the macro
recorder for creating what you may call rough code.

Thus, you can reduce your central code lines like this:

        xlApp.Worksheets(sSheetName).Activate
        rng = Range(sRegionStart).Address
        xlApp.ActiveWorkbook.Names.Add sRangeName, "=" & sSheetName & "!" &
rng

Can you specify the range including the $ signs, like:

  $F$5:$J$7

you don't even need Range:

        xlApp.Worksheets(sSheetName).Activate
        xlApp.ActiveWorkbook.Names.Add sRangeName, "=" & sSheetName & "!" &
sRegionStart

/gustav

>>> Lambert.Heenan at AIG.com 20-01-2006 22:22:23 >>>
Here's an example of creating named ranges....

Sub Excel_CreateNamedRange(sPath As String, sRangeName As String,  _  
	sSheetName As String, Optional sRegionStart As String = "A1") '
Creates a named range (sRangeName) in the given spreadsheet (sPath) ' on the
given worksheet (sSheetName). ' The region of data to include in the named
range includes the cell address in sRegionStart
    Dim xlApp As Excel.Application

    Dim rng As String
    
    'Check to see if the file name passed in to
    'the procedure is valid
    If Not FileExists(sPath) Then '
        MsgBox sPath & " isn't a valid path!"
        Exit Sub
    Else
        Set xlApp = CreateObject("Excel.Application")
        'xlApp.Visible = True
        xlApp.Workbooks.Open sPath
        xlApp.Worksheets(sSheetName).Activate
        xlApp.ActiveSheet.Range(sRegionStart).Select
        rng = xlApp.Selection.CurrentRegion.Address

        xlApp.ActiveWorkbook.Names.Add sRangeName, "=" & sSheetName & "!" &
rng
    End If
    Excel_CloseWorkBook xlApp, True
    Set xlApp = Nothing
End Sub


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com