Results 1 to 6 of 6

Thread: Run Excel Macro through VBS

Hybrid View

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2012
    Posts
    3

    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. #2

    Thread Starter
    New Member
    Join Date
    Mar 2012
    Posts
    3

    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. #3
    Addicted Member
    Join Date
    Jul 2009
    Posts
    208

    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. #4

    Thread Starter
    New Member
    Join Date
    Mar 2012
    Posts
    3

    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. #5
    Addicted Member
    Join Date
    Jul 2009
    Posts
    208

    Re: Run Excel Macro through VBS

    Quote Originally Posted by ivanetter View Post
    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. #6
    Registered User
    Join Date
    Oct 2014
    Posts
    1

    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
  •  



Click Here to Expand Forum to Full Width