Run external .bas modules in VBA

Posted on 2006-05-09
Last Modified: 2008-02-01
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.


Question by:OuttaCyTE
    LVL 1

    Expert Comment

    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).


    LVL 35

    Accepted Solution

    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.


    Author Comment


    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.
    LVL 35

    Expert Comment

    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.


    Author Comment


    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!!


    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now