Open file from current directory with Access VBA

Posted on 2009-02-09
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

Question by:sdholden28
    LVL 119

    Accepted Solution

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

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

    Author Closing Comment

    perfect and fast. THANKS!

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Suggested Solutions

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now