Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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