Running a macro from Access in excel and then refreshing external data in excel
 

Hello All

I am trying to figure out how to create a macro in Excel to run a macro
named "macro 1" in access (which is a combination of a delete query and
an append query). This will bring up screens that ask questions such
as "are you sure you want to delete xxx records'". They are typically
yes, no or cancel questions. I need to have the macro i create in
excel, run the macro in access, click yes to all of the questions that
come up and then refresh the external datalink in the excel workbook.

Can anyone help point me int he right direction to get something like
this done

Thanks

Cory

Running a macro from Access in excel and then refreshing external
 

OK, couple things.

1) In your Macro in Access, you should do this with your code:

DoCmd.SetWarnings False

' Code to run queries here

DoCmd.SetWarnings True

That will suppress the system generated messages like "Are you sure you want
to do this?" and the like. I don't know what your setup is like, but you may
need to also put in code to do certain things if Access runs into problems
with the queires - because it won't tell you about THOSE either.

2) It's been years since I've remotely run Access macros. I know it can be
done. I think the code is something like:

Dim objAccess As Object, objDB As Object

Set objAccess = CreateObject("Access.Application")
Set objDB = objAccess.Databases.Open("C:\Path\To\Your\database .mdb")
objDB.RunMacro "Macro1"
objDB.Close
Set objDB = Nothing
objAccess.Quit
Set objAccess = Nothing

That's untested and may not be exactly right, but it should get you in the
ballpark.

HTH
--
Hmm...they have the Internet on COMPUTERS now!


" wrote:

Hello All

I am trying to figure out how to create a macro in Excel to run a macro
named "macro 1" in access (which is a combination of a delete query and
an append query). This will bring up screens that ask questions such
as "are you sure you want to delete xxx records'". They are typically
yes, no or cancel questions. I need to have the macro i create in
excel, run the macro in access, click yes to all of the questions that
come up and then refresh the external datalink in the excel workbook.

Can anyone help point me int he right direction to get something like
this done

Thanks

Cory