Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2000
    Location
    Denver, CO
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert...Name...Define

    [img]/w3timages/icons/alien.gif[/img]
    I have a range called "database" in an Excel workbook that is eventually used to create a .dbf file for import into a FoxPro program. "Database" contains numbers and text and is always nine columns wide. However, because it is a combination of productivity results sent to me from the field, the number of rows sometimes fluctuates, usually about 560-580 rows.

    I use a macro to insert the data into the Excel worksheet. First I select and delete the current data, then go to Insert...Name...Define and delete the name "database." I then paste in the new data, and finally want to select the new range (e.g. 9 columns x the number of rows) and rename that as "database."

    To do that, I have the macro go to cell A1, then either 1) "Select Current Region" or 2) do a Shift-End-Down Arrow and a Shift-End-Right arrow to select the range of cells containing values, which is then named "database." Eventually "database" becomes a .dbf file.

    Here's the problem: Both items 1 and 2 in the previous paragraph select the former range instead of the new one. For example, last week my range contained 574 rows. This week, it only has 560 rows. However, the new "database" still contains 574 rows, with the last 14 rows blank. It would be no big deal, except the Foxpro program displays an Error when it tries to import the .dbf file, and I have to go back to Excel and manually name the "database."

    Any ideas for fixing the macro? (Note I cannot alter the Foxpro program, so it needs to be done in Excel.)

    Thanks!

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert...Name...Define

    Hi, Rick. What you're looking for is called xldown. You'll find some instructions at Microsoft Technet Article. Good luck!

  3. #3
    New Lounger
    Join Date
    Dec 2000
    Location
    Denver, CO
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert...Name...Define

    Thanks, it worked! I used this code:

    ActiveWorkbook.Names("Database").Delete
    lastCol = ActiveSheet.Range("a1").End(xlToRight).Column
    lastRow = ActiveSheet.Cells(16384, lastCol).EndxlUp).Row
    ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow, _ lastCol)).Select

    This allows me to select my new range.

    However, now I have an additional problem. Once I make the selection, I want to name it "database." When I record a macro to name it database, it refers to the cell range: ActiveWorkbook.Names.Add Name:="database", RefersToR1C1:="=dedb_in!R1C1:R580C9"

    Then, when I run the macro the next time, it makes the same range (R1C1:R580C9) the "database", rather than the selection I just made.

    Is there some way to name the new selection "database" each time without having the cells from the previous database become the new database? For example, I tried:

    ActiveWorkbook.Names.Add Name:="database", RefersToR1C1:= _
    "=dedb_in!R1C1lastRow, lastCol)"

    but it didn't work (even though I didn't get an error).

    I'm pretty new to VBA, so any help is appreciated!

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert...Name...Define

    Rick,

    Would something like:
    ActiveWorkbook.Names.Add Name:="database", RefersToR1C1:= _
    "=dedb_in!R1C1:R" & lastRow &"C" & lastCol"

    do what you want?

    Geoff
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert...Name...Define

    Time for me to come clean, Rick. Often, when I don't know WHERE to look for help, I just type it into Yahoo. For your first question, I did know that it was xldown (and, as you found, xlup, etc.), so I put that in a search in Yahoo. The first return was the link I sent you. This time I typed in ActiveWorkbook.Names.Add Name and got another Technet link: http://msdn.microsoft.com/library/officede...ddNamesObjX.htm that appears to answer your question. (But I'm not sure!) You can always post these questions in the VB/VBA forum. Just remind them your a newbie to VBA (as am I).

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert...Name...Define

    You might also look at:

    http://www.beyondtechnology.com/geeks007.shtml

    a site run by Rodney Powell. This particular page discusses the creation of dynamic named ranges, which will expand and contract based on the number of non-blank lines in your s/sheet.

    There are come limitations (as always) that may mean it won't work for you - primarily that the database has to be the only thing on the sheet, and that in order to reference that named range the s/sheet has to be open (although not active).

    I am not sure if Access (or was it FoxPro?) would deal properly with such a named range, but it might be worth a try.

  7. #7
    Bervis
    Guest

    Re: Insert...Name...Define

    To define a dynamic database choose Insert-Name-Define and in the box 'Refers to" fill in the following formula:
    =Sheet1!$A$1:INDIRECT("T"&COUNTA(Sheet1!$A:$A))
    This is assuming that your database is on Sheet1 and that it starts in A1. It finds he length of the database by counting the number of entries in column A.
    HTH

  8. #8
    Bervis
    Guest

    Re: Insert...Name...Define

    Sorry, forgot to mention that in my example the database extends to column T, hence the "T" in the formula. Please adapt this to your situation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •