Run Excel Macro through VBS

[ivanetter]

Ihave created a VBS script to open an Excel file. I need the script to run 2 macros that I have saved, one formats the worksheet and the other saves the file as a .csv file.

I have the following script, but I am getting a runtime error on line 8:

Dim objExcel
Dim objWorkbook

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
set objWorkbook = objExcel.Workbooks.Open("C:\Users\Desktop\SRI\SRI_Import.xlsx")

objExcel.run "'PERSONAL.XLSB'!SRIStudent"
objExcel.Ontime Now, "PERSONAL.XLSB!SRIStudentSave"
objWorkbook.Close true
objExcel.Quit
Set objWorkbook = Nothing
Set objExcel = Nothing

[ivanetter]

I have had no attempts of replies, so should I consider this request as impossible? Or am I being impatient?

[His Nibbs]

This works for me.

Run_Excel_Sub.vbs

Dim objExcel, folder

folder = Left(WScript.ScriptFullName,(Len(WScript.ScriptFullName))-(Len(WScript.ScriptName)))

Set objExcel = CreateObject("Excel.Application")
With objExcel
  .Workbooks.Open (folder & "\Excel_Workbook.xls")
  .Visible = True
  .Run "TheSub"
  .ActiveWorkbook.Close(True)
  .Quit
End With

Module1 in Excel_Workbook.xls (in the same folder as the .vbs file)

Public Sub TheSub()
    MsgBox Now & vbNewLine & ActiveWorkbook.FullName
End Sub

[ivanetter]

Thank you for the response. I am, however a bit confused .

I am using a batch file that is set to open a database. When opened, this database automatically runs the needed queries and save the tables as an Excel file (one for student and one for teacher). After the Excel files are saved, the next line is to open the student Excel sheet. I have 2 macros created to format the worksheet and one to save the formatted worksheet as a .csv file.

The VB script that I originally uploaded is supposed to open the student worksheet and run the 2 macros (SRIStudent and SRIStudentSave).

I need the script to open the Excel worksheet and run the 2 macros, but it needs to be called from the batch file.

I am not sure how and where I would use the coding from the answer above.

[His Nibbs]

I need the script to open the Excel worksheet and run the 2 macros, but it needs to be called from the batch file.

I am not sure how and where I would use the coding from the answer above.

In the batch file, e.g. batch.bat:

c:\path\to\folder\Run_Excel_Sub.vbs

To call a second Excel procedure, just call the Run method again:

.Run "Proc2"

where Proc2 is defined similar to TheSub as shown previously.

My code is an example to show how to run an Excel procedure (macro) from VBScript. It isn't meant to implement your exact situation. I suggest you get my example working exactly as shown and then try to adapt it for your situation.