Convert VBA (.bas) to VBS or EXE

[DOMTRUMP]

I've been working with VBA for a while now. How can I take my VBA code and convert it into something that I can execute from Windows Scheduler? The only export options for my module are to .bas which does not execute on its own. How can I make it into a .vbs or .exe file so that I can schedule it to run overnight? Is there a way to just launch Excel with some kind of parameters on the command line to execute the macro? If so, what is the syntax? Some one else told me that I have to "compile" the vba code, but I am not sure how to do that.

Thanks for the help!

[shg]

You can have the code triggered in the Workbook_Open event. Sign the VBA project so that it will run without prompting to enable macros, and just schedule the workbook to open as a Scheduled Task.

[Leith Ross]

Hello DOMTRUMP,

Shg's suggestion is the best way to go with this. You can setup and run a task from VBA using various scripting methods like VBS, WSH, and WMI or even the API. The big problem is with the Windows OS. Quote from "Hey,Scripting Guy! Blog"

The problem we have here is that Windows actually has two different - and, sadly, not fully-compatible - APIs (Application Programming Interfaces) used to manage scheduled tasks. For one, there are the Task Scheduler APIs used by both the Task Scheduler and by Schtasks.exe, a command-line task management tool that ships with Windows XP and Windows Server 2003. For another, there are the so-called At APIs used by At.exe and by WMI’s Win32_ScheduledJob class.

So what’s the problem? Well, let’s say you use WMI to create a scheduled task. If you do that, you can then use a script similar to this to retrieve information about that task and about any other tasks created using the At APIs:

[DOMTRUMP]

OK, so if I just put a .xls file in windows scheduler, it will open Excel and launch the file? Or do I have to put excel.exe and some kind of switches on the command line before the file name? If I just select "Excel" from the task scheduler wizard, it puts the following on the command line:
""C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office\Microsoft Office Excel 2007.lnk""
How do I modify this so that it opens "FileB.xls" so the Workbook_Open event can run?

Thanks.

[shg]

Control Panel > Scheduled Tasks > Add Scheduled Task, browse to the workbook, follow the wizard from there.

[DOMTRUMP]

OK, I think this will work fine. However, I pasted my vba code into a Workbook_open event but now I cannot go back and edit my workbook! I had a "ActiveWorkbook.Close" at the end of my code and now the workbook closes right away every time I open it. I need to go in and comment that out, but holding down SHIFT while I open the file does not stop the macro from running as it did in previous versions of Excel (I'm using 2007 - though the file is a .xls 97-2007 format file). Can you please tell me the trick to open this file without executing the Workbook_Open event? Thanks for your help.

[shg]

Developer tab, tick Disable all macros without notification, then open the file.

[DOMTRUMP]

Hmmm. well that didn't work either. The Workbook_Open event still executed. But you did point me in the right direction. On the Developer tab, I clicked on Macro Security. In the Trust Center window, I clicked on the Trusted Locations button. At the very bottom I checked off "Disable all trusted locations." Now I was able to open the file without the execution of the start-up macro, change my code appropriately and go back and re-enable my trusted locations.

Still don't understand why the SHIFT-Open functionality was removed in 2007. Anyway, thanks for all of your help and prompt responses.

Cheers!