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

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