I'm returning data from a SQL stored procedure that has 2 inputs Date1 and
Date2 using the following VB code.

With ActiveSheet.QueryTables.Add(ConnectionString, Range("A1"))
.CommandText = "EXEC sp_date_range_to_oee_values Date1, Date2
.Name = "Query from Plater_SQL"
.FieldNames = True
.PreserveFormatting = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

It works ok but when I run it a second or third time it creates another data
set and concatenates it to the right of the previous extraction. I'm
selecting the destination cells and deleting the contents each time before
running the extraction to prevent the concantenation.

It's not very elegant and I'd like to make it better. Any thoughts.

Jefff

Did the data get returned to the locations you wanted? Sometimes the data is
organized very well on returned queries. Do you want different formating, or
just add header rows and header columns. Maybe add A title to the table or
add formating like borders around the cells or use different fonts. Not sure
what you need.

"Jeff" wrote:

I'm returning data from a SQL stored procedure that has 2 inputs Date1 and
Date2 using the following VB code.

With ActiveSheet.QueryTables.Add(ConnectionString, Range("A1"))
.CommandText = "EXEC sp_date_range_to_oee_values Date1, Date2
.Name = "Query from Plater_SQL"
.FieldNames = True
.PreserveFormatting = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

It works ok but when I run it a second or third time it creates another data
set and concatenates it to the right of the previous extraction. I'm
selecting the destination cells and deleting the contents each time before
running the extraction to prevent the concantenation.

It's not very elegant and I'd like to make it better. Any thoughts.

Jefff

If you have just one querytable on the worksheet, this will delete it:

On Error Resume Next
ActiveSheet.QueryTables(1).Delete
On Error Goto 0
' Your macro starts here

Then you will start with a clean slate every time.

But, you do not have to delete it. After the query table is created -- a
one-time step, manually or programmatically -- refresh the query table,
e.g.:

With wks.QueryTables(1)
.CommandText = "EXEC sp_date_range_to_oee_values Date1, Date2
.Connection = ConnectionString
' Any other properties/methods you might want...
.Refresh ' False
End With

Also, look into the RefreshStyle property. In the VBE's help type in
RefreshStyle. Here is a snippet in regard to the XlCellInsertionMode
emumeration which may assist your situation:

Returns or sets the way rows on the specified worksheet are added or deleted
to accommodate the number of rows in a recordset returned by a query.
Read/write XlCellInsertionMode.
XlCellInsertionMode can be one of these XlCellInsertionMode constants.
xlInsertDeleteCells. Partial rows are inserted or deleted to match the
exact number of rows required for the new recordset.
xlOverwriteCells. No new cells or rows are added to the worksheet.
Data in surrounding cells is overwritten to accommodate any overflow.
xlInsertEntireRows. Entire rows are inserted, if necessary, to
accommodate any overflow. No cells or rows are deleted from the worksheet.

So for example you might modify the refresh macro to:

With wks.QueryTables(1)
.CommandText = ...
.Connection = ...
.RefreshStyle = xlOverwriteCells
' Any other methods you want to use...
.Refresh ' False
End With

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"Jeff" wrote in message
...
I'm returning data from a SQL stored procedure that has 2 inputs Date1 and
Date2 using the following VB code.

With ActiveSheet.QueryTables.Add(ConnectionString, Range("A1"))
.CommandText = "EXEC sp_date_range_to_oee_values Date1, Date2
.Name = "Query from Plater_SQL"
.FieldNames = True
.PreserveFormatting = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

It works ok but when I run it a second or third time it creates another
data
set and concatenates it to the right of the previous extraction. I'm
selecting the destination cells and deleting the contents each time before
running the extraction to prevent the concantenation.

It's not very elegant and I'd like to make it better. Any thoughts.

Jefff

Thank you.

I can't check it now but I'm almost positive you've discovered what I need
to change. Thanks...



"Tim Zych" wrote:

If you have just one querytable on the worksheet, this will delete it:

On Error Resume Next
ActiveSheet.QueryTables(1).Delete
On Error Goto 0
' Your macro starts here

Then you will start with a clean slate every time.

But, you do not have to delete it. After the query table is created -- a
one-time step, manually or programmatically -- refresh the query table,
e.g.:

With wks.QueryTables(1)
.CommandText = "EXEC sp_date_range_to_oee_values Date1, Date2
.Connection = ConnectionString
' Any other properties/methods you might want...
.Refresh ' False
End With

Also, look into the RefreshStyle property. In the VBE's help type in
RefreshStyle. Here is a snippet in regard to the XlCellInsertionMode
emumeration which may assist your situation:

Returns or sets the way rows on the specified worksheet are added or deleted
to accommodate the number of rows in a recordset returned by a query.
Read/write XlCellInsertionMode.
XlCellInsertionMode can be one of these XlCellInsertionMode constants.
xlInsertDeleteCells. Partial rows are inserted or deleted to match the
exact number of rows required for the new recordset.
xlOverwriteCells. No new cells or rows are added to the worksheet.
Data in surrounding cells is overwritten to accommodate any overflow.
xlInsertEntireRows. Entire rows are inserted, if necessary, to
accommodate any overflow. No cells or rows are deleted from the worksheet.

So for example you might modify the refresh macro to:

With wks.QueryTables(1)
.CommandText = ...
.Connection = ...
.RefreshStyle = xlOverwriteCells
' Any other methods you want to use...
.Refresh ' False
End With

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"Jeff" wrote in message
...
I'm returning data from a SQL stored procedure that has 2 inputs Date1 and
Date2 using the following VB code.

With ActiveSheet.QueryTables.Add(ConnectionString, Range("A1"))
.CommandText = "EXEC sp_date_range_to_oee_values Date1, Date2
.Name = "Query from Plater_SQL"
.FieldNames = True
.PreserveFormatting = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

It works ok but when I run it a second or third time it creates another
data
set and concatenates it to the right of the previous extraction. I'm
selecting the destination cells and deleting the contents each time before
running the extraction to prevent the concantenation.

It's not very elegant and I'd like to make it better. Any thoughts.

Jefff