We help IT Professionals succeed at work.

Refresh Excel Pivot table from Access

Karen Schaefer
on
2,962 Views
Last Modified: 2008-05-07
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.Application")
    objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intranet Information Server\CorePlan\SpreadSheets\" & gFileName & ".xls")
    objExcel.Visible = True
    objExcel.DisplayAlerts = False
    'objExcel.PivotTable.Verb = acOLEVerbOpen
    objExcel.PivotTableSelection = acOLEActivate
    'PivotTable.Action = acOLEActivate
    objExcel.ActiveSheet.PivotTables("PivotTable1").RefreshTable
    Set objExcel = Nothing


OpenExcelPivot_Exit:
    Exit Function

OpenExcelPivot_Err:
  MsgBox Error$

   Resume OpenExcelPivot_Exit

End Function

It doesn't like "   objExcel.ActiveSheet.PivotTables("PivotTable1").RefreshTable" portion of the code

Unable to get Pivot Table property of of worksheet class.

Thanks.

Karen
Comment
Watch Question

Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Is that error at compile time or runtime?
Karen SchaeferBI ANALYST

Author

Commented:
runtime
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
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.Application")
    objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intranet Information Server\CorePlan\SpreadSheets\" & gFileName & ".xls")
    objExcel.Visible = True
    objExcel.DisplayAlerts = False
    'objExcel.PivotTable.Verb = acOLEVerbOpen
    objExcel.PivotTableSelection = acOLEActivate
    'PivotTable.Action = acOLEActivate
    Set objSheet = objExcel.ActiveSheet
    Msgbox "About to refresh on " & objSheet.Name
    Msgbox "About to refresh on " & objSheet.PivotTables("PivotTable1").Name
    objSheet.PivotTables("PivotTable1").RefreshTable

See if that changes anything - and if not which line it errors on.
(Naturally the message boxes are to be removed in time).
Karen SchaeferBI ANALYST

Author

Commented:
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
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
How many Pivot tables are there in your worksheet in question?
Karen SchaeferBI ANALYST

Author

Commented:
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
Karen SchaeferBI ANALYST

Author

Commented:
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.Application")
    objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intranet Information Server\CorePlan\SpreadSheets\" & gFileName & ".xls")
    objExcel.Visible = True
    objExcel.DisplayAlerts = False
    'objExcel.PivotTable.Verb = acOLEVerbOpen
    objExcel.PivotTableSelection = 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(nPName).Name
    objSheet.PivotTables(nPName).RefreshTable

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
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
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.Application")
    objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intranet Information Server\CorePlan\SpreadSheets\" & gFileName & ".xls")
    objExcel.Visible = True
    objExcel.DisplayAlerts = False
    'objExcel.PivotTable.Verb = acOLEVerbOpen
    objExcel.PivotTableSelection = acOLEActivate
    'PivotTable.Action = acOLEActivate
    Set objSheet = objExcel.ActiveSheet
   
    For Each objPivot  In objSheet.PivotTables
        strList = strList & objPivot.Name & vbcrlf
    Next
    Msgbox strList
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
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 ;-)

Commented:
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°)
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Noo - not stopped me.. lol
Why to stop me it would take a sunny day - a fridge of beer and...
Karen SchaeferBI ANALYST

Author

Commented:
harfang,

where do you handle the pstrWorkbook variable?

K
Karen SchaeferBI ANALYST

Author

Commented:
I am getting Automation error invalid syntax?

K

Commented:
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\Intranet Information Server\CorePlan\SpreadSheets\" & 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°)
Karen SchaeferBI ANALYST

Author

Commented:
What is the syntax to declare the path as a global declaration?

k

Commented:
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°)
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
You want to declare the folder path as a global constant?
Karen SchaeferBI ANALYST

Author

Commented:
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.Application")
    'objExcel.Workbooks.Open ("\\Wabelhdk0215892\Intranet Information Server\CorePlan\SpreadSheets\" & pstrWorkbook & ".xls")
    objExcel.Workbooks.Open (pstrWorkbook)
    objExcel.Visible = True
    objExcel.DisplayAlerts = False
    'objExcel.PivotTable.Verb = acOLEVerbOpen
    objExcel.PivotTableSelection = 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).Visible = False
   ' xlWorkbook.Application.Visible = 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
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
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
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Karen SchaeferBI ANALYST

Author

Commented:
Thanks to both Harfang and Lpurvis for their assistance.

After combining both their suggestions my problem was solved.

Thanks,

karen

Commented:
I'm glad you got it working. Success with your application!
(°v°)
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
What part of mine did you use?
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
lol or are you just saying that to be kind.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.