Solved: Run VBA from imported module

[mvidas]

Hi All,

I'm trying to import a vba module at runtime, and run the sub "Main" (no arguments) in that module.

I'm a noob with access, can someone please explain what I'm missing?
With VBE.ActiveVBProject.VBComponents.Import("C:\foldername\somename.bas")
Application.Run .Name & ".Main"
VBE.ActiveVBProject.VBComponents.Remove VBE.ActiveVBProject.VBComponents(.Name)
End WithIf I remove the ".Name & " part of application.run, it works (as long as only one of the existing modules has a 'main' sub), but I can't be sure that is the case every time. I can't change the subroutine names, so I've got to stick with Main. The above logic works in excel, but not access.

Any help?
Thanks!
Matt

[Norie]

Matt

What is .Name actually referring to?

[mvidas]

The .Name is referring to the module name of the newly imported module. So if it were Module31, it would be the same as runningApplication.Run "Module31.Main"Which is where my problem with access VBA seems to be (since that doesnt work like I think it should)

[Norie]

Matt

I can't even get this to work after importing the module.

Application.Run "Module3.Main"

I'm sure it's something simple that's missing but I can't think what.

I'm just off now but I'll have another go later.

By the way what are you actually trying to do?

Could there be another approach?

[mvidas]

I can't figure out why it isn't working either, I'm at the same point you are.

If you can think of a better way, I'd love to hear it, here's the backstory. This isn't for me, just trying to help someone out.
There are a number of .bas files received, each with a "Main" subroutine that has no arguments.
The .bas file to import depends on some query results, and unfortunately there are too many to hardcode or to have completely imported into the db file (plus, new ones are received regularly).
I thought about using scriptcontrol, but as that uses vbscript instead of vb it wouldn't work.
Honestly, I don't really know much about the whole thing, the whole thing seems kinda stupid to me. But I'm trying to help nonetheless.

Do you know of another way in access to call a subroutine when the module name is stored in a variable?
Excel and Word use the same (logical) way, like Application.Run "Module2.Main"access is just being stubborn

[Tommy]

Matt,
Just a try this kinda thing, have you tried CallByName?

[mvidas]

Interesting idea! I can't say I've ever used CallByName before, or if I had I was unaware of it's usage.
I tried commenting out the application.run line and instead used: CallByName VBE.ActiveVBProject.VBComponents(.Name), "Main", VbMethodBut unfortunately it didn't work. I think that would work for class modules though, since the functions there are technically methods.
Good idea nonetheless !

[Tommy]

Well this makes no sense :( as you can see below this works.

in module1

Sub Main()
VBE.ActiveVBProject.VBComponents.Import ("C:\Documents and Settings\Administrator\My Documents\fred.bas")
DoTheDo
VBE.ActiveVBProject.VBComponents.Remove VBE.ActiveVBProject.VBComponents("module2")
End Sub
Sub DoTheDo()
Module2.Main
End Sub

in module2

Sub Main()
MsgBox "Hi Ya!!"
End Sub

[mvidas]

I agree, it makes no sense! If I could be sure what the module name was it would be great.
As it turns out, we're just going to use 'Application.Run "Main"' for now, though I don't like it. I'll probably add something to the routine that looks at all the vbcomponents for a procedure named "Main", and remove the component if it does exist. I'm still going to be playing around with it today, so I'll leave it as unsolved for now in case someone gets really creative and really lucky with it!
Silly MS, can't even get their .Run methods to work the same across all of office

[Tommy]

If I could be sure what the module name was it would be great.

Thats the thing. It seems to reference the module as module?? (in this case module2) once it is imported. It doesn't seem to take the .bas name. It also doesn't seem to reconize the procedures in the imported bas file until the code leaves the importing function/sub (that is the reason for the other sub) So the point I am trying to make is if your list of modules has 8 bas files, none with the default naming convention the imported bas file would be referenced as module1.Main if they have the default naming convention the call would be module9.Main :dunno

[mvidas]

Exactly.. and though in the .bas files the first line is something like:
Attribute VB_Name = "Module9"

I could just read that to get the module name but it wouldn't make much difference. Also if there is already a module9 that exists, it would be imported as Module91 or Module92, etc.

Stupid access
Since my friend is happy the way it is, I'm happy for now, but I know I'll be revisiting it soon enough

[Tommy]

Then it doesn't see the newly imported file module91 because of the conflict
The only other way I can see to do it is to read the file itself and write to the code pane ........ which opens another can of worms LOL.

If I find a way I'll post it otherwise I'm out for now. :(

[mvidas]

Another way (though compiling would be a nightmare) would be to have 100 subs likeSub Module1Main()
Module1.Main
End Sub
Sub Module2Main()
Module2.Main
End Sub
Sub Module3Main()
'etc
End SubWhich would allow the application.run to work, but thats just overkill :)
Oh well, I'll just wait until I hear a complaint about just "main", then go from there.
I'm going to mark this as solved as I'm sure it's a silly limitation of access
Thanks again