Named Range

[Sandy Radjenovic]

How do I import a textfile via VBA & ActiveSheet.QueryTables.Add() without creating a named ranged in my worksheet?

I have code which imports correctly, however, with each import a new named range is created and since this import is done frequently, the # of named ranges from the import file grows quickly.

Any suggestions would be greatly appreciated.

 

[DRJ]

I am not sure about the import, but this will delete all named ranges for you. Assuming that is all you want to do.

For x = 1 To ActiveWorkbook.Names.Count
    ActiveWorkbook.Names(1).Delete
Next x

 

[Sandy Radjenovic]

I wish I could do this instead of finding a way around it. Unfortunately, there are some named ranges that I need to keep.

 

[just_jon]

Well, you could 1st capture an array of existing named ranges, then after doing your import, check the current [presumablt, added-to] list of names ranges, deleting any which have no match to the original.

 

[DRJ]

Try

Dim MyNames as Integer

MyNames = ActiveWorkbook.Names.Count

You code here

For x = MyNames + 1 To ActiveWorkbook.Names.Count
ActiveWorkbook.Names(1).Delete
Next x

 

[Sandy Radjenovic]

You suggest

ActiveWorkbook.Names(1).Delete

should this be? ...

ActiveWorkbook.Names(x).Delete

Now, if I look at the listing of named ranges, it is in alpha order. Does Names(x) refer to creation sequence number rather than alpha order number?

If so, this should work for what I need. Thanks alot.

I just find it hard to believe I cannot do an import without creating a range. I couldn't get beyond this so finding an alternate work around did not even enter my mind.

Thanks for helping me turn on the light bulb.

 

[just_jon]

I would say yes, BUT I do not know if names are stored chronologically or alhpabeticaaly, and do not at present have time to test. However, (1) won't work - typo, probably.

 

[Sandy Radjenovic]

Alright, tested out suggestion and, unfortunately,

ActiveWorkbook.Names(x).Delete

(where x = # of names incremented)

points at last name in alpha order not last name added.

Therefore, wrong name will be deleted.

Onwards ...

 

[DRJ]

Yeah I cut and pasted and forgot to change part

MyNames = ActiveWorkbook.Names.Count

You code here

For x = MyNames + 1 To ActiveWorkbook.Names.Count
ActiveWorkbook.Names(MyNames + 1).Delete
Next x


This will delete all names after the total of the names before you started.

 

[HalfAce]

Yep, That's what I was going to suggest.
ActiveWorkbook.Names(MyNames + 1).Delete
You beat me to it.