How to Call a MS Access function from MS Excel

aziz rasul

Active member
Local time
Today, 18:11
Joined
Jun 26, 2000
Messages
1,935
I have an MS Excel file from which when running a macro I want to invoke an MS Access function. Does anyone know how to go about doing this?
 
Late bound and early bound examples ....
Code:
[COLOR="Green"]   'late bound[/COLOR]
   Dim obj As Object
   Set obj = CreateObject("Access.Application")
[COLOR="Green"]   'early bound - requires a reference to Access[/COLOR]
   Dim acc As new Access.Application
Make sure you run the Access.Application.Quit when you are done to ensure you don't leave multiple invisible instances running.
 
If I have a function called KPIs in a module, what would the code look like? Would it be something like

acc.Run "KPIs"
 
Oh, you mean a user defined function in an Access module. Yeah, that should work. Have you tried it?
The first Parameter is the name of the routine, and then if that routine takes parameters you can pass those in too...
Code:
dim returnvalue as variant
returnvalue = acc.Run("YourRoutineName", YourParam1, YourParam2)
...passes two parameters to a function called "YourRoutineName"
Cheers,
Mark
 
Your code worked, many thanks.

The problem I'm having is that I don't want the opening dialog box regarding Enabling Macros to open, which it does. Is there a way around this?
 
Do you get that same warning if you open the Access file normally? Maybe you need to go to the OfficeButton->AccessOptions->TrustCenter->TrustCenterSettings->MacroSettings and Enable All Macros.
 
why won't you copy the module to the Excel file ?
 
I'm using Office 2003. I simply set the security level to low in the Access db to get around the issue. There must be a way of enabling the macros at whatever setting it is set using VBA?
 
smig, the data is in the db so hence I want to run some code from the db module and return the revised data back into Excel.
 
Code:
There must be a way of enabling the macros at whatever setting it is set using VBA?
I doubt that. If you could override this kind of security programmatically it wouldn't be security. It exists to limit code execution.
 
smig, the data is in the db so hence I want to run some code from the db module and return the revised data back into Excel.

can't you link to the access db and still run the code from Excel ?
 

Users who are viewing this thread

Back
Top Bottom