Run an Excel macro on any file using drag and drop

Create an Excel macro, put that macro in a .BAS file and then use this script to apply that macro to any Excel file.


Here is the VBS file, written in WSCRIPT:
if WScript.Arguments.Count < 1 Then    WScript.Echo "Drop file on to me to process."    Wscript.QuitEnd If
MyPath = Left(Wscript.ScriptFullName, Len(Wscript.ScriptFullName) - Len(Wscript.ScriptName))Set oExcel = CreateObject("Excel.Application")Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))Set oVBC = oBook.VBProject.VBComponents oVBC.Import(MyPath & "\My Macro.bas") oBook.Application.Run "Macro1" WScript.Echo "Done"
To make this work, you need to configure Excel properly:

Excel -> Options -> Trust Center -> Trust Center options -> Macro Settings
TURN ON Trust access to the VBA project object model

Post a Comment

Popular posts from this blog

How to connect your Roku to Xfinitywifi

I Was Kidnapped in Manila and Lived to Tell About it

Excel bug/feature: recursive VLOOKUP for hierarchy calculations