Deleting workbook names w/ VB

[Big-E]

I'm trying to delete a workbook name using the code I got from "Record Macro",
ActiveWorkbook.Names("index_3").Delet

However, when I execute I get Run-time error 1004
Application-defined or object-defined erro

Any suggestions
Thanks
Big-

Background info
I'm importing a text file and that part is peachy. When I go to overwrite the data from the last import another workbook name is assigned to the same range. I'm concerned about generating a near-infinite number of names as each import indexes the name "index_n+1" I'd be happy to reuse the names or just have a way of deleting an ever larger number.

 

[Jake Marx]

Hi Big-E,

That line of code should work, assuming you have a Name named "index_3" in
the active workbook. You'll get a runtime error 1004 if the name does not
exist.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Jake Marx]

Hi Big-E,

What results do you get in the Immediate window when you run the following
code?

Sub test()
Dim n As Name

Debug.Print ActiveWorkbook.Names.Count & _
" names in active workbook."

For Each n In ActiveWorkbook.Names
Debug.Print n.Name & ": " & n.RefersTo
Next n
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

No dice. Have name, have code, still have name...
I've even tried using a variable for the name. Might it have to do
with the name being associated with a QueryTable? (even though I
delete the QT before trying to remove the name)

----- Jake Marx wrote: -----

Hi Big-E,

That line of code should work, assuming you have a Name named
"index_3" in the active workbook. You'll get a runtime error
1004 if the name does not exist.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]

I'm trying to delete a workbook name using the code I got from
"Record Macro",
ActiveWorkbook.Names("index_3").Delete
Application-defined or object-defined error
I'm importing a text file and that part is peachy. When I go

to > overwrite the data from the last import another workbook
name is > assigned to the same range. I'm concerned about
generating a > near-infinite number of names as each import
indexes the name > "index_n+1" I'd be happy to reuse the names
or just have a way of > deleting an ever larger number.

 

[Big-E]

I get nothing from your script (VB msg box or cell contents). However, I'm not certain what the "immediate window" is

----- Jake Marx wrote: ----

Hi Big-E

What results do you get in the Immediate window when you run the followin
code

Sub test(
Dim n As Nam

Debug.Print ActiveWorkbook.Names.Count &
" names in active workbook.

For Each n In ActiveWorkbook.Name
Debug.Print n.Name & ": " & n.RefersT
Next
End Su

--
Regards

Jake Mar
MS MVP - Exce
www.longhead.co

[please keep replies in the newsgroup - email address unmonitored


Big-E wrote

No dice. Have name, have code, still have name..
I've even tried using a variable for the name. Might it have to d
with the name being associated with a QueryTable? (even though
delete the QT before trying to remove the name

----- Jake Marx wrote: ----
Hi Big-E
That line of code should work, assuming you have a Name name

"index_3" in the active workbook. You'll get a runtime erro
1004 if the name does not exist

- Regards
Jake Mar

MS MVP - Exce
www.longhead.co

[please keep replies in the newsgroup - email addres unmonitored

Big-E wrote

I'm trying to delete a workbook name using the code I got fro
"Record Macro"
ActiveWorkbook.Names("index_3").Delet

However, when I execute I get Run-time error 1004

Application-defined or object-defined erro

Any suggestions Thanks
Big-
Background info

I'm importing a text file and that part is peachy. When I g

to > overwrite the data from the last import another workboo
name is > assigned to the same range. I'm concerned abou
generating a > near-infinite number of names as each impor
indexes the name > "index_n+1" I'd be happy to reuse the name
or just have a way of > deleting an ever larger number

 

[Jake Marx]

Hi Big-E,

The results would show up in the Immediate window, which is typically below
the code pane in the VBE. If you don't see it, hit Ctrl+g, and it should
appear.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I get nothing from your script (VB msg box or cell contents).
However, I'm not certain what the "immediate window" is.

----- Jake Marx wrote: -----

Hi Big-E,

What results do you get in the Immediate window when you run the
following code?

Sub test()
Dim n As Name

Debug.Print ActiveWorkbook.Names.Count & _
" names in active workbook."

For Each n In ActiveWorkbook.Names
Debug.Print n.Name & ": " & n.RefersTo
Next n
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]

No dice. Have name, have code, still have name...
I've even tried using a variable for the name. Might it have

to do > with the name being associated with a QueryTable? (even
though I > delete the QT before trying to remove the name)named > "index_3" in the active workbook. You'll get a
runtime error > 1004 if the name does not exist.

-- Regards,
Jake Marx

MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
Big-E wrote:
I'm trying to delete a workbook name using the code I got from
"Record Macro",
ActiveWorkbook.Names("index_3").Delete
However, when I execute I get Run-time error 1004,
Application-defined or object-defined error
Any suggestions?
Thanks,
Big-E
Background info:
I'm importing a text file and that part is peachy. When I go

to > overwrite the data from the last import another

workbook > name is > assigned to the same range. I'm
concerned about > generating a > near-infinite number of
names as each import > indexes the name > "index_n+1" I'd
be happy to reuse the names > or just have a way of >
deleting an ever larger number.

 

[Jake Marx]

Do you have a worksheet named AALPSinterface?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Big-E]

Yep

----- Jake Marx wrote: ----

Do you have a worksheet named AALPSinterface

--
Regards

Jake Mar
MS MVP - Exce
www.longhead.co

[please keep replies in the newsgroup - email address unmonitored


Big-E wrote

Thanks for the help Jake. I found the immediate window, it spits ou
all the references (kinda handy script).
The ref(s) in question is AALPSinterface!input_4
=AALPSinterface!$AA$1:$BF$85 (where _4 increments)

However, I fished this external link code out of the MSDN library an

am trying to modify it to catch these input_* names. Any advice?

'variable declaration

Dim response As Intege
Dim msg As Strin
Dim flag As Boolea
Dim defined_name As Objec

flag = True ' check if external links were foun
' loop through each defined name in workboo

For Each defined_name In ActiveWorkbook.Name

' if a [ was found, then the name has a lin

'If InStr(defined_name.RefersTo, "[") > 0 The
If InStr(defined_name.RefersTo, "[") > 0 The
flag = False ' set flag to False indicating a link was foun

' Message displayed to ask if you want to delete nam

msg = "Do you want to delete the defined name " & "'" &
defined_name.Name & "'" & Chr(13) & " that refers to '
& _ defined_name & "' ?

' delete the defined nam

If MsgBox(msg, 292) = vbYes Then defined_name.Delet
End I
Next defined_name ' get the next defined nam

If flag = True Then ' if flag was not set, display message belo
MsgBox "No defined names with AALPS data found. End I
End Su