By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 1,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

Runtime-error 91 when openning Excel Pivot Table for the 2nd time

P: 9
Hi Guys,

I have code for click event on an Access form that opens an Excel file, refreshes data of a pivot table on the active worksheet and then filters records based on a criteria. Everything works fine if I open Access for the first time and click the button to run my code. However, if I close the Excel file and then reclick the button that runs my code I receive a debug message saying "Run-time error 91, Object variable or with block not set." Here is my code. The code halts at: Set pt = ActiveSheet.PivotTables("PivotTable3"). Any help would be greatly appreciated. Thanks!
Expand|Select|Wrap|Line Numbers
  1. Dim appExcel As Excel.Application
  2. Dim wkb As Excel.Workbook
  3. Dim strFile As String
  4. Dim pt As PivotTable
  5.  
  6. Set appExcel = New Excel.Application
  7. strFile = "\\pwrutc\wrkgrp\Operations\Facilities Plant Operations\CapitalPlanningDB\TomTest.xls"
  8. Set wkb = appExcel.Workbooks.Open(strFile)
  9. appExcel.Visible = True 'Do something with worksheet
  10.  
  11.    Set pt = ActiveSheet.PivotTables("PivotTable3")
  12.        pt.RefreshTable
  13. ActiveSheet.PivotTables("PivotTable3").PivotFields("CBR/130Y").CurrentPage = "2007045"
  14. Set wkb = Nothing
  15. Set appExcel = Nothing
  16. Set pt = Nothing
Sep 24 '08 #1
Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,342
Although you tidy up your object variables, you don't seem to close either Excel or the Workbook.

Next attempt is likely to struggle I would think.
Sep 25 '08 #2

P: 9
Thank you for your reply. Although if Excel and the workbook closes, my user would not have a chance to look at the the pivot table.
Sep 25 '08 #3

Expert Mod 2.5K+
P: 2,545
Mmm, I think you will need to debug your code by setting a breakpoint at line 5 and stepping through one line at a time. Pay particular attention to the value of AppExcel, as your error message is telling you that AppExcel is not set when you are referencing it to open the workbook that second time, or alternatively that it cannot access the pivot table of the sheet.

You should also check that you can see Excel (as you have set it visible) and that it is showing the correct workbook - otherwise there is no ActiveSheet to refer to.

AppExcel is set to nothing in line 13. Trying to reference AppExcel thereafter would cause an error, but you are not doing this (at least not in this code extract). Closing Excel of itself should not cause such an error unless you try to reference the currently-set instance of object AppExcel again after the closure, as the automation server is no longer connected to the object concerned, but again you do not appear to be doing this.

-Stewart

ps If you find that the workbook is open and is showing OK then check the syntax of your pivottable selection line. Is the active sheet the one on which your pivot table is stored? Did the user change the active sheet before closing the workbook (by saving the workbook with a different sheet open, for instance)?
Sep 25 '08 #4

NeoPa
Expert Mod 15k+
P: 31,342
Thank you for your reply. Although if Excel and the workbook closes, my user would not have a chance to look at the the pivot table.
That makes sense. You do, however, want to make sure the workbook is not still open when the code runs again. I suspect you are already.

I see no reason why you would get the error you do, but then we can only see a snippet of your code. Nothing indicates what type of procedure it's found in, which may be relevant.

If you follow Stewart's advice and trace through the code (Debugging in VBA), I expect you'll find something that will make the situation clearer :)

Welcome to Bytes!
Sep 25 '08 #5

P: 9
Stewart and NeoPa,

I tried stepping through the code as advised by Stewart. The variable "pt" is being set to "nothing" and appExcel.visble is "True". Also, the correct workbook and worksheet is being saved when I close the first instance of Excel. Let me clarify what's going on. When I run the code to open the pivot table everything works as designed. However, if I close the pivot table and Excel application and rerun my code, Excel will reopen the correct workbook and display all the pivot table data but without filtering on my criteria "2007045". I then get my Err 91. I made a stripped down mdb file and pivot table for testing purposes. I can get it to fail in this stripped down version. Can you guys take a look so you can see what I mean?

Thank very much,
Tom
Sep 25 '08 #6

Expert Mod 2.5K+
P: 2,545
Hi Tom. If it would help then by all means post a copy of your database. To do this, you'd need to zip the DB and the Excel file and attach them to your next post. It is done by editing your next post within an hour after creating it - if you choose Manage Attachments you can upload the zip file and attach it to your post.

