Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB, Pivot Table, Refresh
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB, Pivot Table, Refresh
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 -----Original Message----- 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;Trus ted_Conne ction=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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
slow document / pivot table refresh and pivot function | Excel Discussion (Misc queries) | |||
Create refresh button in worksheet to refresh Pivot Table Data | Excel Worksheet Functions | |||
refresh a new worsheet on pivot table refresh | Excel Worksheet Functions | |||
Timing of automatic query refresh and macro pivot table refresh | Excel Programming | |||
Pivot Table REFRESH Flaw -- Saves Old Data in Selection Area AFTER REFRESH | Excel Programming |