run a macro on particular cell selection using vbs

Question

I need to write a vbs that selects a cell and runs a macro on that cell here's the code I found online(that doesnt work)

ActiveSheet.Cells(5, 3).Select
xl.Run "'Book1.xls'!Calc"

I am creating this vbs in Eclips(Running this VBS through

Runtime.getRuntime().exec("cmd /c RUN_MACRO.vbs "+xlsFile+" "+ macroName);  

Still after running I get no error and no change in the excel as well. Please help

Full Code for VBS for RUN_MACRO.vbs Here Calc is the Macro name saved in some Module(as I created this macro(to multiply A2*B2) in Open Office and when I select a cell and run a macro,I the cell value gets changed)

Set xl = CreateObject(WScript.Arguments(0))
Set wb = xl.Workbooks.Open(WScript.Arguments(1))
ActiveSheet.Cells(5, 3).Select
Range("C5").Select
xl.Run "'Book1.xls'!Calc"
ActiveWorkbook.Save
wb.Close
xl.Quit

Method calling this vbs in my java file

public static void executeMacros(String xlsFile, String macroName){

          try{  
              Runtime.getRuntime().exec("cmd /c RUN_MACRO.vbs "+xlsFile+" "+ macroName);  

      }catch(Exception e)  
      { System.out.println(e);}  
     }

Answer 1

There are a few oddities in your VBS file. The first is that you are trying to create an object from an Excel file instead of from a Class. So the first thing that you need to do is Set xl = CreateObject("Excel.Application").

Because you are doing Set wb = xl.Workbooks.Open(WScript.Arguments(1)) and your second argument is the macro name the script is trying to open a workbook named "macroname".

You are also trying to select two different cells before actually executing the macro. So ActiveSheet.Cells(5, 3).Select doesn't seem necessary and wouldn't really work anyways.

The xl.Run "'Book1.xls'!Calc" actually needs to be in the format of xl.Run "ModuleName.MacroName"

You may need to use this code to update other cells. Therefore you may be interested in adding a third argument to the cell that actually needs to be updated.

Thus try this as your vbs:

WorkBookToOpen = WScript.Arguments(0)
MacroToRun = WScript.Arguments(1)
CellToUpdate = WScript.Arguments(2)

Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(WorkBookToOpen)

xl.Range( CellToUpdate ).Select
xl.Run "Module1."  & MacroToRun
xl.ActiveWorkbook.Save
wb.Close
xl.Quit

And then change to:

Runtime.getRuntime().exec("cmd /c RUN_MACRO.vbs "+xlsFile+" "+ macroName + " " + cellToUpdate);