PDA

View Full Version : Trying to call access functions in excel



DKY
05-19-2008, 12:58 PM
I have an access database on one of our network drives that has some functions in it. One of the functions looks at data and manipulates it a certain way and I would like to be able to call into my excel files.

The creator of this program constantly uses it in access and is always updating the program. If I were to just simply copy and paste every once in a while I would miss any edits that he makes to the program. Another reason that I want to link to it is because many of us use this program and it would be great if we can just draw from one source.

So far I've got the following:



Sub Run_Func()
Dim obj As Access.Application
Set obj = CreateObject("Access.Application")
With obj
.OpenCurrentDatabase "\\location\to\my\database.mdb"
.Visible = True
.Run "macro1"
End With
obj.Quit
Set obj = Nothing
End Sub

This will run 'macro1' in database.mdb but I can't figure out how to run a function instead. HELP?!?! I'm almost there.....

Oorang
06-18-2008, 09:01 AM
Hi DKY,
Welcome to the board :) Access has a command switch ("/x") that will run the access macro (not vba) that you specify on startup. You can take advantage of that by placing a macro in the access database (for example lets call it "PublicMacro"). In PublicMacro use the RunCommand method to call the VBA procedure in question. For the procedure to be callable from an Access Macro it will need to be a Public Function, in a non-private module (if you don't Option Private Module then you're good). After RunCommand put a line for Quit then save the macro.

Once all that is set up you can just call it like so:
Shell "msaccess ""C:\Test.mdb"" /x ""PublicMacro"""


Note: One problem you might run into is security prompts when calling the database. You can supress these by creating a certificate using "Digital Certificate for VBA Projects", signing the database and then have your users trust the certificate.

DKY
06-18-2008, 09:26 AM
Just so I understand.
I set up the Access macro as a Public Function and then I go into Excel and put the line of code that you posted?

Oorang
06-18-2008, 09:31 AM
First ensure the Main Program is a Public Function in a Public Module.
If it is not just make a public function (in a public module) that call the main program. (This is often called a "wrapper")
Next set up an Access-Macro that calls either the Main Program or it's wrapper, then quits.
Finally call the Access-Macro via shell from Excel.