使用VBScript以独占模式打开Excel文件-OpenanExcelfileinexclusivemodeusingVBScript

2  

There is registry key HKEY_CLASSES_ROOT\Excel.Sheet.8\shell\Open\command on Win 7 Excel 2010 for me with default value "C:\Program Files\Microsoft Office\Office14\EXCEL.EXE" /dde. The command line /dde switch enables DDE (Dynamic Data Exchange mechanism - an ancient Win 3.0 interprocess communication method) that forces Excel to start in a single instance. I've tried to remove that switch and opened workbooks, but to no avail. BTW, if you don't have a permission to edit the registry, or you intend to distribute your script to someone who doesn't, that is not a way. Also have tried this answer, but it doesn't work for Win 7 Office 2010.

在Win 7 Excel 2010上有我的注册表项HKEY_CLASSES_ROOT \ Excel.Sheet.8 \ shell \ Open \命令，默认值为“C：\ Program Files \ Microsoft Office \ Office14 \ EXCEL.EXE”/ dde。命令行/ dde开关启用DDE（动态数据交换机制 - 一种古老的Win 3.0进程间通信方法），它强制Excel在单个实例中启动。我试图删除该开关并打开工作簿，但无济于事。顺便说一句，如果您没有编辑注册表的权限，或者您打算将脚本分发给没有编辑注册表的人，那么这不是一种方法。也试过这个答案，但它不适用于Win 7 Office 2010。

I've tested test.xlsm file with DDE enabled. When user opens a file, actually it is just reopened in existing instance that make it visible. If any changes has been already made by the script, then Excel alerts:

我已经在启用了DDE的情况下测试了test.xlsm文件。当用户打开文件时，实际上它只是在现有实例中重新打开，使其可见。如果脚本已经进行了任何更改，则Excel会发出警报：

Anyway write-access is given for the user. After that when the script saves the file, another alert appears:

无论如何，为用户提供了写访问权限。之后，当脚本保存文件时，会出现另一个警报：

Some time ago I created a script that worked with Excel application, and encountered the same issue with Win 7 Excel 2010 as you are describing. I noticed that if there were several Excel application instances created with CreateObject() within script, then Excel file opened by user always used exactly the first created instance. I've solved the issue by creating two invisible instances of Excel application, let's say dummy and target. In outline the algorithm for a script is as follows:

前段时间我创建了一个与Excel应用程序一起使用的脚本，并且遇到了与您描述的Win 7 Excel 2010相同的问题。我注意到如果在脚本中使用CreateObject（）创建了多个Excel应用程序实例，则用户打开的Excel文件始终使用的是第一个创建的实例。我已经通过创建两个不可见的Excel应用程序实例解决了这个问题，让我们说假人和目标。概括地说，脚本的算法如下：

1. Create dummy instance first, no need to add a workbook. After that the dummy instance is exposured an Excel file to be opened by user within it.
2. 首先创建虚拟实例，无需添加工作簿。之后，虚拟实例将暴露一个Excel文件，由用户在其中打开。
3. Create target instance.
4. 创建目标实例。
5. Quit dummy instance.
6. 退出虚拟实例。
7. Open target workbook, modify and save it.
8. 打开目标工作簿，修改并保存。
9. Quit target instance.
10. 退出目标实例。

Consider the below code that illustrates a possible way to implement what you need:

请考虑以下代码，该代码说明了实现所需内容的可能方法：

' target file path
sPath = "C:\Users\DELL\Desktop\test.xlsm"
' create dummy instance
Set oExcelAppDummy = CreateObject("Excel.Application")
' create target instance
Set oExcelApp = CreateObject("Excel.Application")
' quit dummy instance
oExcelAppDummy.Quit
' open target workbook
With oExcelApp
    .Visible = False
    .DisplayAlerts = False
    Set oWB = .Workbooks.Open(sPath)
End With
' make some changes and save
Set oWS = oWB.Sheets(1)
oWS.Cells(1, 1).Value = Now()
oWB.Save
' give additional time for test
MsgBox "Try to open test.xlsm, OK to end the script"
' close target workbook
oWB.Close
' quit target instance
oExcelApp.Quit

Trying open the file you will get desired output:

尝试打开文件，您将获得所需的输出：

And the notification after the script ends:

脚本结束后的通知：

2  

That is strange that you aren't getting a message as below:

奇怪的是你没有得到如下信息：

One possible method would be

一种可能的方法是

• to change the file attributes at the start and end of the code, the version below makes the file readonly and hidden
• 要更改代码开头和结尾的文件属性，下面的版本会使文件只读和隐藏
• make your changes
• 做出你的改变
• save the file with a different name
• 使用其他名称保存文件
• change the attributes back
• 更改属性
• rename the changed file to the original name
• 将更改的文件重命名为原始名称

code

码

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objXLApp = CreateObject("Excel.Application")

filePath = "C:\Temp\MyFile.xlsm"
filePath2 = "C:\Temp\MyFile1.xlsm"

set objFile = objFSO.GetFile(filePath)
objFile.Attributes = 3

objXLApp.Visible = False
objXLApp.DisplayAlerts = False

Set objxlWB = objXLApp.Workbooks.Open(filePath)
'do stuff
objxlWB.saveas filePath2
objxlWB.Close
objXLApp.Quit
set objXLApp = Nothing

objFile.Attributes = 32
objFile.Delete
objFSO.MoveFile filePath2, filePath