4

I'm looking to schedule a call to an MS Access macro. This macro uses a .csv file (that I get daily) to update a SharePoint calendar. Could someone please explain how to schedule a daily call to run this macro (I'm using Access 2003, say EventsCalendar.accdb and macro called Run1) on my Windows 7 machine? I need to automate it to run every morning.

Thanks!

Dmitri Boulanov
  • 87
  • 2
  • 10
  • Try naming your macro 'AutoExec' and that will make the macro run each time Access is opened. Then with Windows, just use the task scheduler to open that Access file each time you log in, or at 8 am, etc. – Grant Feb 12 '13 at 00:18
  • If you don't want your macro to run *every* time, name it something other than `AutoExec`. Then you can pass the macro name after the `/x` switch: `"Path to MSACCESS.EXE" "Path to your db file" /x YourMacroName` You can put that in a batch or VBScript file, or set it up that way directly in the Windows task scheduler. Also see http://stackoverflow.com/q/7676495/77335 – HansUp Feb 12 '13 at 05:50

1 Answers1

1

Write a VBScript script that can run the macro straight from Windows. You can do this by first creating an Access application object in your script and then placing this Access object before all calls in the script which need it. For example,

' RunMyMacro.vbs

set accessApp = CreateObject("Access.Application")

accessApp.OpenCurrentDatabase "C:\db\mydb.accdb"
accessApp.DoCmd.RunMacro "MyMacroName"
accessApp.CloseCurrentDatabase
accessApp.Quit

set accessApp = nothing

This script is now runnable from the Windows shell or from the command line:

C:\db>cscript //B //Nologo  RunMyMacro.vbs

And so you can schedule it as a Windows scheduled task.

Yawar
  • 11,272
  • 4
  • 48
  • 80
  • @DmitriB, glad to help. I just took a deep dive into Access development so it's all fresh in my mind. – Yawar Feb 13 '13 at 00:13