从Word VBA编辑打开或关闭的工作簿 [英] Edit a workbook, whether open or closed, from Word VBA

问题描述

我正在尝试在Word中编写宏，以便可以将一些信息保存到计算机其他位置的Excel文件中.因此，我这样写:

I am trying to write a macro in Word so that I can save some information into an Excel file somewhere else in my computer. For this reason I wrote this:

Dim exlApp As Object
Dim exlWbk As Object
Set exlApp = CreateObject("Excel.Application")
Set exlWbk = exlApp.Workbooks.Open(FileName:="D:\database.xlsx")
exlWbk.ActiveSheet.Cells(1, 1).Value = "some info"
exlWbk.Close SaveChanges:=True
Set exlWbk = Nothing
exlApp.Quit
Set exlApp = Nothing

该代码对我而言非常正常，除非用户已打开有问题的Excel文件(database.xlsx).在这种情况下，运行宏将提示我将新更改保存到我的excel文件的新副本中，这不是我想要的.我希望新的更改包含在当前的Excel文件中，而不创建第二个副本.

The code works perfectly fine for me, except when the Excel file in question (database.xlsx) is already opened by the user. In that case, running the macro will prompt me to save the new changes into a new copy of my excel file, which is not what I want. I want the new changes to be included in the current Excel file without creating a second copy of it.

由于上述代码存在一些问题，所以我这样写:

Since the above code presented some problems, I wrote this:

Dim exlApp As Object
Dim exlWbk As Object
Set exlApp = CreateObject("Excel.Application")
Set exlWbk = exlApp.GetObject("D:\database.xlsx")
exlWbk.ActiveSheet.Cells(1, 1).Value = "some info"
exlWbk.Save
Set exlWbk = Nothing
exlApp.Quit
Set exlApp = Nothing

但是什么都没有改变.我知道有一些方法可以确定我的Excel文件是否打开，但是问题是，如果我发现该文件是打开的，我不知道如何更改代码.

But nothing changed. I know there are some ways to figure out whether my Excel file is open or not, but the problem is that I don't know how to change my code if I find out that file is open.

如何确定工作簿是否已在Excel中打开以便可以对其进行编辑，或者如何在关闭时打开文件进行编辑?

How can I determine whether a workbook is open in Excel so that it can be edited, or open the file in order to edit it if it's closed?

推荐答案

根据

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 Ranges, 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

这篇关于从Word VBA编辑打开或关闭的工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！