New Named Range Created Each Time Data Imported into Excel via Macro
 

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

New Named Range Created Each Time Data Imported into Excel via Macro
 

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

New Named Range Created Each Time Data Imported into Excel via Macro
 

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!