Named Range

Sandy Radjenovic

Board Regular
Joined
Nov 4, 2003
Messages
76
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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
I am not sure about the import, but this will delete all named ranges for you. Assuming that is all you want to do.

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

Sandy Radjenovic

Board Regular
Joined
Nov 4, 2003
Messages
76
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

Legend
Joined
Sep 3, 2002
Messages
10,473
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853

ADVERTISEMENT

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

Board Regular
Joined
Nov 4, 2003
Messages
76
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

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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

Board Regular
Joined
Nov 4, 2003
Messages
76
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Yep, That's what I was going to suggest.
ActiveWorkbook.Names(MyNames + 1).Delete
You beat me to it.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,840
Messages
5,598,386
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top