Let me start by saying that, in my experience, query tables are a bit finicky.
It seems that once they start automatically appending numbers to the query
names there's no way to stop it.

Here's what works for me:
I create a valid query table in Excel (using MS Query) and always append
"_1" to the data range name.
I SAVE the workbook.
Then, I run my code. If something goes wrong and Excel starts incrementing
the data range name....I close the workbook, open the saved copy, amend the
code, and save the workbook again. I repeat the process until I get the
desired results. After that, I don't have to worry about incrementing names.

Note: You need do delete the range name AND the querytable...not just the
range name.

Try downloading the QueryMaster file at Debra Dalgleish's website:
http://www.contextures.com/excelfiles.html#External

Study the VBA code in that model and see if any of those techniques help
resolve your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Sajit" wrote:

This is the code that I used,

Application.Goto Reference:="MS_Project_paste_List"
Selection.ClearContents

ActiveWorkbook.Names("MS_Project_paste_List").Dele te
ActiveWorkbook.Save
ActiveWorkbook.Names("MS_Project_paste_List").Dele te



With Worksheets("All").QueryTables.Add(Connection:=rstR ecordset,
Destination:=Range("A1"))

.Name = "MS_Project_paste_List"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False

End With
Even after deleting and saving it still creates additional instances with
the _1. However the same if done manually it does not do.

"Ron Coderre" wrote:

Try something like this:

In my code, I pass these arguments:

strDestWkshtName: the sheetname containing the querytable
strQryTableName: the name of the querytable to impact

Note: To avoid the problem you are facing, I ALWAYS append "_1" to the
querytable name.
Example: NetBkgsQryResults_from_Oracle_1

'---------start of code sample--------
With ThisWorkbook.Worksheets(strDestWkshtName)
If .QueryTables.Count < 0 Then

For Each qtbQTbl In .QueryTables

'Find the target querytable
If qtbQTbl.Name = strQryTableName Then

'erase the previous query results
On Error Resume Next
.Range(strQryTableName).ClearContents

'delete the querytable
On Error Resume Next
qtbQTbl.Delete

'delete the querytable range name
On Error Resume Next
.Names(strQryTableName).Delete

On Error GoTo 0
End If
Next qtbQTbl
End If
End With
'---------end of code sample--------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Sajit" wrote:

I have to create a range name during an import from an SQL server data base.
During the next time the data import is done. The existing name range has to
be deleted and the same range name is given for the new data imported. This,
if done manually, excel does it correct. But if done through code, then
instead of creating the same name it adds the suffix _1 to the name. Possibly
indicating that the previous name has not been deleted. I have also tried
putting in a save statement after the delete statement. But it still does the
same.