Run external .bas modules in VBA

Hello Experts,

What I need to do is run the Main routine of a .bas module stored in file x:\path\somename.bas

I have a program that creates data conversion routines for me in vbasic and saves them as .bas files.  There are hundreds of them.

Each one is a stand-alone .bas module and the primary routine name to be executed is Main with no parameters.

If I import the routine into my VBA (Access), I have no problem running it, but my goal is to automate the running of selected ones of them driven by a query from an Access table.  Doing these by hand would take a very long time and would be error prone.

My thought is to use the Access VBA to drive which routines run and in what order (via a query which supplies the filename).  The routine would invoke each of the external routines, one after the other until the list was complete.

Using Shell & OpenProcess so I could monitor the progress would be ideal, but I'm afraid I have no idea what the parms would be to Shell.


Who is Participating?
g and Ron,

This is indeed puzzling. The VBA VBE allows you to import the module programmatically, and you can run a macro using Application.Run.  However, in case multiple modules existed that had "Main" subroutines, the Application.Run would need to have the modules specified.  In Word and Excel VBA, you can do it like:
 Application.Run "Module1.Main"

However, access vba doesn't seem to allow the module name specification.  I'm still playing around trying to figure something out, the following will work in other VBAs just not access:

 With VBE.ActiveVBProject.VBComponents.Import("C:\foldername\somename.bas")
  Application.Run .Name & ".Main"
  VBE.ActiveVBProject.VBComponents.Remove VBE.ActiveVBProject.VBComponents(.Name)
 End With

By the way, this will work:
 Application.Run "Main"

But only if one "Main" sub exists in the project.  Theres gotta be something I'm missing.  I'm still trying it out, but I figured I'd post that in case someone can think of a way to get access to allow it.

Hi there,

This is quite an issue. As far as I understand, you have some options here:

A. Use some kind of a VBA container, like Excel or Access. Use the container's IDE to import the file, run the Sub, and then remove it. All this in a script (macro) that the IDE is running, which uses the IDE's API to do some stuff. I'm not sure if it's possible, but anyway it's a thought. I think that you can get a lot if you program a COM Add-In for Access/Office.

B. Create a wrapping program that:
1. Gets the list of files
2. Copies the files with a vbs extension
3. Executes the vbs files as a script (in WSH)

Although option B seems to be better, it might be irrelevant in the first place. This is because VBScript doesn't support types that VB/A does, and the result is a useless code. Converting the bas modules into a valid VBS code can be a real pain, if possible at all.

C. If Visual Basic 6 is installed on the very same machine that runs the whole thing, it is possible to programmatically create a project for each bas file, and compile it using the command line options in the (namely, using vb6.exe). When you're done with the compilation, just execute the compiled exe file using Shell.
It's somewhat overkill, but it seems reasonable if the job is done in long intervals (once a day, once a week and so on).


OuttaCyTEAuthor Commented:

You gave me the clue that I needed:

    Dim myVBComponent As Object
    Set myVBComponent = VBE.ActiveVBProject.VBComponents.Import("X:\Path\Filename.bas")
    Application.Run "Main"
    VBE.ActiveVBProject.VBComponents.Remove myVBComponent

This loads the module and executes the Main subroutine and then unloads it.  I then loop and do the next one.
I'm glad it can work like that for you (you don't actually need the myVBComponent variable if you use the With block, but it is up to you).  Just know that if for some reason your db has a module in it with a "Main" sub, it will error out!

If you'd like I can give you some code that will look through all existing components for a "Main" procedure, and remove the component if it exists. I am still playing around looking for a way to do it in access, seems like a big oversight on MS's part.

OuttaCyTEAuthor Commented:

Thanks for the offer, but I got it running last night and already did a full load with it.  It took next to no time to do all of the loads.

This will never be "production-ized" as it is simply a tool to speed the loads that I do on a custom basis.  Each load will be different (conversions from different back ends) and need different types of loads to complete.  This way I can put all of my learning/finished update routines in a table as I work through the various conversion issues and run repeatedly without having to do it all by hand.

Thanks again Matt & everyone here at EE!!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.