Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Clear Excel 'Names' (O2K SP1)

    I am trying to automate an Excel spreadsheet to run a saved query. The run-query macro includes the following code:<pre> With ActiveSheet.QueryTables.Add(Connection:= _
    "FINDER;C:WINDOWSApplication DataMicrosoftQueriesQryGetBK.dqy", _
    Destination:=Range("A8"))
    .Name = "QryGetBK"</pre>

    Each time this is run, it adds to the workbook's 'Names' list: QryGetBK, QryGetBK_1, QryGetBK_2, and so on. I tried to include the code ActiveWorkbook.Names("QryGetBK").Delete after the End With statement, but all I got were error messages.

    I would prefer to not create any Names at all with this query, but as an alternate an "if exist, delete" type routine that would run when the workbook was opened or closed would be more than adequate. What would be the best way to make this happen?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Clear Excel 'Names' (O2K SP1)

    David,

    Try somethinkg like <pre>Dim cName As Name
    For Each cName In ThisWorkbook.Names
    If Left(cName.Name, 8) = "QryGetBK" Then
    cName.Delete
    End If
    Next</pre>

    in your Workbook_Open or WorkBook_Close events.

    It should rid the workbook of all range names starting with QryGetBK

    Or you could incorporate it at the end of your QueryTables code, if you are happy that it is safe to do so.

    Andrew C

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Clear Excel 'Names' (O2K SP1)

    I gave it a try, thanks, but it behaved rather oddly. The loop cycled through all of the names in the workbood but didn't match on anything. I was able to get it to delete all names in the workbook by removing the conditional check, but that then required re-writing the rest of the code to use non-named references. Is there a way to trigger debug to show each name that is being evaulated?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Clear Excel 'Names' (O2K SP1)

    Try

    For Each cName in Thisworkbook.Names
    Debug.Print cName.Name
    Next

    That should print each name to the immediate window.

    Is it possible that the code is in a different workbook that the active workbook ?

    If so replace ThisWorkbook with ActiveWorkbook

    Andrew C

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Clear Excel 'Names' (O2K SP1)

    I was actually able to find the names with a little bit of poking around. I opened the Locals window and stepped through the code a few times before spotting the details I was looking for. I still have to try it out, but it looks as if the worksheet name is saved as part of the Name.
    Attached Images Attached Images

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Clear Excel 'Names' (O2K SP1)

    David,

    The worksheet name will be added if a given range name is used on more than one sheet, so that theh ranges can be distinguished. However the sheetname might not be included in the first instance of the name. Try the following and see if it works for you ;<pre> Dim cName As Name
    For Each cName In ActiveWorkbook.Names
    If InStr(1, cName.Name, "QryGetBK") > 0 Then
    cName.Delete
    End If
    Next</pre>

    That should deal with any rangename that containsQryGetBK as part of the name.

    Andrew C

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Clear Excel 'Names' (O2K SP1)

    Absolutely fantastic! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Now it won't matter if the sheet name gets renamed along the way, and I was able to put all my other Name'd ranges back and know there won't be any snafu's with them. That little snippet of code should be quite useful in the future. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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