B
Ben Rum
I have an excel file with 8 worksheets. Each of these pulls data from a
SQL Server database.
I have created a Macro which refreshes these all individually & then
returns to the first worksheet.
It is possible to make this happen without seeing each worksheet get
selected, refresh, etc, keep the focus on the first sheet and update a
cell with the current action? i.e. "Refreshing Batch Update
workseet..." & so on. And after its completed to display "Refresh
completed at 1-dec-2006 08:15am"
Copy of Macros below
Help appreciated!
Sub RefreshAll()
'
' RefreshAll Macro
' Macro recorded 30-11-2006 by Ben Watt
'
'
Sheets("Summary").Select
Range("A6").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Batch Update").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Datawarehouse ETL").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Disk Space").Select
Range("A42").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Sheets("Scheduled Jobs").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Scheduled Jobs Perf").Select
Range("A42").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("SQL DATA").Select
Range("A42").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
Sheets("SQL LOGS").Select
Range("A42").Select
ActiveSheet.PivotTables("PivotTable6").PivotCache.Refresh
Sheets("Summary").Select
Range("A6").Select
End Sub
SQL Server database.
I have created a Macro which refreshes these all individually & then
returns to the first worksheet.
It is possible to make this happen without seeing each worksheet get
selected, refresh, etc, keep the focus on the first sheet and update a
cell with the current action? i.e. "Refreshing Batch Update
workseet..." & so on. And after its completed to display "Refresh
completed at 1-dec-2006 08:15am"
Copy of Macros below
Help appreciated!
Sub RefreshAll()
'
' RefreshAll Macro
' Macro recorded 30-11-2006 by Ben Watt
'
'
Sheets("Summary").Select
Range("A6").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Batch Update").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Datawarehouse ETL").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Disk Space").Select
Range("A42").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Sheets("Scheduled Jobs").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Scheduled Jobs Perf").Select
Range("A42").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("SQL DATA").Select
Range("A42").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
Sheets("SQL LOGS").Select
Range("A42").Select
ActiveSheet.PivotTables("PivotTable6").PivotCache.Refresh
Sheets("Summary").Select
Range("A6").Select
End Sub