Solved

Error when triying to refresh pivot table within MS Access form

Posted on 2004-10-06
14
364 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
Comment Utility
Might be a stupid question, but is your pivot table called "PivotTable1"?
0
 

Author Comment

by:ken_berg
Comment Utility
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
Comment Utility
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
 

Author Comment

by:ken_berg
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 15

Expert Comment

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

Author Comment

by:ken_berg
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
It's fine with me.
0
 

Accepted Solution

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now