[AccessD] Syntax for seeing named range in excel

Hi John

That's an Excel book you are asking for ...
Didn't you once work with a fellow fluid in Excel? Or was that in your Mexico days?

I'm certainly no expert in Excel but I've found that if you define everything you touch as ranges and define and use Named Ranges to a degree where you may find it ridiculous (for a single row or column or even a cell) you are pretty well off, as your code becomes much easier to read and maintain. Also, if you are working on an existing file - you can easily adjust the layout of worksheet without changing any code as the Named Ranges will persist.

Further, don't take anything for granted; a worksheet used to be no. 2, one day it is no. 1, so always move it to position 2 if that is important, and so on.

And never ever use Selection unless, of course, you wish to highlight an area for the user in interactive mode.

For your entertainment, I have a single function which has proven extremely useful for me as Excel lookup in a range behaves in a way you - with a database background - would not expect. Not like DLookup. It has in-line documentation as it otherwise for non Excel gurus like me is nearly impossible to understand what it does and why:

Function RLookup( _
  ByVal rngLookup As Range, _
  ByVal strField As String, _
  Optional intSearchOrder As Integer = xlByRows, _
  Optional intOffset As Integer = 1) _
  As Variant

' Looks up a value in the named range rngLookup from the cell,
' offset intOffset rows below or columns right from the found cell.
'
' 2000-07-30.
' Cactus Data ApS, Gustav Brock

' Default intSearchOrder is xlByRows, as named ranges built this way are
' easily attached by Access.
  
  Dim rng As Range
  Dim intRow As Integer
  Dim intColumn As Integer
  
  ' No special error handling.
  On Error Resume Next

  If intSearchOrder = xlByRows Then
    ' Search by rows.
    ' Read from found row, offset intOffset rows, and found column.
  Else
    ' Search by columns.
    ' Read from found row and found column, offset intOffset columns.
    intSearchOrder = xlByColumns
  End If
  
  With rngLookup
    ' Start search from upper left cell in range by starting in lower right cell.
    ' Search case sensitive for whole words in values only.
    Set rng = .Find(strField, .Cells(.Rows.Count, .Columns.Count), xlValues, xlWhole, intSearchOrder, , True)
  End With
  If Not rng Is Nothing Then
    ' Searched value found.
    ' Lookup value to retrieve.
    With rng
      intRow = .Row - (intOffset * (intSearchOrder = xlByRows))
      intColumn = .Column - (intOffset * (intSearchOrder = xlByColumns))
      ' Return value, offset intOffset rows or columns from cell with found value.
      RLookup = .Worksheet.Cells(intRow, intColumn).Value
    End With
    Set rng = Nothing
  End If

End Function

/gustav

>>> jwcolby at ColbyConsulting.com 20-01-2006 18:25:46 >>>
LOL, all service packs applied so I guess I build a brand new workbook.

Does anyone have a library of functions for doing things out in a
spreadsheet?  I need to do things like name ranges, lock ranges, format
ranges etc.

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 10:31 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Syntax for seeing named range in excel

Hi John

Yes, it is possible to update a linked xls sheet/range - except if you have
applied the latest servicepack. 

A Named Range can be created like this:

  ActiveWorkbook.Names.Add "John", "=$C1:$D4" 

or more specific:

  ActiveWorkbook.Names.Add "John", "=NameOfWorksheet!$C1:$D4" 

If that Named Range exists, it will be adjusted to the new range settings.

/gustav

>>> jwcolby at ColbyConsulting.com 19-01-2006 19:53 >>>
OK oh hero, now is it possible to update the named range through a query?  I
tried saving the working select query, then opening that and modifying the
data but that definitely does not work.  Is this part of the "patent
lawsuit" changes or am I just doing something wrong?  

If a "Update" syntax would work that would be fine.  I am running code
anyway, not manually updating the data as I was trying to do in the test
above.

As I mentioned, I have a template with the named ranges all set up.  I would
like to be able to update that spreadsheet (append data into it) rather than
have to build up a new spreadsheet.  I can create the spreadsheet but then I
would need the syntax to create named ranges so I can get the data later -
using the syntax I just got from you.


John W. Colby
www.ColbyConsulting.com