When I get external data using the code below, a new range name is created
each time I bring in the data. The name refers to the external data file and
just keeps incrementing the range name by 1 so the range names aren't
duplicates. Am I doing something wrong when I get the external data? Even
if I comment out the .Name line I still get ExternalData_1, ExternalData_2,
etc. This problem's been nagging at me for quite some time. Help??? I
appreciate it!

Range("B3:C50").Select
Selection.ClearContents
Range("B3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;S:\common\Excel Rewrites\Store Table BigM.txt",
Destination:=Range( _
"B3"))
' .Name = "Store Table BigM"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.Refresh BackgroundQuery:=False
End With

Can't you just refresh your existing table.

--
Regards,
Tom Ogilvy

"cottage6" wrote in message
...
When I get external data using the code below, a new range name is created
each time I bring in the data. The name refers to the external data file
and
just keeps incrementing the range name by 1 so the range names aren't
duplicates. Am I doing something wrong when I get the external data?
Even
if I comment out the .Name line I still get ExternalData_1,
ExternalData_2,
etc. This problem's been nagging at me for quite some time. Help??? I
appreciate it!

Range("B3:C50").Select
Selection.ClearContents
Range("B3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;S:\common\Excel Rewrites\Store Table BigM.txt",
Destination:=Range( _
"B3"))
' .Name = "Store Table BigM"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.Refresh BackgroundQuery:=False
End With

Yes Tom, actually I could do that. Thanks. Sometimes I get carried away
trying to make things easier for users, but there's no reason they can't
refresh these tables.

"Tom Ogilvy" wrote:

Can't you just refresh your existing table.

--
Regards,
Tom Ogilvy

"cottage6" wrote in message
...
When I get external data using the code below, a new range name is created
each time I bring in the data. The name refers to the external data file
and
just keeps incrementing the range name by 1 so the range names aren't
duplicates. Am I doing something wrong when I get the external data?
Even
if I comment out the .Name line I still get ExternalData_1,
ExternalData_2,
etc. This problem's been nagging at me for quite some time. Help??? I
appreciate it!

Range("B3:C50").Select
Selection.ClearContents
Range("B3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;S:\common\Excel Rewrites\Store Table BigM.txt",
Destination:=Range( _
"B3"))
' .Name = "Store Table BigM"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.Refresh BackgroundQuery:=False
End With

If you want code:

With ActiveSheet.QueryTables(1)
.Refresh BackgroundQuery:=False
End With

--
Regards,
Tom Ogilvy

"cottage6" wrote in message
...
Yes Tom, actually I could do that. Thanks. Sometimes I get carried away
trying to make things easier for users, but there's no reason they can't
refresh these tables.

"Tom Ogilvy" wrote:

Can't you just refresh your existing table.

--
Regards,
Tom Ogilvy

"cottage6" wrote in message
...
When I get external data using the code below, a new range name is
created
each time I bring in the data. The name refers to the external data
file
and
just keeps incrementing the range name by 1 so the range names aren't
duplicates. Am I doing something wrong when I get the external data?
Even
if I comment out the .Name line I still get ExternalData_1,
ExternalData_2,
etc. This problem's been nagging at me for quite some time. Help???
I
appreciate it!

Range("B3:C50").Select
Selection.ClearContents
Range("B3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;S:\common\Excel Rewrites\Store Table BigM.txt",
Destination:=Range( _
"B3"))
' .Name = "Store Table BigM"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.Refresh BackgroundQuery:=False
End With