Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
 Posted - 2006-09-12 : 10:42:50
|
| Hi i have a access form that has a button on it that opens up a excel file that in turn has pivot tables on it. So far i have the first pivot table in the excel sheet refreshing but i can't get the rest.Am getting the error of "Unable to get Pivot Table property of of worksheet class."Here is my code. Private Sub UK_COst_Report_Click() On Error Goto Err_UK_Cost_Report_Click Dim oApp As Object Dim wrkFile As Object 'Workbooks Set oApp = CreateObject("Excel.Application") Set wrkFile = oApp.Workbooks ' Open a file. wrkFile.Open "B:\Report.xls" oApp.Visible = True 'Only XL 97 supports UserControl Property ' to refresh pivot table on opening oApp.PivotTableSelection = acOLEActivate oApp.ActiveSheet.PivotTables("PivotTable18").RefreshTable 'oApp.ActiveSheet.PivotTables("PivotTable3").RefreshTable MsgBox "You've closed the Report," & Chr$(13) & _ "and return to the main screen." ' Close the file. wrkFile.Close ' Quit Excel. oApp.Quit ' Close the object references. Set wrkFile = Nothing Set appXl = Nothing On Error Resume Next oApp.UserControl = True Exit_UK_Cost_Report_Click: Exit Sub Err_UK_Cost_Report_Click: MsgBox Err.Description Resume Exit_UK_Cost_Report_Click End Sub |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-12 : 14:23:05
|
| This will do ithttp://www.ozgrid.com/VBA/pivot-table-refresh.htm |
 |
|
|
|
|
|
Refresh pivot table from access