Run Excel Macro through VBS-VBForums

# Thread: Run Excel Macro through VBS

1. ## Run Excel Macro through VBS

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

2. ## Re: Run Excel Macro through VBS

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

3. ## Re: Run Excel Macro through VBS

This works for me.

Run_Excel_Sub.vbs
Code:
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)
Code:
Public Sub TheSub()
MsgBox Now & vbNewLine & ActiveWorkbook.FullName
End Sub

4. ## Re: Run Excel Macro through VBS

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.

5. ## Re: Run Excel Macro through VBS

Originally Posted by ivanetter
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:
Code:
c:\path\to\folder\Run_Excel_Sub.vbs
To call a second Excel procedure, just call the Run method again:
Code:
.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.

6. ## Re: Run Excel Macro through VBS

Hai all, Im new to the Forum. From the above code we can only run a macro only if vbs and XLS are in same folder. How to run a macro of diffrent location from vbs file .

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Featured