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.

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.

This is the code that I used,

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

ActiveWorkbook.Names("MS_Project_paste_List").Delete
ActiveWorkbook.Save
ActiveWorkbook.Names("MS_Project_paste_List").Delete



With Worksheets("All").QueryTables.Add(Connection:=rstRecordset,
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.

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").Delete
> ActiveWorkbook.Save
> ActiveWorkbook.Names("MS_Project_paste_List").Delete
>
>
>
> With Worksheets("All").QueryTables.Add(Connection:=rstRecordset,
> 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.

Ron,

By deleting the range name, it stopped generating the _1 number for the
range. There were about ranges created by several test runs that I did.

Thank you very much for the help.

Sajit Viswan
Abu Dhabi

"Ron Coderre" wrote:

> 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").Delete
> > ActiveWorkbook.Save
> > ActiveWorkbook.Names("MS_Project_paste_List").Delete
> >
> >
> >
> > With Worksheets("All").QueryTables.Add(Connection:=rstRecordset,
> > 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.

I am having the same issue... _1 _2 or no _# appending to the named range
of my QueryTable.

I am deleting and re-creating the Names and QTs each time but still have the
issue. When a RngName_1 was autocreated by the query .add, the it is the only
range name attached to the sheet. This makes no sense why the query.add
would choose to increment the _#

Here is what I am using to delete...
Dim WS As Worksheet
Dim QT As QueryTable
For Each WS In Worksheets
For Each QT In WS.QueryTables
QT.Delete
Next
Next
Dim N As name
For Each N In ActiveWorkbook.Names
N.Delete
Next

My VBA code is set to run each time the book is opened and the option to
re-FTP data was selected. Does the .delete need time to complete? adding a
donothing counter loop to fix?

Open1... Works with no appended _# so my dynamically created formula using
the named range work fine.

Open2... ERROR _1 has been appended.

Open3... Works with nothing appended.

Open4... ERROR _1 again

Each time the only named ranges stored in the WB are the 2 matching the
query tables I have created except the _# being appended or not.

any suggestions?

does closing the workbook do something to complete the named deletes?

Grabbing for straws here!

TIA
--
Regards,
John

John

Just to reiterate from my prior post:

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.

There may be a different/better way to achieve the same results, but I was
thankful to find even one method that worked. Really....one Excel starts
autonumbering the data range name, it tenaciously continues to.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"John Keith" wrote:

> I am having the same issue... _1 _2 or no _# appending to the named range
> of my QueryTable.
>
> I am deleting and re-creating the Names and QTs each time but still have the
> issue. When a RngName_1 was autocreated by the query .add, the it is the only
> range name attached to the sheet. This makes no sense why the query.add
> would choose to increment the _#
>
> Here is what I am using to delete...
> Dim WS As Worksheet
> Dim QT As QueryTable
> For Each WS In Worksheets
> For Each QT In WS.QueryTables
> QT.Delete
> Next
> Next
> Dim N As name
> For Each N In ActiveWorkbook.Names
> N.Delete
> Next
>
> My VBA code is set to run each time the book is opened and the option to
> re-FTP data was selected. Does the .delete need time to complete? adding a
> donothing counter loop to fix?
>
> Open1... Works with no appended _# so my dynamically created formula using
> the named range work fine.
>
> Open2... ERROR _1 has been appended.
>
> Open3... Works with nothing appended.
>
> Open4... ERROR _1 again
>
> Each time the only named ranges stored in the WB are the 2 matching the
> query tables I have created except the _# being appended or not.
>
> any suggestions?
>
> does closing the workbook do something to complete the named deletes?
>
> Grabbing for straws here!
>
> TIA
> --
> Regards,
> John
>

Yes, the deleting the previous query table did the thing.

Thanks for the help,

I thought I had replied this mail.

Sajit

"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.