Trailing number on Named Region

[Kieran H]

Hi,

I realise that this has probably been discussed previously but I
failed to find a useful thread.

I'm creating named regions programatically (corresponding to
querytables)
but I end up with an underscore and a number following my chosen name.

What is the best way of correcting / preventing this behaviour

Note that my code attempts to clear existing names with the
following:

With ThisWorkbook.Sheets("data")
.Cells.Clear
For Each qt In .QueryTables
qt.Delete
Next
For Each nName In .Names
nName.Delete
Next
End With

Any help would be appreciated

Regards

Kieran

 

[Peter T]

There's nothing wrong with a Name that's named MyName_123
I have no idea what logic you used to create unique names but if you don't
want those trailing underscores and numbers you will need to work out
something different

I assume you only want to delete only worksheet level names like
"data!MyName_123" because that's what your code does. However if you want to
all delete Workbook level names, which might refer to ranges on your sheet
as well as any others, you need to do

For each nName in ThisWorkbook.Names
nName.delete

Come back if you only want to delete Names that refer to cells on your
sheet.

Regards,
Peter T

 

[Jim Rech]

That suggests that you're trying to create an illegal name. You cannot for
instance create a name with a space or that looks like a cell address.

--
Jim
| Hi,
|
| I realise that this has probably been discussed previously but I
| failed to find a useful thread.
|
| I'm creating named regions programatically (corresponding to
| querytables)
| but I end up with an underscore and a number following my chosen name.
|
| What is the best way of correcting / preventing this behaviour
|
| Note that my code attempts to clear existing names with the
| following:
|
| With ThisWorkbook.Sheets("data")
| .Cells.Clear
| For Each qt In .QueryTables
| qt.Delete
| Next
| For Each nName In .Names
| nName.Delete
| Next
| End With
|
| Any help would be appreciated
|
| Regards
|
| Kieran

 

[Kieran H]

To clarify:

My code does the following:

1. Clears all named regions and query tables on a partiular
worksheet.

2. Connects to a remote SQL server and pull back data into a
series of query tables using a series of sp calls.

3. Legally names the region using the name property of the query
table i.e ' WIP'

However, when I check the worksheet manually the named region is not
'WIP' as expected but 'WIP_1'
I need to re-use the same name as charts and tables reference the
data. It is as if Excel will not use my chosen name as it it
believes it to be already in use - thus adding an unwanted underscore
and incremental index.

I appreciate that I could parse and correct the names but I felt I
must be missing something!

Hope this is clearer

Regards

Kieran

 

[Peter T]

I think I see what's happening. You add a new Query named "WIP" to your
sheet, which in turn automatically adds a similarly named range.

You delete both the Query and the named range.

You add the same named Query back again, it gets re-named as WIP_1 and also
defines a similarly named range.

The problem appears more related to the Query name rather than the named
range, which merely gets named to same as the Query. After deleting the
Query it's name is not fully removed, when you add the same named query it
gets renamed with the underscore and extension, and the Named range
similarly.

It seems the only way to reset is to save the workbook (after deleting the
Query tables & names), close it and reopen.

Alternatively just live with it in the knowledge that the Named range will
be the same as that of your Query of the same name.

FWIW object counters of shapes are not decremented when they are deleted,
all must be deleted and do the save/close/reopen to reset.

Regards,
Peter T


To clarify:

My code does the following:

1. Clears all named regions and query tables on a partiular
worksheet.

2. Connects to a remote SQL server and pull back data into a
series of query tables using a series of sp calls.

3. Legally names the region using the name property of the query
table i.e ' WIP'

However, when I check the worksheet manually the named region is not
'WIP' as expected but 'WIP_1'
I need to re-use the same name as charts and tables reference the
data. It is as if Excel will not use my chosen name as it it
believes it to be already in use - thus adding an unwanted underscore
and incremental index.

I appreciate that I could parse and correct the names but I felt I
must be missing something!

Hope this is clearer

Regards

Kieran

 

[Kieran H]

I think I see what's happening. You add a new Query named "WIP" to your
sheet, which in turn automatically adds a similarly named range.

You delete both the Query and the named range.

You add the same named Query back again, it gets re-named as WIP_1 and also
defines a similarly named range.

The problem appears more related to the Query name rather than the named
range, which merely gets named to same as the Query. After deleting the
Query it's name is not fully removed, when you add the same named query it
gets renamed with the underscore and extension, and the Named range
similarly.

It seems the only way to reset is to save the workbook (after deleting the
Query tables & names), close it and reopen.

Alternatively just live with it in the knowledge that the Named range will
be the same as that of your Query of the same name.

FWIW object counters of shapes are not decremented when they are deleted,
all must be deleted and do the save/close/reopen to reset.

Regards,
Peter T


To clarify:

My code does the following:

    1. Clears all named regions and query tables on a partiular
worksheet.

    2. Connects to a remote SQL server and pull back data into a
series of query tables using a series of sp calls.

    3. Legally names the region using the name property of the query
table  i.e  ' WIP'

However, when I check the worksheet manually  the named region is not
'WIP'  as expected but 'WIP_1'
I need to re-use the same name as charts and tables reference the
data.  It is as if  Excel will not use my chosen name as it it
believes it to be already in use - thus adding an unwanted underscore
and incremental index.

I appreciate that I could parse and correct the names but I felt I
must be missing something!

Hope this is clearer

Regards

Kieran




- Show quoted text -

Thanks for your response,
I circumvented the problem in the end by deleting the entire sheet and
re-creating it programatically. This allowed me to maintain the
desired region names.

It was important because the "users" reference these region names in
their worksheets

Cheers

Kieran