更新时间:2023-12-05 17:19:58
According to the documentation, GetObject(filename)
will pick up the existing file if it's already open or, optionally, open the file if it is not open:
执行此代码后,与指定的路径名已启动,并且指定文件中的对象是已激活.
When this code is executed, the application associated with the specified pathname is started, and the object in the specified file is activated.
如果Excel未运行,则默认情况下,执行 GetObject(filename)
时,任何内容均不可见.将打开Excel,将打开并更改文件.因此,存在一个真正的危险,那就是Excel和工作簿的实例将在内存中挂起",这可以在Windows的任务管理器"中看到.重复运行此类代码最终可能会导致Windows崩溃,因此必须注意在每次迭代时正确清理内容.
If Excel is not running, by default nothing will be visible when GetObject(filename)
executes. Excel will be opened, the file will be opened and changed. There's a real danger, therefore, that the instance of Excel and the workbook will "hang" in memory, which could be seen in the Windows "Task Manager". Repeated running of such code can eventually crash Windows, so care must be taken to clean things up correctly on each iteration.
由于该问题还规定用户可以打开该文件,因此有必要确定该文件以及Excel应用程序是否已在运行.
Since the question also stipulates that the file could be opened already by a user, it's necessary to determine that, as well as whether the Excel application is already running.
下面的代码示例演示了如何完成此操作.假定应用程序和文件均未打开.然后它会测试Excel是否已经在运行.
The following code sample demonstrates how this can be done. The assumption is that neither the applicaton nor the file is open. Then it tests whether Excel is already running.
Set xlApp = GetObject(, "Excel.Application")
请注意语法上的差异:代替 fileName
的是逗号,后跟应用程序的名称.这将检查该应用程序是否可用;如果不是,将触发错误.因此, On Error Resume Next>
在 GetObject
之前,这意味着该错误将被忽略.
Notice the difference in the syntax: instead of the fileName
there's a comma, followed by the name of the application. This will check whether the application is available; if it's not, an error will be triggered. Therefore, On Error Resume Next
precedes GetObject
, which means the error will be ignored.
由于忽略错误很危险,因此下一行 Or Error GoTo 0
会重新打开错误.
Since ignoring errors is dangerous, the next line Or Error GoTo 0
turns errors back on.
如果 GetObject
不成功,则无法实例化变量 xlApp
,并且其值"为 Nothing
.如果可以实例化 xlApp
,并且将布尔值 appAlreadyOpen
设置为true,则执行 If Not xlApp Nothing
,因此我们知道 not 代码完成后退出Excel.它还检查所需的工作簿是否已经打开.如果是,则可以实例化 xlWb
并将fileAlreadyOpened设置为true.
If GetObject
is not successful, the variable xlApp
could not be instantiated and its "value" is Nothing
. If Not xlApp Is Nothing
executes if xlApp
could be instantiated and the Boolean appAlreadyOpen
is set to true so that we know to not quit Excel when the code finishes. It also checks whether the required workbook is already open. If it is, xlWb
can be instantiated and fileAlreadyOpened set to true.
如果无法实例化 xlWb
,或者由于Excel应用程序未运行或尚未打开工作簿,则执行 GetObject(fileName)
.该工作簿将在Excel的现有实例(如果已运行)或Excel的新实例中打开.在此代码块的末尾注释了两行:如果使新启动的Excel应用程序可见并在代码结束时保持打开状态,请取消注释它们.
If xlWb
could not be instantiated, either because the Excel application was not running or the workbook was not yet open, GetObject(fileName)
is executed. The workbook will be opened, in the existing instance of Excel if already running or in a new instance of Excel. At the end of this code block two lines are commented: should the newly started Excel application be made visible and remain open when the code ends, uncomment them.
然后可以编辑工作簿.
最后,需要清理东西.检查布尔值,如果不为true,则关闭工作簿和可能的应用程序. 非常重要 是从内存中释放这些对象的最后两行.如果代码创建了其他任何对象,例如 Range
,则也应以实例化它们的相反顺序释放这些对象.
Lastly, things need to be cleaned up. The Booleans are checked and if not true, the workbook and possibly the application are closed. Very important are the last two lines that release these objects from memory. If the code creates any other objects, such as Range
s, these should also be released, in the reverse order they are instantiated.
Sub GetFileOpenedOrClosed()
Dim xlApp As Object ' Excel.Application
Dim xlWB As Object, wb As Object ' Excel.Workbook
Dim fileName As String
Dim fileAlreadyOpen As Boolean, appAlreadyOpen As Boolean
fileName = "C:\Test\SampleChart.xlsx"
fileAlreadyOpen = False
appAlreadyOpen = False
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0
If Not xlApp Is Nothing Then
appAlreadyOpen = True
For Each wb In xlApp.Workbooks
If wb.FullName = fileName Then
Set xlWB = wb
fileAlreadyOpen = True
Exit For
End If
Next
End If
If xlWB Is Nothing Then
Set xlWB = GetObject(fileName)
Set xlApp = xlWB.Application
xlWB.Windows(1).Visible = True 'So that the window is not hidden when file is opened again
'xlApp.Visible = True
'xlApp.UserControl = True
End If
xlWB.Worksheets(1).Cells(7, 1).value = "some other info"
If Not fileAlreadyOpen Then
xlWB.Save
xlWB.Close
End If
If Not appAlreadyOpen Then
xlApp.Quit
End If
Set xlWB = Nothing
Set xlApp = Nothing
End Sub