I recorded a macro that imports data into a sheet. I am finding that
each time I run this macro, a new named range is added to the list of
named ranges. Does anyone know why this is happening, and how I can
stop this action? This does not create a problem per se; it's just
that the list of names gets longer and longer.

Each new named range is for the same range, since I always import to
the same sheet/range. Each time the macro is run, the name has a
number at the end of the name that is incremented, e.g., receipt_20,
receipt_21, etc.

The recorded VBA looks like this:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;K:\usrfiles\download\receipt.txt",
Destination:=Range("recpts!A2"))
.Name = "receipt"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 3
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(9, 2, 2, 3, 1, 1)
.TextFileFixedColumnWidths = Array(2, 1, 2, 10, 13)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

I tried commenting out the line with .Name in front, but it still
created a new named range.

Thanks,

Carroll Rinehart

Create your querytable once (using the code you show). Then just refresh
it. (activeSheet.querytables(1).Refresh BackgroundQuery:=False)

otherwise, just before the beginning of your code shown do something like
for each nm in Thisworkbook.Names
if instr(1,nm.name,"receipt",vbTextCompare) then
nm.Delete
end if
Next
With ActiveSheet.QueryTables.Add(Connection:= _

--
Regards,
Tom Ogilvy


"Carroll Rinehart" wrote in message
om...
I recorded a macro that imports data into a sheet. I am finding that
each time I run this macro, a new named range is added to the list of
named ranges. Does anyone know why this is happening, and how I can
stop this action? This does not create a problem per se; it's just
that the list of names gets longer and longer.

Each new named range is for the same range, since I always import to
the same sheet/range. Each time the macro is run, the name has a
number at the end of the name that is incremented, e.g., receipt_20,
receipt_21, etc.

The recorded VBA looks like this:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;K:\usrfiles\download\receipt.txt",
Destination:=Range("recpts!A2"))
.Name = "receipt"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 3
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(9, 2, 2, 3, 1, 1)
.TextFileFixedColumnWidths = Array(2, 1, 2, 10, 13)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

I tried commenting out the line with .Name in front, but it still
created a new named range.

Thanks,

Carroll Rinehart

Tom,

I used the "nm.Delete: approach and it worked just fine. What I found
interesting is that somehow Excel knows what incremented number you're
on for the range name. So instead of having multiple named ranges, I
have just one, but its name is something like "receipt_62". I would
have thought that it would have started over with "receipt_1", but
that's really not important.

Thanks so much.

Carroll Rinehart

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!