Problem with external_data

[Guest]

Hi
I am jsut using excel 2003 and sql server 2000 and dsn connection. I have an excel template and a command button. If I click on the button, it fills my excel sheet but everytime it fills my excel sheet, I am noticing extenal_data_1 , external_data_2..in the names list, it keeps increasing. I am pasting my code here. Please suggest me if anyone have any ideas to get rid of external_data_3.. or a better way of writing.

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Range("A:C").ClearContents
sqlstring = "select * from test"
connstring = _
"ODBC;DSN=MagCardDev;Description=MagCardDev;APP=Microsoft® Query;DATABASE=MagCardDev;Trusted_Connection=Yes"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("dataarea"), Sql:=sqlstring)
.Refresh
End With
End Sub

Thanks

 

[Frank Stone]

It is the line "With ActiveSheet.QueryTables.Add" that is
this is doing it. each day you add a new query table
one way might be to add this code:

Worksheets("Sheet1").Range("A:C").select
selection.clearcontents
selection.querytable.delete

this way you will delete the old query table before you
create a new one.
But once the query table has been created all you need to
do after that is refresh it. you might be able to replace
all of the code with this:

Worksheets("Sheet1").Range("A:C").select
Selection.querytable.refresh Backgroundquery:= False

Unless you are creating a new and different querytable,
there is no need to recreate the same query each time you
reference the query. all you need to do is refresh it.
also see Tools>get exteranal data> data range properties.
this will help you set the query table properies.

-----Original Message-----
Hi
I am jsut using excel 2003 and sql server 2000 and dsn

connection. I have an excel template and a command button.
If I click on the button, it fills my excel sheet but
everytime it fills my excel sheet, I am noticing
extenal_data_1 , external_data_2..in the names list, it
keeps increasing. I am pasting my code here. Please
suggest me if anyone have any ideas to get rid of
external_data_3.. or a better way of writing.

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Range("A:C").ClearContents
sqlstring = "select * from test"
connstring = _
"ODBC;DSN=MagCardDev;Description=MagCardDev;APP=Microso

ft® Query;DATABASE=MagCardDev;Trusted_Connection=Yes"