vbs to open excel in edit mode-CodePudding

I have a macro within an excel file that is automatically triggered once a day using the task scheduler and a vbs script that I copied directly from here: https://www.thespreadsheetguru.com/blog/how-to-automatically-run-excel-vba-macros-daily

The problem is, the users often have the file open already, which means the automated instance can not open and run. One idea was to make the excel file read-only by default (the users mostly only want to view and copy the data from the file). Now though, the vbs script can not open the file in edit mode. I tried a few different things in the Open Excel File step as shown below, but none of the options worked - with each, the script ran and completed, but the file wasn't saved - I guess because the excel was still opened in read-only mode. Any ideas how I can get the macro able to edit the file while still making it more difficult for the user to open the file in edit-mode?

 'Input Excel File's Full Path
  ExcelFilePath = "C:\Users\User1\Documents\ReadOnlyTest.xlsm"

'Input Module/Macro name within the Excel File
  MacroPath = "Module1.ATest"

'Create an instance of Excel
  Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
  ExcelApp.Visible = True  'or "False"

'Prevent any App Launch Alerts (ie Update External Links)
  ExcelApp.DisplayAlerts = False

'Open Excel File
  Set wb = ExcelApp.Workbooks.Open(ExcelFilePath, ReadOnly=False)
  ' Set wb = ExcelApp.Workbooks.Open(ExcelFilePath, IgnoreReadOnlyRecommended=True)
  'If wb.Application.ProtectedViewWindows.Count > 0 Then  ' this one taken from here: https://stackoverflow.com/questions/52110678/vbscript-enable-editing-in-excel-file-save-as. 
    'wb.Application.ActiveProtectedViewWindow.Edit
  'End If

'Execute Macro Code
  ExcelApp.Run MacroPath

'Save Excel File (if applicable)
  wb.Save

'Reset Display Alerts Before Closing
  ExcelApp.DisplayAlerts = True

'Close Excel File
  wb.Close

'End instance of Excel
  ExcelApp.Quit

CodePudding user response：

If you set the Workbook to ReadOnly on operating system level, you will not be able to save the file - because the operating system is preventing this (not Excel).

One solution could be to reset the ReadOnly-Flag before you open the file in your script. Use the SetAttr statement for that; 0 stands for normal, 1 for readonly. In VBA you can use vbNormal and vbReadOnly, but those are not defined in VBScript. Note that this is untested.

 SetAttr ExcelFilePath, 0
 Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)
 (...)
 wb.Close
 SetAttr ExcelFilePath, 1

Another option is to use ThisWorkbook.ChangeFileAccess xlReadOnly in the Open-Trigger of the workbook. In that case you need to disable events before opening the file with you script (ExcelApp.EnableEvents = False).
However this is a little bit dangerous: You will not be able to open the file in write mode by yourself unless you disable events manually (eg via the immediate window). Or you could add a check for the current user with environ("Username") and if it is you that is opening the file skip the ChangeFileAccess-statement

CodePudding user response：

Please, test the next adapted way:

Dim ExcelFilePath, MacroPath, wb

'Input Excel File's Full Path
  ExcelFilePath = "C:\Users\User1\Documents\ReadOnlyTest.xlsm"

'Change ReadOnly attribute
 CreateObject("Scripting.FileSystemObject").GetFile(ExcelFilePath).Attributes = 0

'Input Module/Macro name within the Excel File
  MacroPath = "Module1.ATest"

'Create an instance of Excel
  Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
  ExcelApp.Visible = True  'or "False"

'Open Excel File
  Set wb = ExcelApp.Workbooks.Open(ExcelFilePath) 

'Execute Macro Code
  ExcelApp.run "'" & ExcelFilePath & "'" & "!" & MacroPath

'Save Excel File (if applicable)
  wb.Save

'Close Excel File
  wb.Close

'End instance of Excel
  ExcelApp.Quit

'Change the workbook ReadOnly attribute to True
 CreateObject("Scripting.FileSystemObject").GetFile(ExcelFilePath).Attributes = 1

If it still does not run the macro, the workbook path to be open should be added to Excel Trusted Locations (Options - Trust Center - Trust Center Settings... - Trusted Locations - Add New location...).