[AccessD] Syntax for seeing named range in excel

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

And the routines it uses...

Function FileExists(strFile As String) As Boolean
  ' Comments  : Determines if the file exists
  '             Works for hidden files and folders
  ' Parameters: strFile - file to check
  ' Returns   : True if the file exists, otherwise false
  Dim intAttr As Integer
  Dim errnum As Long
  On Error Resume Next
  intAttr = GetAttr(strFile)
  errnum = Err.Number
  FileExists = (Err.Number = 0)
End Function


Sub Excel_CloseWorkBook(xlApp As Excel.Application, Optional bSaveChanges As
Boolean = False)
Dim wb As Excel.Workbook
    On Error Resume Next
    If xlApp.Name > "" Then ' forces an error if the xlApp object is not set
    End If
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
        For Each wb In xlApp.Workbooks         'Close all open workbooks
            wb.Close bSaveChanges
        Next wb
    xlApp.UserControl = False
    Set xlApp = Nothing
End Sub



HTH

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Friday, January 20, 2006 3:08 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Syntax for seeing named range in excel


Gustav,

>Nice to hear. Please let me leave that as the quote of the week!

Well, you know how it goes with this list, there is so much traffic that you
have to ignore what you don't use.

I just don't use Excel much for my own purposes.  I am moving this summer to
North Carolina and have to get real about my money so this last week I did a
"Income/Expense" workbook with three worksheets for income, expenses and
credit.  It wasn't until I started naming ranges there and then using the
names that I discovered just how cool they are.

>So, everyone else, Named Ranges are so simple to apply and use, the 
>feature
has been there since "forever", but still most users and many programmers
don't know about it, thus wasting time hammering primitive cell coordinates
into formulas and code.

Amen Brother!  

Now I need to figure out how to CREATE named ranges when I build a
spreadsheet in VBA.

John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, January 20, 2006 2:05 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Syntax for seeing named range in excel

Hi John

Nice to hear. Please let me leave that as the quote of the week!

However, I haven't missed an opportunity to mention this through the
years(!) - speaking for deaf ears?

So, everyone else, Named Ranges are so simple to apply and use, the feature
has been there since "forever", but still most users and many programmers
don't know about it, thus wasting time hammering primitive cell coordinates
into formulas and code.

/gustav

>>> jwcolby at ColbyConsulting.com 20-01-2006 19:40:29 >>>

I just really discovered named ranges and boy is THAT cool, even inside the
spreadsheet.


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

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