Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Error when triying to refresh pivot table within MS Access form

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
ken_berg
Asked:
ken_berg
  • 7
  • 5
1 Solution
 
will_scarlet7Commented:
Might be a stupid question, but is your pivot table called "PivotTable1"?
0
 
ken_bergAuthor Commented:
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
 
will_scarlet7Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
ken_bergAuthor Commented:
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
 
will_scarlet7Commented:
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
 
ken_bergAuthor Commented:
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
 
will_scarlet7Commented:
If you want to send it to me, my email address is in my profile.
0
 
ken_bergAuthor Commented:
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
 
will_scarlet7Commented:
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
 
ken_bergAuthor Commented:
Question:

How do I now close this question??

I have looked for a link but do not find one....
0
 
will_scarlet7Commented:
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
 
will_scarlet7Commented:
It's fine with me.
0
 
ee_ai_constructCommented:
Question answered by asker or dialog valuable.
Closed, 250 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now