Open an Excel file in exclusive mode using VBScript

Question

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")

Answer 1

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:

Answer 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