使用VBScript以独占模式打开Excel文件 [英] Open an Excel file in exclusive mode using VBScript

问题描述

我有一个简单的问题，但是我已经搜索过了，找不到任何有用的主题.

我正在使用一个VBScript来打开一个Excel文件并修改其中的一些内容.所以我正在使用以下代码:

 设置objXLApp = CreateObject("Excel.Application")objXLApp.Visible = FalseobjXLApp.DisplayAlerts = False设置objXLWb = objXLApp.Workbooks.Open(FilePath) 

现在，我要做的是使用一种锁定文件的方式打开Excel文件，并防止用户在脚本打开该文件时(直到关闭)打开该文件.

更新:

我认为问题与Excel实例有关，我尝试执行以下操作(当文件通过脚本打开时):

• 当我手动打开文件时(通过脚本打开)，它们都成为一个实例.
• 当我打开任何其他Excel文件时，它们都也都成为一个实例！！！并且原始文件(由脚本打开)变得可见！

现在这很奇怪，因为我使用的是 CreateObject("Excel.Application")，而不是 GetObject(，"Excel.Application")

解决方案

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

为用户提供了任何写访问权限.之后，当脚本保存文件时，将显示另一个警报:

前段时间，我创建了一个可与Excel应用程序一起使用的脚本，并且在Win 7 Excel 2010中遇到了与您所描述的相同的问题.我注意到，如果在脚本中使用 CreateObject()创建了多个Excel应用程序实例，那么用户打开的Excel文件始终完全使用第一个创建的实例.我已经通过创建两个不可见的Excel应用程序实例来解决了这个问题，比如说dummy和target.概述中，脚本的算法如下:

1. 首先创建虚拟实例，无需添加工作簿.之后，该虚拟实例将暴露一个Excel文件，供用户在其中打开.
2. 创建目标实例.
3. 退出虚拟实例.
4. 打开目标工作簿，进行修改并保存.
5. 退出目标实例.

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

 '目标文件路径sPath ="C:\ Users \ DELL \ Desktop \ test.xlsm"'创建虚拟实例设置oExcelAppDummy = CreateObject("Excel.Application")'创建目标实例设置oExcelApp = CreateObject("Excel.Application")退出虚拟实例oExcelAppDummy.Quit开放目标工作簿使用oExcelApp.Visible = False.DisplayAlerts =假设置oWB = .Workbooks.Open(sPath)结束于'进行一些更改并保存设置oWS = oWB.Sheets(1)oWS.Cells(1，1).Value = Now()保存'给额外的时间进行测试MsgBox尝试打开test.xlsm，确定结束脚本"'接近目标工作簿关闭退出目标实例oExcelApp.Quit 

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

脚本结束后的通知:

I have a simple question, but I've searched for this and couldn't find any helpful topics..

I'm working on a VBScript that opens an Excel file and modify a few stuff in it.. so I'm using this code:

    Set objXLApp = CreateObject("Excel.Application")

    objXLApp.Visible = False
    objXLApp.DisplayAlerts = False

    Set objXLWb = objXLApp.Workbooks.Open(FilePath)

Now, what I want to do is to open the Excel file using a way that locks the file and prevents the user from opening it while it's open by the script (until it's closed).

Update:

I think the problem is somehow related to the Excel instances, I tried to do the following (while the file is open by the script):

• When I manually open the file (while it's open by the script) they're both become a single instance.
• When I open any other Excel file they're both also become a single instance!!! And the original file (opened by the script) becomes visible!

Now this is weird because I'm using CreateObject("Excel.Application") and not GetObject(, "Excel.Application")

解决方案

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.

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:

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:

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. Create target instance.
3. Quit dummy instance.
4. Open target workbook, modify and save it.
5. Quit target instance.

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:

这篇关于使用VBScript以独占模式打开Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！