VBA Script runs in Excel but not CMD/Powershell

Question

I have a VBA script in Excel which works fine but when saved as script_name.vbs and executed in cmd/powershell as cscript.exe script_name.vbs it throws the error:

dir_path\script_name.vbs(30, 37) Microsoft VBScript compilation error: Expected ')'

Firstly I apologise. This seems like a well-worn question but no answer I could find explains any reasons why my particular VBA script won't work.

I learnt that you cannot Dim As when running vbs on the cmd line so I removed that, and then got the above error. No question I found seems to indicate to me as to why.

Help much appreciated!

Thanks

FYI: The macro is to iterate through all files which have passwords in a folder and

1. Attempt a number of any possible passwords to open the file
2. Same again for workbook protection passwords
3. Unhide all worksheets
4. Save the file
5. Move onto the next file

Sub BAUProcessVBA()

Dim wb
Dim ws
Dim myPath
Dim myFile
Dim myExtension
Dim i


'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

myPath = "C:\blah\dir\"

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
    Debug.Print myFile
    On Error Resume Next
    Set wb = Workbooks.Open(Filename:=myPath & myFile, Password:="pw1", IgnoreReadOnlyRecommended:=True, ReadOnly:=False)
    Set wb = Workbooks.Open(Filename:=myPath & myFile, Password:="pw2", IgnoreReadOnlyRecommended:=True, ReadOnly:=False)
    On Error GoTo 0
    
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
      
    'Remove workbook protection and, unhide all tabs, save the file
    On Error Resume Next
    wb.Unprotect "pw1"
    wb.Unprotect "pw2"
        On Error GoTo 0
    
        On Error Resume Next
    wb.Password = ""
        On Error GoTo 0
    For Each ws In wb.Worksheets
        ws.Visible = xlSheetVisible
    Next ws

    'Save and Close Workbook
    Application.CutCopyMode = False
    wb.Close SaveChanges:=True
    Application.EnableEvents = False
          
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents

    'Get next file name
      myFile = Dir
  Loop

  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Answer 1

You seem under the impression that Visual Basic for Applications vba and Visual Basic Script vbscript are identical languages. That is not the case. They may be more closely related than Visual Basic .Net vb.net and VBA or VBS, but they are still different languages.
Which is why we have different tags for all of them.

Now, to tackle your question: VBA has got the Microsoft Office Object Library reference, which means native support for office objects.
Application doesn't exist in vbs, so you need to create that object: Set Application = WScript.CreateObject("Excel.Application")

Excel constants don't exist: xlCalculationManual = -4135, xlCalculationAutomatic = -4105 and xlSheetVisible = -1

Dir doesn't exist, so you need to create a FileSystemObject

Named arguments don't exist, so you need commas: Set wb = app.Workbooks.Open(myPath & myFile, , False, , "pw1", , True)

And DoEvents doesn't exist either.

Answer 2

To solve this problem I have used Python to open Excel and execute the Macro I want. Below is a function that should work for anyone.

Things I have learnt: If you have VBA code in Excel and want to run it without Excel then you cannot just save this as a .vbs and execute it on the command line with cscript.exe.

VBS and VBA are different languages.

Therefore, a quick tutorial for those stuck at the same problem but are unfamiliar with Python:

1. Download and install Python ensuring python is added to PATH. This script was written and successfully executed with Python 3.8 64-bit for Windows. https://www.python.org/downloads/

2. Save the below in a file called run_macro.py

3. On the last line of run_macro.py, with no indentation, type what is below within Code2

4. Carrying on with Code2: Inside of the quotes 'like this' type in what it's asking for. The filepath_incl_filename must contain the full path AND the filename whereas filename must contain ONLY the filename. Yes, it must be provided like this.

5. Copy the filepath where run_macro.py is located and press win+r and type 'cmd' to open the cmd terminal, then type cd <filepath from clipboard> and press enter

6. Now type python run_macro.py

7. So long as you get no errors and it appears to "freeze" then that means it's working. Otherwise, you will need to debug the errors.

Code:

import win32com.client as wincl
    
def run_excel_macro(filepath_incl_filename=r'raw_filepath', filename='', module_name='', macro_name=''):
        """
        :param filepath_incl_filename: Must be r'' filepath to dir with filename but also include the filename in the filepath (c:\etc\folder\wb_with_macro.xlsm)
        :param filename: Filename of xlsm with the Macro (wb_with_macro.xlsm)
        :param module_name: Found inside 'Modules' of macros within that workbook
        :param macro_name: The 'sub name_here()' means macro is called 'name_here'
        :return: Nothing. Executes the Macro.
        """
        # script taken from: https://stackoverflow.com/questions/19616205/running-an-excel-macro-via-python
        # DispatchEx is required in the newest versions of Python.
        excel_macro = wincl.DispatchEx("Excel.application")
        workbook = excel_macro.Workbooks.Open(Filename=filepath_incl_filename, ReadOnly=1)
        excel_macro.Application.Run(f"{filename}!{module_name}.{macro_name}")
    
        # Save the results in case you have generated data
        workbook.Save()
        excel_macro.Application.Quit()
        del excel_macro

Code2

run_excel_macro(
    filepath_incl_filename=r'',
    filename='',
    module_name='',
    macro_name=''
    )