<Mark.Darby@ncmail.net>De: Mark Darby 
Objet: Re: Automatic name creation
Date : vendredi 27 août 1999 22:07

Les,
   Think you could do what I've done.  I'm pumping data into an Excel
Spreadsheet from Access.  The code I've refreence below is VBA code from an
Access module, but it should be the same (or similar) for use within Excel.
----------------------
    dim oApp As New Excel.application
    dim db As Database
    dim rst As Recordset
    dim icounter As Integer
    dim strrange As String
    dim sqlString As String
    dim rwIndex As Integer
    dim colIndex As Integer
    dim ValidateStr As String

    set db = CurrentDb()                                  'Open the access
database

      oApp.workbooks.add                                'Create a new workbook
inside Excel
      oApp.worksheets.add                                ' Create a new
worksheet, change its name to Validation.
      oApp.activesheet.name = "Validation"

'  Fill the A5 with the string "Equipmenttypes"
      colIndex = 1
      rwIndex = 5
      oApp.cells(rwIndex, colIndex) = "Equipmenttypes"

'Open a recordset and fill the rows below A5 with a list of equipment types
      set rst = db.OpenRecordset("select * FROM dbo_tbl_Equipment_types ORDER
BY
                   PresentationOrder", dbOpendynaset, dbSeeChanges)
      with rst
         .MoveFirst
         do
            rwIndex = rwIndex + 1
            oApp.cells(rwIndex, colIndex) = !Equipment_type
            .Movenext
         loop Until .EOF
         .Close
      end with

'name the range in the excel spreadsheet.  Use the string value in A5 for the
range's name.
     ValidateStr = "A5:A" & CStr(rwIndex)                       '  Build a
string variable naming the cells in the range
      oApp.range(ValidateStr).Createnames top:=true    '  names the range of
cells in ValidateStr according

'  to the string contained in the top cell of the range.


Hope this helps.
Mark Darby
markdarby@aol.com


Les Morganstein wrote:

> I have a worksheet that looks something like the stuff below.  There are
> item names (ex.- report_name, prod_name) followed by a list of items below
> (ex- prod_name has "chocolate chip cookies" and "cream filled cookies."  I
> want to automatically name each list of items with the respective names at
> the top of each list.  There is always an empty row between sets of names.
> I would appreciate any help in accomplishing this.
>
> Thanks much
> Les Morganstein
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> report_name
> RMSpecInt
>
> prod_code     rev_number     prod_group     prod_subgroup
> 1234                 1                     SPEC                 Cookies
>
> site_name
> Chambers Works
>
> prod_name                                 appearance
> formula
> chocolate chip cookies             light tan with brown inclusions
> XY2Co
> cream filled cookies                  dark on outside light inside
> YX1
>
> alt_name
> chips ahoy
> Famous Amos
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~