Automating through VBS to run VBA Script

[Ananthak275]

Hello everyone,

I'm trying to run this VBS script. It should go into the file "Hello.xlsm", Sheet1(in the example, it was module1, i assumed this was referring to the sheet name so i changed it to Sheet1), and "sched" is the name of the macro. it runs but dont show up anything.

set objexcel = CreateObject("Excel.Application")
objExcel.Application.Run "'C:\Hello.xlsm'!Sheet1.sched"
objExcel.DisplayAlerts = False
objExcel.Application.Quit

The macro within "Hello.xlsm"

Sub sched()
MsgBox "hello hello hi hi hi !!!!!"
End Sub

Very simple macro. Not sure what the error is

 

[Zot]

Syntax should be like this to run from another workbook
Application.Run "'Another Workbook.xlsm'!NameOfMacro"

 

[johnnyL]

Syntax should be like this to run from another workbook
Application.Run "'Another Workbook.xlsm'!NameOfMacro"

I think the code is fine, I think the location of the module may be the issue, assuming that this vbs code is trying to be run outside of excel.

 

[Ananthak275]

Syntax should be like this to run from another workbook
Application.Run "'Another Workbook.xlsm'!NameOfMacro"

So i got the code to work (see below). But im having a few problems, the file gets locked after if i re run it, do you know why?

set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'C:\Users\Documents\Test data\code..xlsm'!sched"
objExcel.Application.Run "'C:\Users\Documents\Test data\code4.xlsm'!sched1"
objExcel.Visible=True

objExcel.DisplayAlerts = False
objExcel.Application.Quit

 

[Zot]

So i got the code to work (see below). But im having a few problems, the file gets locked after if i re run it, do you know why?

I have created a workbook named code.xlsm with Hello macro you provided above

I run the code below from another workbook again and again with no problem.
Anyway, I noted that you have double dot in workbook name which I remove one code..xlsm. I also remark out the other sched1 macro

Sub Test()

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'C:\Users\Documents\Test data\code.xlsm'!sched"
'objExcel.Application.Run "'C:\Users\Documents\Test data\code4.xlsm'!sched1"
objExcel.Visible = True

objExcel.DisplayAlerts = False
objExcel.Application.Quit

End Sub

So, I have no idea why you are having problem

 

[johnnyL]

So i got the code to work (see below). But im having a few problems, the file gets locked after if i re run it, do you know why?

try adding the following below your last line:

Set objExcel = Nothing