Ever needed to run, execute a file? It could be a *.bat, *.vbs, *.exe, … One common method is to use the shell function. However, we see quite often in the forums and newsgroups regarding issues with it not working and users not getting the synthax right because of spaces in the file paths, etc.

Below is, yet again, a very simple procedure which encapsulates the file to be run/executed with the necessary quotations so that you no longer have any headaches trying to get your Shell function operational. Simply copy the function to a module and call it as indicated below.

'---------------------------------------------------------------------------------------
' Procedure : RunFile
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Run/Execute files from vba (bat, vbs,…)
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strFile - full path including filename and extension
' strWndStyle - style of the window in which the program is to be run
'               value can be vbHide,vbNormalFocus,vbMinimizedFocus
'               vbMaximizedFocus,vbNormalNoFocus or vbMinimizedNoFocus
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' RunFile("c:\test.bat", vbNormalFocus)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Feb-05             Initial Release
'---------------------------------------------------------------------------------------
Function RunFile(strFile As String, strWndStyle As String)
On Error GoTo Error_Handler

   Shell "cmd /k """ & strFile & """", strWndStyle

Error_Handler_Exit:
   On Error Resume Next
   Exit Function

Error_Handler:
   MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
   Err.Number & vbCrLf & "Error Source: RunFile" & vbCrLf & "Error Description: " & _
   Err.Description, vbCritical, "An Error has Occurred!"
   Resume Error_Handler_Exit
End Function

If however you are interested in opening a file, any type of file, then please refer to my MS Access VBA – Open a File post.