-
Mar 27th, 2012, 04:07 PM
#1
Thread Starter
New Member
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
-
Mar 28th, 2012, 04:17 PM
#2
Thread Starter
New Member
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?
-
Mar 30th, 2012, 04:17 PM
#3
Addicted Member
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
-
Apr 3rd, 2012, 04:06 PM
#4
Thread Starter
New Member
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.
-
Apr 4th, 2012, 08:37 AM
#5
Addicted Member
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:
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.
-
Oct 30th, 2014, 12:19 AM
#6
Registered User
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 .
Thanks in advance
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|