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

[r90slash6]

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!

Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim strFile As String
Dim pt As PivotTable
 
Set appExcel = New Excel.Application
strFile = "\\pwrutc\wrkgrp\Operations\Facilities Plant Operations\CapitalPlanningDB\TomTest.xls"
Set wkb = appExcel.Workbooks.Open(strFile)
appExcel.Visible = True 'Do something with worksheet
 
   Set pt = ActiveSheet.PivotTables("PivotTable3")
       pt.RefreshTable
ActiveSheet.PivotTables("PivotTable3").PivotFields("CBR/130Y").CurrentPage = "2007045"
Set wkb = Nothing
Set appExcel = Nothing
Set pt = Nothing

[NeoPa]

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.

[r90slash6]

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.

[Stewart Ross]

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)?

[NeoPa]

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!

[r90slash6]

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

[Stewart Ross]

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

[r90slash6]

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

[Stewart Ross]

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

[Stewart Ross]

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

[Stewart Ross]

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

Set pt = appExcel.ActiveSheet.PivotTables("PivotTable3") 
appExcel.ActiveSheet.PivotTables("PivotTable3").PivotFields("CBR/130Y").CurrentPage = "2007045"

-Stewart

[r90slash6]

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

[r90slash6]

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

[NeoPa]

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 :

With wbk
  ...
  .ActiveSheet.Range(...)
  ...
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.

[r90slash6]

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

[Stewart Ross]

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