Solved

Error when triying to refresh pivot table within MS Access form

Posted on 2004-10-06
14
369 Views
Last Modified: 2012-06-27
I am trying to issue out a refresh to a pivot table in Excel from Access.  

I have been looking over this wonderful site and found the following code:

   Dim xl As Excel.Application
   Set xl = New Excel.Application
   
   Me!PivotTable.Verb = acOLEVerbOpen
   Me!PivotTable.Action = acOLEActivate
   xl.ActiveSheet.PivotTables("PivotTable1").RefreshTable

The posting indicated that this works.   I have put the code in the Open event of the PivotTable form and it opens the PivotTable in Excel directly however I get the error "Object variable or With block variable not set" on the statment "xl.ActiveSheet.PivotTables("PivotTable1").RefreshTable".

In debug I can see that the Excel.Application statement in the set shows the same value.

I have registered (I think, I'm still pretty new to the Access coding stuff) the Excel Class library.

Help...
0
Comment
Question by:ken_berg
  • 7
  • 5
14 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12238377
Might be a stupid question, but is your pivot table called "PivotTable1"?
0
 

Author Comment

by:ken_berg
ID: 12238436
I just was looking at the pivot table in the excel sheet and through the wizard and the options function it does show "PivotTable1".  Looks like this is what is assigned to the table by default by Access.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12238790
Hi Ken,
I'm not real experienced with automating Excel from Access, but you could try inserting your code in the Worksheet's On Open event of the "ThisWorkbook" module instead of trying to run it from Access. The question below used this method:

http://www.experts-exchange.com/Databases/MS_Access/Q_20570302.html?query=ActiveSheet.PivotTables&topics=39
0
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.

 

Author Comment

by:ken_berg
ID: 12238908
Thanks for the reply will_scalet7.  

I think this could raise two problems from my side:
1. I don't have a permanent place to put the code.  It appears that the Excel object is opened dynamically by Access so I would have no place to put the script.  
2. I am trying to make this easily distributable and need to make sure I have as few places as possible to have code (I really do not want to have to put code in the spreadsheet also).
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12238999
Can you post a copy of your database somewhere I can download it. I might be able to help more if I can see and work with the file.
0
 

Author Comment

by:ken_berg
ID: 12239525
unfortunately I don't have a public ftp site or dummy email address. If you want to email me directly at bergk@eldoradoreno.com I would be glad to send it to you.  

I have more information also.

I forgot to mention that I am trying to get the code to work in a callable function in the Access project that is being invoked by an on-click event on a startup form.

The error that I was receiving was from a test database I have setup just to test this function.  

I just found in the posting that I got the orginal code from that he was using the code in the on open event of the form.

I have tried this code in the on-open event of my test project (not in the function) and it is now working OK.

However what I am trying to do (hence putting the code in a module) is to put all of my pivot tables (10 so far) on a startup panel that has one button per pivot table.  By doing this I then do not have to have the user go through the form directly and they will just see the data in Excel.

The code in the example above works just fine until I try to update the pivot table.

Now at this point I receive a message of:  
"Run time error 91: Object variable or With Block variable not set".

0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12239583
If you want to send it to me, my email address is in my profile.
0
 

Author Comment

by:ken_berg
ID: 12241543
I got it to work using code samples from other places on this site.

The code is:

Function LaunchPivotTable(FormToLaunch As Object, PivotTableName As String)


FormToLaunch!PivotTable.Verb = acOLEVerbOpen
FormToLaunch!PivotTable.Action = acOLEActivate
Set objPivotTable = FormToLaunch!PivotTable.Object
objPivotTable.ActiveSheet.PivotTables(PivotTableName).RefreshTable

End Function

By setting the object objPivotTable after the pivot table has been set by access I have the object to reference to refresh the table.   Now I get to work on other things in this program and use more searching on this wonderful site.

Thanks will for the ideas and guidance.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12241591
Glad to hear you got it working. If you want to put a request in at Comunity Service to have the question PAQ'd and points refunded, that's fine with me.

God bless!

Sam
0
 

Author Comment

by:ken_berg
ID: 12241622
Question:

How do I now close this question??

I have looked for a link but do not find one....
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12242649
If you would like your points refunded, post a question in the community service section:
http://www.experts-exchange.com/Community_Support/
asking the mods to close the question.

It's late here now so good night.

Sam
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12250270
It's fine with me.
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 12278324
Question answered by asker or dialog valuable.
Closed, 250 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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