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

Move temporarily the Excel file to a special folder, open it, modify a few stuff in it, then save, close and move back :) — omegastripes, Mar 15 '16 at 20:46
I thought to do that as a workaround, but I'm wondering if there is a direct way to open the file exclusively — 41686d6564 stands w. Palestine, Mar 15 '16 at 21:32
If the file is open by the script it will only be readonly to any other attempts to open it. Do you still want to restrict this? — brettdj, Mar 16 '16 at 03:12
@brettdj This seems to be incorrect.. when I open the excel file (by the script) using the code in my question, I can double click on the file and it opens normally (I can edit anything and save) — 41686d6564 stands w. Palestine, Mar 16 '16 at 04:44
Added excel-vba tag to increase audience, and as the vba can be converted to vbs. — brettdj, Mar 16 '16 at 05:23
Could you please explain what the reason you intend to restrict an access is? Do you want to prevent the user from open it even in read-only mode? — omegastripes, Mar 16 '16 at 09:48
@omegastripes, No I think I'm okay if it'll be in read-only mode.. The problem is it doesn't open in read-only mode, and once it's opened the script can't proceed with the changes and throws exceptions — 41686d6564 stands w. Palestine, Mar 16 '16 at 10:20

brettdj · Answer 1 · 2016-03-16T06:14:19.620

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

edited Mar 16 '16 at 06:14

answered Mar 16 '16 at 05:23

brettdj

54,857
16
114
177

Since the file is read only, I won't be able to change its contents by the script (I just tested it btw) I also tried to set the file attributes = 32 **before** saving the file.. but that changes won't be saved! – 41686d6564 stands w. Palestine Mar 16 '16 at 05:52
@GeniuSBraiN updated given your need to change the file. Still surprised the default readonly protection doesn't apply. – brettdj Mar 16 '16 at 06:14
That's not what I want (I don't want to save as).. Anyway I did some debugging, and I came up with something.. kindly check the update in my question – 41686d6564 stands w. Palestine Mar 16 '16 at 06:33
Well the saveas is only an intermediate step - you end up with the single file. Will leave this to someone else to assist, I've taken this far enough. – brettdj Mar 16 '16 at 06:35
I understand that it's an intermediate step. @omegastripes already suggested something like this.. thank you for your help.. I appreciate your effort :) – 41686d6564 stands w. Palestine Mar 16 '16 at 06:40
The update to the question is not holding up. I have a small piece of VBS code that shows Excel 2010 not behaving like that update. Code doesn't show up well in comments so I won't post it here. I will post it as an answer if you confirm that other users can open the Excel file in read-only mode while your script is processing it. – MikeC Mar 17 '16 at 01:50
@MikeC what do you mean "not holding up?" – brettdj Mar 17 '16 at 01:51
The OP made an update to the question and parts of it are not correct. An example is this statement the OP added to the question: **When I -manually- open the file (while it's open by the script) they're both become a single instance.** That is not true and I have code that can demonstrate that; but since it is code, it will not come out looking well in a comment. If the OP affirms what I asked him/her to, then I'll post it as an answer. – MikeC Mar 17 '16 at 02:57

score 2 · Accepted Answer · edited Mar 20 '17 at 10:04

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:

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

The 'dummy instance' is really a great idea. This is perfect, it's exactly what I was looking for. Thank you so much :) — 41686d6564 stands w. Palestine, Mar 17 '16 at 07:27

Open an Excel file in exclusive mode using VBScript

2 Answers2

Linked