Results 1 to 5 of 5
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    rename Names in formulas (Excel 2003)

    I have a workbook with 27 names that I need to rename. All the names start with "_" and the application I'm using that reads/imports the spreadsheet can't deal with leading underscores in the name (even though it's valid for Excel). Without breaking all the formulas that use names, what's the quickest way to do this? I do use JKP's NameManager and was hoping it could help me but it doesn't seem so.

    If I rename the names to remove the "_" then the formulas break. Should I first duplicate all 27 names w/o the leading "_" and then do a search/replace 27 times? Are there other ideas? I guess I'll stop using the leading underscores but it was my way of easily recognizing names which my VBA code uses vs. those that a user might create (and so it's easier for me to know which names to hide).

    Thnx, Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: rename Names in formulas (Excel 2003)

    The Spreadsheet Detective Excel Auditing Add-In Audit AddIn can rename names and adjust all formulas referring to the name automatically. You can download a free evaluation copy.
    Or you can wait for Excel 2007: Microsoft Excel 2007 (nee Excel 12) : Formula building improvements Part 4: Defined Names <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rename Names in formulas (Excel 2003)

    Hey that's a cool tool. It's pricey at $190 (maybe I qualify for the $53 personal license, I'm not clear on their explanation) but I'll look into it more. I'll ask my boss to let me buy it as I'm having to reverse engineer quite a few old .xls files to update them for customers and while the updated Formula Auditing tool bar is nicer, it's still a major chore.

    For this problem, I ended up writing my own code to loop through the Names, adding a new one of same name (w/o the "_") then copying the .RefersTo field. I'll then do a search/replace. I figure it's only 27 names and will do it this one time, then I'll remove all the old names.

    Thnx, Deb

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rename Names in formulas (Excel 2003)

    Renaming names as a new option for my Name Manager has been on my wish list for a looong time, but I don't seem to get round to it.

    As a workaround , I would:

    - use name manager's list option to list all your names
    - search and replace in that list to get rid of the underscores
    - upload the new names (they will be added) using name manager's pickup button.

    Then I'd use my Flexfind utility to serach and replace the now duplicated names with their new counterparts
    (making sure it also checks objects!) and finally, I'd reload name manager and get rid of all old names.

    As a check I normally run flexfind again to check for any #name! errors.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rename Names in formulas (Excel 2003)

    I ended up writing this just to duplicate the existing names:
    <pre>Public Sub renameit()
    Dim nm As Name

    For Each nm In ThisWorkbook.Names
    If InStr(1, nm.Name, "_") = 1 Then
    ' Debug.Print "add name: " & Mid(nm.Name, 2) & ", refers to: " & nm.RefersTo
    ThisWorkbook.Names.Add Mid(nm.Name, 2), RefersTo:=nm.RefersTo, Visible:=True
    End If
    Next nm

    End Sub</pre>

    I hadn't tried NameManager in the way you suggested, I guess I need to spend more time with it as it can do more than I tend to use it for. I also downloaded FlexFind and will use it instead of manually doing Find/Replace.

    Thanks for the suggestions.
    Deb

Posting Permissions

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