Deleting workbook names w/ VB
 

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.

Deleting workbook names w/ VB
 

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]


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.

Deleting workbook names w/ VB
 

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]


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 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]


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.

Deleting workbook names w/ VB
 

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

Deleting workbook names w/ VB
 

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]


Big-E wrote:
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]


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 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]
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.

Deleting workbook names w/ VB
 

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 wrote:
Thanks for the help Jake. I found the immediate window, it spits out
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 and
am trying to modify it to catch these input_* names. Any advice?

'variable declarations
Dim response As Integer
Dim msg As String
Dim flag As Boolean
Dim defined_name As Object

flag = True ' check if external links were found

' loop through each defined name in workbook
For Each defined_name In ActiveWorkbook.Names

' if a [ was found, then the name has a link
'If InStr(defined_name.RefersTo, "[") 0 Then
If InStr(defined_name.RefersTo, "[") 0 Then
flag = False ' set flag to False indicating a link was found

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

' delete the defined name
If MsgBox(msg, 292) = vbYes Then defined_name.Delete
End If
Next defined_name ' get the next defined name

If flag = True Then ' if flag was not set, display message below

MsgBox "No defined names with AALPS data found."
End If

End Sub

Deleting workbook names w/ VB
 

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