Solved

Error when triying to refresh pivot table within MS Access form

Posted on 2004-10-06
14
371 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
[X]
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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

751 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