We help IT Professionals succeed at work.

Open file from current directory with Access VBA

sdholden28 asked
Medium Priority
Last Modified: 2012-05-06
Currently I have a form from which I can open external files via the onclick events of buttons on the forms. I grabbed the module off the internet (shown below) and call the function with

fHandleFile ("C:\Documents and Settings\holdensd\My Documents\Holden Energy\8000 Gallon Tank Chart.htm"), WIN_NORMAL

I need to open this file from the current directory rather than hard-coding its location so that when installing this database on another machine or in another directory there are no file location issues. So how can i modify what i currently have to accomplish this? Is there a way to get the current working directory and then pass it to my existing function? Or does the function need to be modified? Is there another, better way? I'm not new to VBA, but I'm not overly experienced either.

Option Explicit
Option Compare Database
Private Declare Function apiShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) _
    As Long
Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 2            'Open Maximized
Public Const WIN_MIN = 3            'Open Minimized
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&
Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function

Open in new window

Watch Question

Top Expert 2016
if you are referring to the folder where your db application is

fHandleFile (currentproject.path & "\8000 Gallon Tank Chart.htm"), WIN_NORMAL

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


perfect and fast. THANKS!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.