Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Refresh Excel Pivot table from Access

Posted on 2006-06-08
25
2,770 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
0
Comment
Question by:Karen Schaefer
  • 10
  • 9
  • 6
25 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16866047
Is that error at compile time or runtime?
0
 

Author Comment

by:Karen Schaefer
ID: 16866074
runtime
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16866126
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).
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Karen Schaefer
ID: 16866252
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16866299
How many Pivot tables are there in your worksheet in question?
0
 

Author Comment

by:Karen Schaefer
ID: 16866357
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
0
 

Author Comment

by:Karen Schaefer
ID: 16866428
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16866431
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
0
 
LVL 58

Assisted Solution

by:harfang
harfang earned 500 total points
ID: 16869636
Hello kfschaefer1

Actually, it seems better to refer to the pivot tables by number in this case. Provided the activesheet has a pivot table, ActiveSheet.PivotTables(1).RefreshTable will work. On the other hand, your code does not control which sheet is active, so that's another cause for bugs.

However, I wonder if you would not prefer to refresh all pivot tables? Depending on the complexity of your workbook, this might be an overkill, naturally. Also, if you have different pivot tables based on the same data source, you might want to refresh that (or those) directly.

The following code does that: refresh the data source(s) of all pivot tables...

Sub OpenXL_Pivot(pstrWorkbook As String)

    Dim xlWorkbook As Excel.Workbook
    Dim xlPivotCache As Excel.PivotCache

On Error GoTo Error_Label
    DoCmd.Hourglass True
    ' get (or open) workbook
    Set xlWorkbook = GetObject(pstrWorkbook)
    ' refresh all pivot tables
    For Each xlPivotCache In xlWorkbook.PivotCaches
        xlPivotCache.Refresh
    Next xlPivotCache
   
Exit_Label:
    On Error Resume Next
    ' make sure everything is visible...
    xlWorkbook.Activate
    xlWorkbook.Windows(1).Visible = True
    xlWorkbook.Application.Visible = True
    DoCmd.Hourglass False
    Exit Sub
   
Error_Label:
    MsgBox Err.Description
    Resume Exit_Label
   
End Sub

Cheers!

(°v°)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16869648
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 ;-)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16869759
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°)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16869773
Noo - not stopped me.. lol
Why to stop me it would take a sunny day - a fridge of beer and...
0
 

Author Comment

by:Karen Schaefer
ID: 16874204
harfang,

where do you handle the pstrWorkbook variable?

K
0
 

Author Comment

by:Karen Schaefer
ID: 16874243
I am getting Automation error invalid syntax?

K
0
 
LVL 58

Expert Comment

by:harfang
ID: 16874346
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°)
0
 

Author Comment

by:Karen Schaefer
ID: 16874392
What is the syntax to declare the path as a global declaration?

k
0
 
LVL 58

Expert Comment

by:harfang
ID: 16874596
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°)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16874606
You want to declare the folder path as a global constant?
0
 

Author Comment

by:Karen Schaefer
ID: 16874657
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16874747
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
0
 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
ID: 16875199
kfschaefer1,

If you don't mind following two parallel threads, here is a version better suited for an automated nightly run. It saves the refreshed workbook and no longer displays an error message when a problem is encountered.


Sub XL_RefreshPivots(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
    For Each xlPivotCache In xlWorkbook.PivotCaches
        xlPivotCache.Refresh
    Next xlPivotCache
   
Exit_Label:
    On Error Resume Next
    ' close everything
    xlWorkbook.Windows(1).Visible = True
    xlWorkbook.Close SaveChanges:=True
    Exit Sub
   
Error_Label:
    ' insert here a log of the messages,
    ' to review in the morning (table, textfile, etc)
    Resume Exit_Label
   
End Sub


You would then leave the above as it is, and call that from your routine, e.g.:

Sub EveryNight()
   
    Const FilePath = "\\Wabelhdk0215892\Intranet Information Server\CorePlan\SpreadSheets\"

    XL_RefreshPivots FilePath & "File One with Pivots.xls"
    XL_RefreshPivots FilePath & "Another Workbook.xls"
    XL_RefreshPivots FilePath & "Etc.xls"

End Sub

Something similar could also be done with a loop, or for all .xls files in a folder, etc.

Cheers!
(°v°)
0
 

Author Comment

by:Karen Schaefer
ID: 16921992
Thanks to both Harfang and Lpurvis for their assistance.

After combining both their suggestions my problem was solved.

Thanks,

karen
0
 
LVL 58

Expert Comment

by:harfang
ID: 16922038
I'm glad you got it working. Success with your application!
(°v°)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16922094
What part of mine did you use?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16922099
lol or are you just saying that to be kind.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question