Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refreshing external data on individual sheets in a specific sequen
Hi All,
I have a workbook consisting of 10 worksheets. First three worksheets contains Pivot Table, which gets the data from 4th, 5th and 6th worksheets. 4th, 5th and 6th worksheets gets the data (using a web query) from an external CSV file. I want to have a "Refresh Data" on the fist sheet, which is when clicked, refreshes the external data and the pivot tables in the following sequence :- Refresh the data on 4th Sheet. Refresh the data on 5th Sheet. Refresh the data on 6th Sheet. Refresh the Pivot Tables on 1st sheet. Refresh the Pivot Tables on 2nd sheet. Refresh the Pivot Tables on 3rd sheet. I used "ThisWorkbook.RefreshAll", but it refreshes the data in one go, not in the required sequence. I also tried "Sheets(index).Calculate" for different sheets, but it does not refreshes the external data. Any help in writing the macro/subroutine to achieve this functionality would be highly appreciated. Thanks, Nayan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refreshing external data on individual sheets in a specific sequen
Nayan" wrote in message
... Hi All, I have a workbook consisting of 10 worksheets. First three worksheets contains Pivot Table, which gets the data from 4th, 5th and 6th worksheets. 4th, 5th and 6th worksheets gets the data (using a web query) from an external CSV file. I want to have a "Refresh Data" on the fist sheet, which is when clicked, refreshes the external data and the pivot tables in the following sequence :- Refresh the data on 4th Sheet. Refresh the data on 5th Sheet. Refresh the data on 6th Sheet. Refresh the Pivot Tables on 1st sheet. Refresh the Pivot Tables on 2nd sheet. Refresh the Pivot Tables on 3rd sheet. I used "ThisWorkbook.RefreshAll", but it refreshes the data in one go, not in the required sequence. I also tried "Sheets(index).Calculate" for different sheets, but it does not refreshes the external data. Any help in writing the macro/subroutine to achieve this functionality would be highly appreciated. Thanks, Nayan Hi Nayan I wrote a couple of subs a few years ago when i was experiencing a similiar problem. If you call UpdateQueryTablesInWorkBook (to update your web queries) and then UpdatePivotTablesInWorkBook (to update your pivottables) it ought to solve your problem. 'Updates all queries in the Workbook Public Sub UpdateQueryTablesInWorkBook() Dim s As Worksheet Dim q As QueryTable For Each s In ActiveWorkbook.Worksheets For Each q In s.QueryTables q.Refresh Next q Next s End Sub 'Updates all Pivottables in the workbook Public Sub UpdatePivotTablesInWorkBook() Dim s As Worksheet Dim p As PivotTable For Each s In ActiveWorkbook.Worksheets For Each p In s.PivotTables With p.PivotCache .Refresh End With Next p Next s End Sub Regards Marinus Calitz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refreshing External Data | Excel Programming | |||
Refreshing external data | Excel Programming | |||
Refreshing External Data Ranges | Excel Programming | |||
Refreshing external data | Excel Programming | |||
Query to external data not refreshing | Excel Discussion (Misc queries) |