Executing an Access Macro from Excel 2007

[vik01]

Hi

Is it possible to execute a Macro on an Access Database from Excel 2007?

If so, could someone advise me on how to do this?

Thanks in advance for your help.

Vik

ps. I don't know any VB Script, so please dumb down your response, if possible!

 

[GohDiamond]

What is that macro supposed to do? Cuz if your just trying to get data out of Access then you can use MicrosoftQuery from Excel with the Access Dataabase as your source.

I think we need more information...
Cheers!
Goh

 

[vik01]

What is that macro supposed to do? Cuz if your just trying to get data out of Access then you can use MicrosoftQuery from Excel with the Access Dataabase as your source.

I think we need more information...
Cheers!
Goh

Hi Goh

Thanks for your reply.

The macro in access is used to append data from an excel spreadsheet into a table in my access database. In other words the data in excel is going to be the data source, but I don't want to have to go into access every time.

I know it would be easier to eliminate excel from the equation and do it all on access, but I'd rather stick with excel.

I hope that makes sense. Any other suggestions?

Thanks

Vik

 

[GohDiamond]

Have a gander at this one:
http://support.microsoft.com/kb/131921

After you give it a go, let us know if you need anything else or share your results with us.

Cheers!
Goh

 

[vik01]

Have a gander at this one:


After you give it a go, let us know if you need anything else or share your results with us.

Cheers!
Goh

Hi Goh

I've tried this one before. I get the following error message when I run this:

______________________
Run-time error '5':
Invalid procedure call or argument
______________________

Any other ideas?

Thanks

Vik

 

[vik01]

Hi,

I have managed to steal pieces of code from here and there and resolved this. I used this script on VB:

Sub Run_Macro

Dim ac As Object
On Error Resume Next
Set ac = GetObject(, "Access.Application")
If ac Is Nothing Then
Set ac = GetObject("", "Access.Application")
ac.OpenCurrentDatabase "pathway of DB file"
ac.UserControl = True
End If
AppActivate "Microsoft Access"
ac.DoCmd.runmacro "name of macro"
ac.Quit

End Sub