Button To Search A Network Drive Folder For PDF File And Open It

[data808]

Has anyone created a button using VBA to search a nework drive for a PDF file and open it for viewing?

I was thinking of naming the PDF file with the auto number of the record it will be associated with, so that way the PDF file name will always be unique. Then when the user clicks the button on the form while viewing a record, it will search the network drive folder for a PDF with the same auto number as the record and open it.

Could someone help me create this if its even possible?

Thank you!

[Gicu]

Something like this should do it:

Option Compare Database
Option Explicit
Declare Function ShellExecute Lib "shell32.dll" Alias _
    "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, lpParameters As String, _
    ByVal lpDirectory As String, ByVal nShowCMd As Long) As Long
 
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
 
'***App Window Constants***
Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 3            'Open Maximized
Public Const WIN_MIN = 2            'Open Minimized
 
'***Error Codes***
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&
Global Const SW_SHOWNORMAL = 1
 
Public 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
    Else
        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
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<ADD ABOVE TO A NEW STANDARD MODULE>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 
‘This goes into your existing form where you add the button and have the autonumber
Private Sub Form_Current()
 
Dim sPDFPath as String
 
sPDFPath = dLookup("PDF_Path","tblSettings") & "\" & Me.YourAutoNumber & ".PDF" 'assumes you store the network path for the files in a settings table
 
Me.cmdOpenPDF.Enabled = Not(Dir(sPDFPath) = "") 'Enable or disable the command button based on finding the matching pdf file
 
'<<<<<<<<<<<<<<<your other code for this event>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 
End Sub
 
 
Private Sub cmdOpenPDF_Click()
Dim vReturn
Dim sPDFPath as String
 
sPDFPath = dLookup("PDF_Path","tblSettings") & "\" & Me.YourAutoNumber & ".PDF" 'assumes you store the network path for the files in a settings table
vReturn = fHandleFile(strFilePath & strFileName, WIN_NORMAL)
 
'or you can use the line below (no need for the new module above)
'Application.FollowHyperlink sPDFPath
End Sub

Cheers,

[data808]

Thanks @Gicu. I think I understand your post except for the part where it says "assumes you store the network path for the files in a settings table" and also "'or you can use the line below (no need for the new module above)".

I don't know how to store the network path in a settings table. Is there a way to just set the file path within the VBA code? Perhaps in the cmdOpenPDF Click event?

Then that last part where you say that I can use "Application.FollowHyperlink sPDFPath" instead of creating a new module with that long code at the top for a new standard module? Are you suggesting I replace all that with just this line "Application.FollowHyperlink sPDFPath" instead? If so, do I just add that to the Form_Current event?

[moke123]

I generally use a folder in the same directory as the database, named something like MyPdfFiles.
then you can use
currentproject.Path & "\MyPdfFiles" to get the folder path.

You can either concatenate the file name to that or use something like below
(requires a reference to Microsoft scripting runtime)

Sub sOpenPDF(folPath As String, FilName As String)
    Dim fso As New FileSystemObject
    Dim fullPath As String

    fullPath = fso.BuildPath(folPath, FilName)

    If fso.FileExists(fullPath) Then
        Application.FollowHyperlink fullPath
    Else
        MsgBox "File Not Found"
    End If

End Sub

you would call it like

dim pth as string
pth = currentproject.path &"\MyPdfFiles"

sOpenPDF(pth,"12345.PDF")

[Gicu]

I don't know how to store the network path in a settings table

You simply create a (local) new table called tblSettings with a ShortText field called PDF_Path (in my example, but choose whatever you want), save it, open it and add your network path to the folder containing the PDFs (without the final "" as you notice it gets added in the code. Of course you can hard-code it VBA, but then it makes it harder to change it if needed. Plus a local settings table is handy for many other uses.

The

Application.FollowHyperlink sPDFPath

should go in the Click event of the button; the Current event of the form simply enables or disables the button if the matching PDF file is found or not found in the specified folder.

Cheers,

[data808]

I generally use a folder in the same directory as the database, named something like MyPdfFiles.
then you can use
currentproject.Path & "\MyPdfFiles" to get the folder path.

You can either concatenate the file name to that or use something like below
(requires a reference to Microsoft scripting runtime)

Sub sOpenPDF(folPath As String, FilName As String)
    Dim fso As New FileSystemObject
    Dim fullPath As String

    fullPath = fso.BuildPath(folPath, FilName)

    If fso.FileExists(fullPath) Then
        Application.FollowHyperlink fullPath
    Else
        MsgBox "File Not Found"
    End If

End Sub

you would call it like

dim pth as string
pth = currentproject.path &"\MyPdfFiles"

sOpenPDF(pth,"12345.PDF")

Thanks! Will keep this in mind if I can't figure out @Gicu's suggestion.

[data808]

You simply create a (local) new table called tblSettings with a ShortText field called PDF_Path (in my example, but choose whatever you want), save it, open it and add your network path to the folder containing the PDFs (without the final "" as you notice it gets added in the code. Of course you can hard-code it VBA, but then it makes it harder to change it if needed. Plus a local settings table is handy for many other uses.

The

Application.FollowHyperlink sPDFPath

should go in the Click event of the button; the Current event of the form simply enables or disables the button if the matching PDF file is found or not found in the specified folder.

Cheers,

Thank you very much @Gicu. I set everything up like you mentioned and right now the button seems to be doing what it's supposed to as far as being activated or deactivated based on if the PDF file exists or not. However, when I click the button if the PDF does exist in the network drive, it seems to only open file explorer to the Documents folder instead of the G: Drive in which I set it in the tblSettings. Am I missing something?

[data808]

Ok I got it to work if I didn't use the standard module route. Instead I used the "Application.FollowHyperlink sPDFPath" in the click event and I also had to deactivate the line "vReturn = fHandleFile(strFilePath & strFileName, WIN_NORMAL)" as it kept giving a not defined error.

I'm going to run more tests and see if everything is ok. Will get back to you.

[moke123]

vReturn = (strFilePath & strFileName, WIN_NORMAL)

Try

Call fHandleFile(strFilePath & strFileName, WIN_NORMAL)

make sure the syntax is correct with strFilePath & strFileName

[data808]

You simply create a (local) new table called tblSettings with a ShortText field called PDF_Path (in my example, but choose whatever you want), save it, open it and add your network path to the folder containing the PDFs (without the final "" as you notice it gets added in the code. Of course you can hard-code it VBA, but then it makes it harder to change it if needed. Plus a local settings table is handy for many other uses.

The

Application.FollowHyperlink sPDFPath

should go in the Click event of the button; the Current event of the form simply enables or disables the button if the matching PDF file is found or not found in the specified folder.

Cheers,

Ok looks like its working great! Thanks again @Gicu for the help.

[Gicu]

You're very welcome!

Cheers,