第二次打开Excel数据透视表时的运行时错误91 [英] Runtime-error 91 when openning Excel Pivot Table for the 2nd time
问题描述
大家好，
我在Access窗体上有点击事件的代码，用于打开Excel文件，刷新活动工作表上的数据透视表的数据，然后根据一个标准。如果我第一次打开Access并单击按钮来运行我的代码，一切正常。但是，如果我关闭Excel文件，然后重新单击运行我的代码的按钮，我会收到一条调试消息，说运行时错误91，对象变量或未设置块。这是我的代码。代码停止在：Set pt = ActiveSheet.PivotTables（" PivotTable3"）。任何帮助将不胜感激。谢谢！
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!
推荐答案
虽然你整理了你的对象变量，但你似乎没有关闭Excel或工作簿。
下一次尝试可能会让我觉得很困难。
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.
感谢您的回复。虽然如果Excel和工作簿关闭，我的用户将没有机会查看数据透视表。
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.
嗯，我认为您需要通过设置断点来调试代码第5行，一次踩过一行。请特别注意AppExcel的值，因为您的错误消息告诉您，当您第二次打开工作簿时它未设置AppExcel，或者它无法访问工作表的数据透视表。
您还应该检查您是否可以看到Excel（因为您已将其设置为可见）并且它显示正确的工作簿 - 否则没有可供参考的ActiveSheet。
AppExcel在第13行中设置为空。之后尝试引用AppExcel会导致错误，但您没有这样做（至少在此代码提取中没有）。关闭Excel本身不应该导致这样的错误，除非你尝试在关闭后再次引用当前设置的对象AppExcel实例，因为自动化服务器不再连接到相关对象，但是你似乎也没有做这个。
-Stewart
ps如果您发现工作簿已打开且显示正常，请检查您的pivottable选择行的语法。活动工作表是存储数据透视表的工作表吗？用户是否在关闭工作簿之前更改了活动工作表（例如，通过打开另一个工作表来保存工作簿）？
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)?
这篇关于第二次打开Excel数据透视表时的运行时错误91的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
