Help with DoCmd in MS Access

[boreal1]

I have a basic script that opens an Access database and clicks some buttons on a form to run macros and I want to enhance the script so that if the database form design changes my script will still work.

Basically, I need to send the DoCmd.RunMacro command and I'm not sure how to do that. I wrapped it in a VB function in Macro Scheduler, but it doesn't work.

Any help would be greatly appreciated.

[Marcus Tettmar]

The easiest way to run an Access macro from Macro Scheduler is just to do this:

Run>"c:\pathtoaccess\msaccess.exe" "c:\pathtodb\yourdb.mdb" /x macro_name


That will run Access, open the MDB and immediately run the macro.

If you want to do more you would need to use VBScript and create a reference to the access object. E.g.:

Code: Select all

VBSTART
Sub RunMacro
  Dim oDB
  Dim dpPath
  dbPath = "c:\data\mydb.mdb"
  Set oDB = GetObject(dbPath)
  oDB.DoCmd.RunMacro "Customers"
  Set oDB = Nothing
End Sub
VBEND

VBRun>RunMacro

[boreal1]

Great! That worked well for my Access problem, thank-you.

However, within the same script, I need to open an excel file, run an excel macro, and save/close the xls. Using both methods you presented above, this is not working for me.

For instance, if I use the line:
Run>"C:\Program Files\Microsoft Office\OFFICE11\excel.exe" "\\home\boreal1\myxls.xls" /x "Simply_Save"

Excel opens and it runs an auto-exec macro to refresh some pivots tables and then I get the error message:
"myxls.xls is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen myxls.xls?"

When try to run the macro from Macro Scheduler using the following vb script:
Sub RunMacroXLS
Dim oDB
Dim xlPath
xlPath = "\\home\boreal1\myxls.xls"
Set oDB = GetObject(xlPath)
oDB.Application.Run "Simply_Save"
Set oDB = Nothing
End Sub

I get the message: Microsoft Office Excel: 1004 The macro 'Simply_Save' cannot be found.

The macro is definitely there.

What could be causing this behaviour?

Any help would be greatly appreciated.

[Marcus Tettmar]

I get the same problem when trying to use GetObject but the following works with CreateObject:

Code: Select all

VBSTART
Sub RunMacroXLS
  Dim xlApp
  Dim xlPath
  Dim xlBook
  xlPath = "d:\test.xls"
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = true
  Set xlBook=xlApp.Workbooks.Open(xlPath)
  xlApp.Application.Run "MyMacro"
  'Set XL = Nothing
End Sub
VBEND

VBRun>RunMacroXLS