为什么即使关闭文件后文件仍保留在项目窗口中? [英] Why do files remain in project window even after files closed?

问题描述

场景

我有一个用户窗体，当按下一个按钮时，它以只读方式打开一个Excel文件，获取一些值，然后关闭该文件.以下是我用来打开和关闭文件的代码.

I have a userform, and when a button is pressed, it opens an Excel file as read-only, gets some values, and closes that file. Following are the codes I am using to open and close the file.

要打开:Set planbook = Workbooks.Open(planFilePath, ReadOnly:=True)

要关闭:planbook.Close False

问题

关闭文件后，每次按下按钮，我都会在项目窗口中看到该文件.

After closing the file, I see that file in the project window, for each time I press the button.

它正在使用大量内存.因此，Excel间歇性挂起.

It is using a lot of memory. Due to this, Excel hangs intermittently.

为什么以前打开文件但当前关闭的文件仍显示在项目窗口中?

Why are files opened earlier but currently closed still appearing in the project window?

请注意，当按下按钮3次(意味着打开和关闭工作簿3次)时，图片中列出了三个文件.

Note, when the button is pressed three times (means open and close a workbook three times), and thus there are three files listed in the picture.

编辑1
剩下的不是模块，尽管关闭了工作簿，但整个文件在VBA窗口中可见.

Edit 1
It's not the module that remains, the whole file is visible in the VBA window, although the workbook is closed.

编辑2
注意，如果我将宏打开一段时间，则项目窗口中显示的文件将一一消失.

Edit 2
Noticed, if I keep the macro open for some time, the files showing in the project window, disappear one by one.

编辑3
我发现，问题是由于打开和关闭共享工作簿引起的.有关更多说明，请参见答案.

Edit 3
I found, the issue is due to opening and closing a shared workbook. See the answer for more explanation.

推荐答案

经过多次检查，发现该问题不是由于任何代码引起的.完全是因为我要打开和关闭共享工作簿.使用共享工作簿时，这种类型的问题很常见.因此，最好避免使用共享工作簿.

After many checks, it is found out that issue is not due to any codes. It is purely because of I am opening and closing a shared workbook. This type of issues are common when use shared workbooks. So it is best to avoid usage of shared workbooks.

在这些过程中，我为清除内存问题所做的一些操作是

During these process some items I did to clear memory issue are

1. 使用完后立即清除变量和对象.

1. Clear out variables and objects immediately after their usage.

Eg: Set wb = nothing

在可能的情况下以只读方式打开工作簿.

Open workbooks as read only whenever possible.

Eg: Set wb = Workbooks.Open(blanketPo, ReadOnly:=True)

使用以下代码清空剪贴板，例如在运行宏时复制并保存在剪贴板中的对象和东西.我从另一个论坛获得了这段代码

Use the following code to empty clip board such as object and stuffs which were copied and keeping in clipboard while running macro. I got this code from another forum

Dim DataObj As New MSForms.DataObject 'empty the clipboard DataObj.SetText "" DataObj.PutInClipboard

Dim DataObj As New MSForms.DataObject 'empty the clipboard DataObj.SetText "" DataObj.PutInClipboard

无论如何，当我选择不使用共享工作簿时，大多数内存和挂断问题就消失了.因此，这主要是关于越野车共享工作簿的.

Anyway the moment I choose not to use shared workbook, most of the memory and hang up issues disappeared. So its mostly all about the buggy shared workbook.

这篇关于为什么即使关闭文件后文件仍保留在项目窗口中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！