VBScript To Run Excel Macro

Question

I am attempting to run this script

if WScript.Arguments.Count < 1 Then   
WScript.Echo "Drop file on to me to process."   
Wscript.QuitEnd If
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
Set oVBC = oBook.VBProject.VBComponents oVBC.Import("C:\Test\Test.bas") 
oBook.Application.Run "Test" 
WScript.Echo "Done"

The name of the macro inside of the Test.bas file is Test() and the file is located at C:\Test\Test.bas - now anytime I try to drag a .xlsx file on top of the .vbscript I get an error that reads

Syntax error
800A03EA
Microsoft VBScript Compilation error

What needs to be altered in this script to remedy that issue?

dwirony · Accepted Answer · 2017-06-14 19:05:57Z

1

Wscript.QuitEnd If Needs to be fixed to

Wscript.Quit End If

Set oVBC = oBook.VBProject.VBComponents oVBC.Import("C:\Test\Test.bas") to

Set oVBC = oBook.VBProject.VBComponents oVBC.Import("C:\Test\Test.bas")

edited Jun 14, 2017 at 19:05

answered Jun 14, 2017 at 18:57

dwirony

5,4604 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

BellHopByDayAmetuerCoderByNigh Over a year ago

Changing that syntax gives me an error on line 7 - Expected End Of statement.

dwirony Over a year ago

Set oVBC = oBook.VBProject.VBComponents oVBC.Import("C:\Test\Test.bas") Try bringing oVBC.Import("C:\Test\Test.bas") down a line.

BellHopByDayAmetuerCoderByNigh Over a year ago

Yes, that got it! It produces the file as needed but I get an Unknown Runtime Error as well?

BellHopByDayAmetuerCoderByNigh Over a year ago

-- would prefer to avoid just a random on error resume next if possible

BellHopByDayAmetuerCoderByNigh Over a year ago

It does not show me the line. It seems the script is run to completion as the file is generated just as I need. I don't understand the error, haha

|

Collectives™ on Stack Overflow

VBScript To Run Excel Macro

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related