Using VBS to open Excel spreadsheet with write access

Hi @Rick Crosby ,

Please let me know how the tests suggested by Marcin go, particularly whether the task works with “Run only when user is logged on.”

To add some context on why this happens: when a task is set to “Run whether user is logged on or not,” it runs in a non interactive session with no loaded user desktop and a limited Office profile. Excel automation depends on that profile for things like temporary folders, HKEY_CURRENT_USER settings, and file locking. If Excel cannot create its lock file next to the workbook in that session, it silently falls back to opening the file as read only instead of returning an error. That is why the same script behaves differently between a manual run and a scheduled run, and why the Open(WkbMain, False, False) call is not the cause here.

If the issue persists, please share:

• The workbook’s storage location, local drive or network share. Network shares and mapped drives are a common failure point because mapped drives are not available in a non interactive session, so a UNC path is required.
• The result of the ReadOnly check right after the workbook is opened.
• Any error message from the macro or the save operation.
• The account the task runs under, and whether “Run with highest privileges” is enabled.

Also worth confirming that the account running the task has Modify or Write permission on the folder containing the workbook, not just on the workbook itself, since Excel needs to create the lock file in that folder.

These details will help me narrow down the cause.

Thank you.

Considering that Excel successfully opens the correct workbook, you might want to focus on the difference between the interactive session and the Task Scheduler session rather than the file path.

Test by changing the task temporarily from "Run whether user is logged on or not" to "Run only when user is logged on." If the workbook then opens normally and the macros work, that indicates the problem is the noninteractive Task Scheduler session. Excel desktop automation can behave differently when launched without an interactive Windows desktop, particularly with the user's Office profile, HKEY_CURRENT_USER, temporary folders, and file locking.

Also verify that the task is running under exactly the same Windows account that you use when manually running the VBS. On the General tab, enable "Run with highest privileges." This does not automatically grant file permissions, so the account must also have Modify/Write permissions on the folder containing the workbook. Excel needs to create its temporary lock file there.

I would also populate Task Scheduler's "Start in (optional)" field with the folder containing the VBS/workbook, without quotation marks. This eliminates differences in the working directory between manual execution and Task Scheduler.

You can confirm what Excel is actually doing by checking the workbook immediately after opening it:

Set objWorkbook = objExcel.Workbooks.Open(WkbMain, False, False)
WScript.Echo "ReadOnly = " & objWorkbook.ReadOnly

If this reports True only when the task runs, then the VBS is not requesting read-only mode. Excel is choosing read-only because it cannot obtain the required write/lock access in that execution environment.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin