Hi,

I am currently using a selection of VB script to run a stored procedure and
place this into a Sheet in my Excel Book, the problem is, when I run it the
first time, the data goes in fine starting as cell A1, if I run it again, it
moves the data that was on that sheet across the page and then puts the newly
collected data in starting at cell A1 i.e. I end up with 2 lots of data on
the page, what I want is on each refresh just put the newly collected data at
cell A1, hope that makes sense. This also causes me a problem with a Pivot
table using this data, as the Range gets all messed up and the Pivot table
wont refresh, does anyone have any ideas how to fix this.

My VB Code is :-

Sub DataCollect()

Sheets("ClientKPIComparrison").Select
Rows("1:1000").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=XXX;UID=XXX;pword=XXXAPP=Microsoft®
Query;WSID=XXX;DATABASE=XXX;Network=DBMSSOCN;Trust ed_Connection=Yes" _
, Destination:=Range("A1"))
.CommandText = Array("STORED_PROCEDURE " &
Worksheets("Sheet1").Range("B1") & ", " & _
Worksheets("Sheet1").Range("B2") & ", " &
Worksheets("Sheet1").Range("B3") & ", " & _
Worksheets("Sheet1").Range("B4") & ", " &
Worksheets("Sheet1").Range("B5") & ", " & _
Worksheets("Sheet1").Range("B6") & ", " &
Worksheets("Sheet1").Range("B7") & ", " & _
Worksheets("Sheet1").Range("B8") & ", " &
Worksheets("Sheet1").Range("B9") & ", " & _
Worksheets("Sheet1").Range("B10") & ", " &
Worksheets("Sheet1").Range("B11") & ", " & _
Worksheets("Sheet1").Range("B13") & ", " &
Worksheets("Sheet1").Range("B14") & ", " & _
Worksheets("Sheet1").Range("B15") & ", " &
Worksheets("Sheet1").Range("B16") & ", " & _
Worksheets("Sheet1").Range("B17") & ", " &
Worksheets("Sheet1").Range("B18") & ", " & _
Worksheets("Sheet1").Range("B19") & ", " &
Worksheets("Sheet1").Range("B20") & ", " & "'" & _
Worksheets("Sheet1").Range("B22") & "'")
Debug.Print .CommandText
.Name = "Sheet1"
.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:=True
End With
Sheets("ClientKPIComparrison").Select
End Sub