How to Call a MS Access function from MS Excel (1 Viewer)

aziz rasul

Active member
Local time
Today, 22:03
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?
 

MarkK

bit cruncher
Local time
Today, 15:03
Joined
Mar 17, 2004
Messages
8,178
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.
 

aziz rasul

Active member
Local time
Today, 22:03
Joined
Jun 26, 2000
Messages
1,935
If I have a function called KPIs in a module, what would the code look like? Would it be something like

acc.Run "KPIs"
 

MarkK

bit cruncher
Local time
Today, 15:03
Joined
Mar 17, 2004
Messages
8,178
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
 

aziz rasul

Active member
Local time
Today, 22:03
Joined
Jun 26, 2000
Messages
1,935
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?
 

MarkK

bit cruncher
Local time
Today, 15:03
Joined
Mar 17, 2004
Messages
8,178
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.
 

smig

Registered User.
Local time
Tomorrow, 00:03
Joined
Nov 25, 2009
Messages
2,209
why won't you copy the module to the Excel file ?
 

aziz rasul

Active member
Local time
Today, 22:03
Joined
Jun 26, 2000
Messages
1,935
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?
 

aziz rasul

Active member
Local time
Today, 22:03
Joined
Jun 26, 2000
Messages
1,935
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.
 

MarkK

bit cruncher
Local time
Today, 15:03
Joined
Mar 17, 2004
Messages
8,178
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

Registered User.
Local time
Tomorrow, 00:03
Joined
Nov 25, 2009
Messages
2,209
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 ?
 

aziz rasul

Active member
Local time
Today, 22:03
Joined
Jun 26, 2000
Messages
1,935
I need to look into that. Thanks for the suggestion.
 

Users who are viewing this thread

Top Bottom