{"id":2712,"date":"2016-08-04T16:57:43","date_gmt":"2016-08-04T16:57:43","guid":{"rendered":"https:\/\/www.mjtnet.com\/blog\/?p=2712"},"modified":"2016-08-04T16:57:43","modified_gmt":"2016-08-04T16:57:43","slug":"how-to-run-an-access-macro-from-macro-scheduler","status":"publish","type":"post","link":"https:\/\/www.mjtnet.com\/blog\/2016\/08\/04\/how-to-run-an-access-macro-from-macro-scheduler\/","title":{"rendered":"How to Run an Access Macro from Macro Scheduler"},"content":{"rendered":"<p>Recently someone asked in the forums how to &#8220;<a href=\"http:\/\/help.mjtnet.com\/article\/19-converting-office-vba-to-vbscript\">Automatically Detect MS Office Install Location<\/a>&#8221; so that they could run an Access macro.  <\/p>\n<p>Well, there are ways to get the path of an installed Office application, but it isn&#8217;t necessary in order to run an Access macro.  This is a rehash of <a href=\"https:\/\/www.mjtnet.com\/forum\/viewtopic.php?f=2&#038;t=9136\">my forum answer<\/a>:<\/p>\n<p>You can run an Access macro via the command line using the \/x switch.  The ExecuteFile command lets you pass parameters.  So you could just do this:<\/p>\n<pre class=\"brush:macroscript\">ExecuteFile>%USERDOCUMENTS_DIR%\\MyDb.accdb,\/x Macro1<\/pre>\n<p>This will open the DB and run macro &#8220;Macro1&#8221;. Note my DB is in my documents folder here so I&#8217;m just using USERDOCUMENTRS_DIR but this could be any path.<\/p>\n<p><a href=\"http:\/\/help.mjtnet.com\/article\/19-converting-office-vba-to-vbscript\">Here&#8217;s a list of other command line switches<\/a>.<\/p>\n<p>For more control you could use VBScript:<\/p>\n<pre class=\"brush:macroscript\">VBSTART\r\n  Sub RunMacro(accessfile,macroname)\r\n    dim accessApp\r\n    set accessApp = createObject(\"Access.Application\")\r\n    accessApp.OpenCurrentDataBase(accessfile)\r\n    'comment next line out if you don't want access to be visible\r\n    accessApp.visible = true\r\n    accessApp.DoCmd.RunMacro macroname\r\n    'you can run a subroutine or function in module code instead if you want:\r\n    'accessApp.run \"routinename\"\r\n    accessApp.Quit\r\n    set accessApp = nothing\r\n  End Sub\r\nVBEND\r\n\r\nVBRun>RunMacro,%USERDOCUMENTS_DIR%\\MyDb.accdb,Macro1<\/pre>\n<p>This gives you more control &#8211; you could make it invisible, and as you can see you could run VBA code instead if you want &#8211; or access any of the other methods. Anything you can do inside Access you can do here &#8211; by converting VBA to VBScript:<\/p>\n<p><a href=\"http:\/\/help.mjtnet.com\/article\/19-converting-office-vba-to-vbscript\">http:\/\/help.mjtnet.com\/article\/19-converting-office-vba-to-vbscript<\/a><\/p>\n<p>But if you do <strong>really<\/strong> want to get the path, how about querying the mime-type in the registry:<\/p>\n<pre class=\"brush:macroscript\">RegistryReadKey>HKEY_CLASSES_ROOT,ms-access\\shell\\open\\command,,accPath\r\nExtractFilePath>accPath,accPath<\/pre>\n<p>Enjoy!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recently someone asked in the forums how to &#8220;Automatically Detect MS Office Install Location&#8221; so that they could run an Access macro. Well, there are ways to get the path of an installed Office application, but it isn&#8217;t necessary in order to run an Access macro. This is a rehash of my forum answer: You [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4,5,6],"tags":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/2712"}],"collection":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/comments?post=2712"}],"version-history":[{"count":7,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/2712\/revisions"}],"predecessor-version":[{"id":2719,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/2712\/revisions\/2719"}],"wp:attachment":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/media?parent=2712"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/categories?post=2712"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/tags?post=2712"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}