Karen Schaefer
asked on
Refresh Excel Pivot table from Access
I need to open an Excel Pivot table from Access and refresh on open via VBA code.
Here is my current code:
Function OpenExcelPivot()
On Error GoTo OpenExcelPivot_Err
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Applic ation")
objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intran et Information Server\CorePlan\SpreadShee ts\" & gFileName & ".xls")
objExcel.Visible = True
objExcel.DisplayAlerts = False
'objExcel.PivotTable.Verb = acOLEVerbOpen
objExcel.PivotTableSelecti on = acOLEActivate
'PivotTable.Action = acOLEActivate
objExcel.ActiveSheet.Pivot Tables("Pi votTable1" ).RefreshT able
Set objExcel = Nothing
OpenExcelPivot_Exit:
Exit Function
OpenExcelPivot_Err:
MsgBox Error$
Resume OpenExcelPivot_Exit
End Function
It doesn't like " objExcel.ActiveSheet.Pivot Tables("Pi votTable1" ).RefreshT able" portion of the code
Unable to get Pivot Table property of of worksheet class.
Thanks.
Karen
Here is my current code:
Function OpenExcelPivot()
On Error GoTo OpenExcelPivot_Err
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Applic
objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intran
objExcel.Visible = True
objExcel.DisplayAlerts = False
'objExcel.PivotTable.Verb = acOLEVerbOpen
objExcel.PivotTableSelecti
'PivotTable.Action = acOLEActivate
objExcel.ActiveSheet.Pivot
Set objExcel = Nothing
OpenExcelPivot_Exit:
Exit Function
OpenExcelPivot_Err:
MsgBox Error$
Resume OpenExcelPivot_Exit
End Function
It doesn't like " objExcel.ActiveSheet.Pivot
Unable to get Pivot Table property of of worksheet class.
Thanks.
Karen
Is that error at compile time or runtime?
ASKER
runtime
Odd.
I wonder if it's some issue with the ActiveSheet object.
What about
Dim objExcel As Excel.Application
Dim objSheet As Excel.Worksheet
Set objExcel = CreateObject("Excel.Applic ation")
objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intran et Information Server\CorePlan\SpreadShee ts\" & gFileName & ".xls")
objExcel.Visible = True
objExcel.DisplayAlerts = False
'objExcel.PivotTable.Verb = acOLEVerbOpen
objExcel.PivotTableSelecti on = acOLEActivate
'PivotTable.Action = acOLEActivate
Set objSheet = objExcel.ActiveSheet
Msgbox "About to refresh on " & objSheet.Name
Msgbox "About to refresh on " & objSheet.PivotTables("Pivo tTable1"). Name
objSheet.PivotTables("Pivo tTable1"). RefreshTab le
See if that changes anything - and if not which line it errors on.
(Naturally the message boxes are to be removed in time).
I wonder if it's some issue with the ActiveSheet object.
What about
Dim objExcel As Excel.Application
Dim objSheet As Excel.Worksheet
Set objExcel = CreateObject("Excel.Applic
objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intran
objExcel.Visible = True
objExcel.DisplayAlerts = False
'objExcel.PivotTable.Verb = acOLEVerbOpen
objExcel.PivotTableSelecti
'PivotTable.Action = acOLEActivate
Set objSheet = objExcel.ActiveSheet
Msgbox "About to refresh on " & objSheet.Name
Msgbox "About to refresh on " & objSheet.PivotTables("Pivo
objSheet.PivotTables("Pivo
See if that changes anything - and if not which line it errors on.
(Naturally the message boxes are to be removed in time).
ASKER
Is it possible to pass the actual name from the Excel Pivot table on open of the pivot table.
My workbook can contain multiple wksheets and pivot tables on the same wksht.
the first msgbox returns the value of 3G - name of the active wksheet (1 of 4 within the wkbook)
the 2nd msgbox errors on the PivotTable name - in this case mind is Pivottable2 not 1.
So there must be a way to get the active name and relay it back to the code?
K
My workbook can contain multiple wksheets and pivot tables on the same wksht.
the first msgbox returns the value of 3G - name of the active wksheet (1 of 4 within the wkbook)
the 2nd msgbox errors on the PivotTable name - in this case mind is Pivottable2 not 1.
So there must be a way to get the active name and relay it back to the code?
K
How many Pivot tables are there in your worksheet in question?
ASKER
That can very because I have several xls that can contian various numbers of wksheets. So I will have to allow for this variance some how.
K
K
ASKER
thought I would try a variable by my code doesn't like it.
Function OpenExcelPivot()
On Error GoTo OpenExcelPivot_Err
Dim objExcel As Excel.Application
Dim objSheet As Excel.workSheet
Dim nPName As String
Set objExcel = CreateObject("Excel.Applic ation")
objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intran et Information Server\CorePlan\SpreadShee ts\" & gFileName & ".xls")
objExcel.Visible = True
objExcel.DisplayAlerts = False
'objExcel.PivotTable.Verb = acOLEVerbOpen
objExcel.PivotTableSelecti on = acOLEActivate
'PivotTable.Action = acOLEActivate
Set objSheet = objExcel.ActiveSheet
nPName = objSheet.Name
MsgBox "About to refresh on " & objSheet.Name
MsgBox "About to refresh on " & objSheet.PivotTables(nPNam e).Name
objSheet.PivotTables(nPNam e).Refresh Table
OpenExcelPivot_Exit:
Exit Function
OpenExcelPivot_Err:
MsgBox Error$
Resume OpenExcelPivot_Exit
End Function
I am going for the night - thanks for your assistance - I will pick this up in the am.
K
Function OpenExcelPivot()
On Error GoTo OpenExcelPivot_Err
Dim objExcel As Excel.Application
Dim objSheet As Excel.workSheet
Dim nPName As String
Set objExcel = CreateObject("Excel.Applic
objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intran
objExcel.Visible = True
objExcel.DisplayAlerts = False
'objExcel.PivotTable.Verb = acOLEVerbOpen
objExcel.PivotTableSelecti
'PivotTable.Action = acOLEActivate
Set objSheet = objExcel.ActiveSheet
nPName = objSheet.Name
MsgBox "About to refresh on " & objSheet.Name
MsgBox "About to refresh on " & objSheet.PivotTables(nPNam
objSheet.PivotTables(nPNam
OpenExcelPivot_Exit:
Exit Function
OpenExcelPivot_Err:
MsgBox Error$
Resume OpenExcelPivot_Exit
End Function
I am going for the night - thanks for your assistance - I will pick this up in the am.
K
The name of the pivot table is definately Pivottable2?
What do you see listed using
Dim objExcel As Excel.Application
Dim objSheet As Excel.Worksheet
Dim objPivot As Excel.Worksheet
Dim strList as String
Set objExcel = CreateObject("Excel.Applic ation")
objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intran et Information Server\CorePlan\SpreadShee ts\" & gFileName & ".xls")
objExcel.Visible = True
objExcel.DisplayAlerts = False
'objExcel.PivotTable.Verb = acOLEVerbOpen
objExcel.PivotTableSelecti on = acOLEActivate
'PivotTable.Action = acOLEActivate
Set objSheet = objExcel.ActiveSheet
For Each objPivot In objSheet.PivotTables
strList = strList & objPivot.Name & vbcrlf
Next
Msgbox strList
What do you see listed using
Dim objExcel As Excel.Application
Dim objSheet As Excel.Worksheet
Dim objPivot As Excel.Worksheet
Dim strList as String
Set objExcel = CreateObject("Excel.Applic
objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intran
objExcel.Visible = True
objExcel.DisplayAlerts = False
'objExcel.PivotTable.Verb = acOLEVerbOpen
objExcel.PivotTableSelecti
'PivotTable.Action = acOLEActivate
Set objSheet = objExcel.ActiveSheet
For Each objPivot In objSheet.PivotTables
strList = strList & objPivot.Name & vbcrlf
Next
Msgbox strList
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'd wondered about number - hence the question about how many there are per sheet :-)
The last loop was just to see for certain exactly what Pivot Tables there are in there.
Perhaps to then iterate through the tables - and compare for name - refreshing the appropriate one when found ;-)
The last loop was just to see for certain exactly what Pivot Tables there are in there.
Perhaps to then iterate through the tables - and compare for name - refreshing the appropriate one when found ;-)
Ah, yes. That would avoid the error, indeed. I didn't mean to stop you in the direction you were taking, mind you, I was just offering another approach to the entire problem...
Cheers!
(°v°)
Cheers!
(°v°)
Noo - not stopped me.. lol
Why to stop me it would take a sunny day - a fridge of beer and...
Why to stop me it would take a sunny day - a fridge of beer and...
ASKER
harfang,
where do you handle the pstrWorkbook variable?
K
where do you handle the pstrWorkbook variable?
K
ASKER
I am getting Automation error invalid syntax?
K
K
kfschaefer1
pstrWorkbook is the name of the workbook you wish to open and refresh. For example, call the sub above like this:
OpenXL_Pivot "\\Wabelhdk0215892\Intrane t Information Server\CorePlan\SpreadShee ts\" & gFileName & ".xls"
It should work as it is. It was not necessarily meant to be incorporated in your existing Sub...
To find where the error occurs, try from VB "tools / options...", [General] tab, Error trapping: (•) Break on all errors. This should highlight the line if you choose [Debug]. Remember to turn this back off when done debugging, naturally.
(°v°)
pstrWorkbook is the name of the workbook you wish to open and refresh. For example, call the sub above like this:
OpenXL_Pivot "\\Wabelhdk0215892\Intrane
It should work as it is. It was not necessarily meant to be incorporated in your existing Sub...
To find where the error occurs, try from VB "tools / options...", [General] tab, Error trapping: (•) Break on all errors. This should highlight the line if you choose [Debug]. Remember to turn this back off when done debugging, naturally.
(°v°)
ASKER
What is the syntax to declare the path as a global declaration?
k
k
Please explain what you mean or want to acheive. A global *variable* is a variable outside of a sub or function, so it a global *constant*. What is a global path?
(°v°)
(°v°)
You want to declare the folder path as a global constant?
ASKER
Nevermind
I need to incorporate both samples of the open pivot code;
I now need to run this code with my nightly run - so I need to refresh the pivot tables. I have created a table that will contain the variable name of the spreadsheet _ I want to refresh all pivot tabels within each of the half dozen spreadsheets.
I am having a little difficulty getting the combined code to work.
Function OpenXL_Pivot(pstrWorkbook As String)
' Dim xlWorkbook As Excel.workBook
Dim xlPivotCache As Excel.PivotCache
On Error GoTo Error_Label
' get (or open) workbook
Set xlWorkbook = GetObject(pstrWorkbook)
' refresh all pivot tables
Dim objExcel As Excel.Application
Dim objSheet As Excel.Worksheet
Dim objPivot As Excel.Worksheet
Dim strList As String
Set objExcel = CreateObject("Excel.Applic ation")
'objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intran et Information Server\CorePlan\SpreadShee ts\" & pstrWorkbook & ".xls")
objExcel.Workbooks.Open (pstrWorkbook)
objExcel.Visible = True
objExcel.DisplayAlerts = False
'objExcel.PivotTable.Verb = acOLEVerbOpen
objExcel.PivotTableSelecti on = acOLEActivate
'PivotTable.Action = acOLEActivate
Set objSheet = objExcel.ActiveSheet
For Each xlPivotCache In xlWorkbook.PivotCaches --------- If I use this one it dies on the xlworkbook object
xlPivotCache.Refresh
Next xlPivotCache
For Each objPivot In objSheet.PivotTables - this one only returns a message box and not actually refreshing the pivot tables.
strList = strList & objPivot.Name & vbCrLf
Next
MsgBox strList
Exit_Label:
On Error Resume Next
' make sure everything is visible...
' xlWorkbook.Activate
' xlWorkbook.Windows(1).Visi ble = False
' xlWorkbook.Application.Vis ible = False
Exit Function
Error_Label:
MsgBox Err.Description
Resume Exit_Label
End Function
Thanks for your assistance.
Going home for the weekend - HAVE A GREAT WEEKEND.
kAREN
I need to incorporate both samples of the open pivot code;
I now need to run this code with my nightly run - so I need to refresh the pivot tables. I have created a table that will contain the variable name of the spreadsheet _ I want to refresh all pivot tabels within each of the half dozen spreadsheets.
I am having a little difficulty getting the combined code to work.
Function OpenXL_Pivot(pstrWorkbook As String)
' Dim xlWorkbook As Excel.workBook
Dim xlPivotCache As Excel.PivotCache
On Error GoTo Error_Label
' get (or open) workbook
Set xlWorkbook = GetObject(pstrWorkbook)
' refresh all pivot tables
Dim objExcel As Excel.Application
Dim objSheet As Excel.Worksheet
Dim objPivot As Excel.Worksheet
Dim strList As String
Set objExcel = CreateObject("Excel.Applic
'objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intran
objExcel.Workbooks.Open (pstrWorkbook)
objExcel.Visible = True
objExcel.DisplayAlerts = False
'objExcel.PivotTable.Verb = acOLEVerbOpen
objExcel.PivotTableSelecti
'PivotTable.Action = acOLEActivate
Set objSheet = objExcel.ActiveSheet
For Each xlPivotCache In xlWorkbook.PivotCaches --------- If I use this one it dies on the xlworkbook object
xlPivotCache.Refresh
Next xlPivotCache
For Each objPivot In objSheet.PivotTables - this one only returns a message box and not actually refreshing the pivot tables.
strList = strList & objPivot.Name & vbCrLf
Next
MsgBox strList
Exit_Label:
On Error Resume Next
' make sure everything is visible...
' xlWorkbook.Activate
' xlWorkbook.Windows(1).Visi
' xlWorkbook.Application.Vis
Exit Function
Error_Label:
MsgBox Err.Description
Resume Exit_Label
End Function
Thanks for your assistance.
Going home for the weekend - HAVE A GREAT WEEKEND.
kAREN
You've not set the xlWorkbook object in that example.
(Could be something like Set xlWorkbook = objExcel.ActiveWorkbook)
Is it a whole book you're wanting to loop through? Or just the pivot tables in a given sheet?
As I said - mine wasn't intended to fix anything - the messagebox was to tell you the names of the pivot tables on that worksheet. To confirm that the name you thought was there was indeed.
Does it return "Pivottable2" in that list?
If so then you can progress the solution (into a kind of halfway house between the two suggestions).
For Each objPivot In objSheet.PivotTables
If objPivot.Name = "Pivottable2" Then
objPivot.RefreshTable
Exit For
End If
Next
(Could be something like Set xlWorkbook = objExcel.ActiveWorkbook)
Is it a whole book you're wanting to loop through? Or just the pivot tables in a given sheet?
As I said - mine wasn't intended to fix anything - the messagebox was to tell you the names of the pivot tables on that worksheet. To confirm that the name you thought was there was indeed.
Does it return "Pivottable2" in that list?
If so then you can progress the solution (into a kind of halfway house between the two suggestions).
For Each objPivot In objSheet.PivotTables
If objPivot.Name = "Pivottable2" Then
objPivot.RefreshTable
Exit For
End If
Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to both Harfang and Lpurvis for their assistance.
After combining both their suggestions my problem was solved.
Thanks,
karen
After combining both their suggestions my problem was solved.
Thanks,
karen
I'm glad you got it working. Success with your application!
(°v°)
(°v°)
What part of mine did you use?
lol or are you just saying that to be kind.