[AccessD] Syntax for seeing named range in excel

John,

Also be sure to name the individual sheets - so much better than getting 
blown away by the change of position of a sheet.  

Named ranges have been around since Lotus 123 - way back.  They really 
make things easier.  A long time ago, I needed to create a tax 
computation for payroll for my church, using whatever software was 
already on the church computer - used a spreadsheet with named ranges 
for all the tax rates and all the minimums, so I could write an IF 
formula, seven layers deep, to compute the deductions for social 
security, fica, medicare.  Each year, all I had to change for the new 
computations were the contents of the named ranges.  It took a while to 
create - and of course, these days, the church has switched to 
QuickBooks for accounting - but, at the time. . . . .

Tina

Gustav Brock wrote:

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