Recently someone asked in the forums how to “Automatically Detect MS Office Install Location” so that they could run an Access macro.
Well, there are ways to get the path of an installed Office application, but it isn’t necessary in order to run an Access macro. This is a rehash of my forum answer:
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:
ExecuteFile>%USERDOCUMENTS_DIR%\MyDb.accdb,/x Macro1
This will open the DB and run macro “Macro1”. Note my DB is in my documents folder here so I’m just using USERDOCUMENTRS_DIR but this could be any path.
Here’s a list of other command line switches.
For more control you could use VBScript:
VBSTART Sub RunMacro(accessfile,macroname) dim accessApp set accessApp = createObject("Access.Application") accessApp.OpenCurrentDataBase(accessfile) 'comment next line out if you don't want access to be visible accessApp.visible = true accessApp.DoCmd.RunMacro macroname 'you can run a subroutine or function in module code instead if you want: 'accessApp.run "routinename" accessApp.Quit set accessApp = nothing End Sub VBEND VBRun>RunMacro,%USERDOCUMENTS_DIR%\MyDb.accdb,Macro1
This gives you more control – you could make it invisible, and as you can see you could run VBA code instead if you want – or access any of the other methods. Anything you can do inside Access you can do here – by converting VBA to VBScript:
http://help.mjtnet.com/article/19-converting-office-vba-to-vbscript
But if you do really want to get the path, how about querying the mime-type in the registry:
RegistryReadKey>HKEY_CLASSES_ROOT,ms-access\shell\open\command,,accPath ExtractFilePath>accPath,accPath
Enjoy!