[AccessD] Syntax for seeing named range in excel

>Didn't you once work with a fellow fluid in Excel? Or was that in your
Mexico days?

That was in Mexico.

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

I'm not really a spreadsheet kinda guy but I am running into a bunch of
"need to export to" and a little "need to import from".  Luckily the "import
from" is from my own exported workbooks so I know that it is good data. 

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 1:27 PM
To: accessd at databaseadvisors.com
Subject: Re: [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 

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