-Stewart
Sep 25 '08 #7

P: 9
Thanks Stewart! Here you go. BTW: you'll figure it out but The files should go under a folder named TomTest under root of C drive. Thanks again
Attached Files
File Type: zip TomTest.zip (55.2 KB, 113 views)
Sep 25 '08 #8

Expert Mod 2.5K+
P: 2,545
Hi Tom. I have so far found that on all but one occasion when I opened the Excel sheet from Access that the PivotTable object caused a failure. For me, this occurred regardless of whether I had opened the sheet already or not.

I used debug.print to try to print the pivottables.count value from the active sheet - this caused an immediate failure, just as it did for you on trying to set the pivottable object. However, in the immediate window I can access the pivottable count without a problem. I also tried referring to it from ActiveSheet, WorkSheets(1) and WorkSheets("Test") (all equivalent ways to do so) - no go. I tried explicitly activating the worksheet first, using ActiveWorkbook.Worksheets(1).Activate. No go. I tried using DoEvents to make sure there were no queued event processes causing issues. No go.

All this needs further investigation. It is a large pivottable, connected via a SQL Server DB (I have commented out the table refresh method in your code as I don't have a connection for the DB).

Very interesting problem. No solution so far, but timing is on the top of my list of suspects. I think what is happening is that the sheet is opening correctly, but the pivottable collection is not available in code until Excel has finished doing whatever it does under the hood in initialising the sheet. This I reckon is too late for the automation code, which has already tried to access the pivottable collection and failed.

-Stewart
Sep 25 '08 #9

Expert Mod 2.5K+
P: 2,545
Well, it's not a timing issue; the pivot table count is working correctly, and I can access the name of the pivot table - but I can't set object variable pt to the value of pivottables(1) or pivottables("PivotTable3"). A real conundrum at present.

-Stewart
Sep 25 '08 #10

Expert Mod 2.5K+
P: 2,545
Resolved - and I should have seen it straight away too. Amazing what can be overlooked.

Problem was that you are referring to ActiveSheet without the automation object appExcel in two places - which works within Excel itself (where ActiveSheet is an implied property of Excel) but not consistently when running as an automation server in Access. It's a very subtle error.

Lines 11 and 13 in post #1 should be replaced with

Expand|Select|Wrap|Line Numbers
  1. Set pt = appExcel.ActiveSheet.PivotTables("PivotTable3") 
  2. appExcel.ActiveSheet.PivotTables("PivotTable3").PivotFields("CBR/130Y").CurrentPage = "2007045"
-Stewart
Sep 25 '08 #11

P: 9
Hey Stewart, I appreciate all your efforts. I've been racking my brain over this problem for days now. I guess if it's not possible I will resort to using the Pivot table form that comes with Access. However, it just doesn't look as nice as the Excel pivot table. Thanks again
Sep 25 '08 #12

P: 9
OMG! You are awesome Stewart! I am so elated and relieved and so will my boss. Thank you, Thank you so very very much. You are the man.
Tom
Sep 25 '08 #13

NeoPa
Expert Mod 15k+
P: 31,342
Nice one Stewart. I've just got in so I'm pleased to see you've already done all the hard work here :D

I wanted to post as I noticed the timing on the previous posts and suspect you've both missed the other's posts (Thought I'd trigger another look for you both).

While I'm here, I'll mention that what I do to avoid this problem when running Excel as an Automation Server (Excel work from within Access code) is to surround my code with a With of the workbook as in :
Expand|Select|Wrap|Line Numbers
  1. With wbk
  2.   ...
  3.   .ActiveSheet.Range(...)
  4.   ...
  5. End With
Hope this makes sense.

** Edit **
I just posted this from a page (I can still see) that I loaded less than ten minutes ago. Post #13 is not shown there at all.
Sep 26 '08 #14

P: 9
Thank you again NeoPa and Stewart for your help and for being so quick to find and answer! I have found Bytes very useful in the past. This was the first time I had ever asked for help through a forum and it sure was worth it.

Thanks again,
Take care,
Tom
Sep 26 '08 #15

Expert Mod 2.5K+
P: 2,545
Very glad we were of assistance to you - and thanks also to NeoPa for showing how using a simple With statement could help avoid this kind of omission.

Cheers

Stewart
Sep 26 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.