VB, Pivot Table, Refresh

[Guest]

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;Trusted_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

 

[Mike215]

Hi,

I'd suggest that you name the range that serves as the
pivot table source. Then insert a line at the beginning
of the code that clears that range. From that point, bring
in your new data starting in A1 as always. At the end of
your code, add a line that resets the named range to
include all the new data. At that point the pivot refresh
should work.

sub datacollect()
Sheets("ClientKPIComparrison").Select
range("DATARANGE").clear
your code
range("a1").select
activecell.currentregion.name = "DATARANGE"
end sub

mike