[RESOLVED] Opening a PDF with default application

[mavt14]

Okay so I have an Excel database that uses a form to lookup values in a table and based on the value gets a file path.

The issue is that the files were originally all .doc files. I used the following code to open them:

Public Function OpenFile(strfile As String)
     Dim x As Variant
     'call word to the program
     'C:\WINDOWS\SYSTEM32\WINWORD.exe
     x = Shell("WINWORD.exe " & strfile, 1)
End Function

Private Sub OpenButton_Click()
'set the sheet
Set Sheet = ActiveWorkbook.Worksheets("DCA_Data")
Dim strfile As String
 
If listi = 0 Then
    'error if nothing is selected
    response = MsgBox("No Value Selected", 0, "No Values")
Else
    'call the path
    strfile = Sheet.Cells(listi, 4)
    'open the path
    OpenFile (strfile)
End If
End Sub

It is now required that I open the files as PDFs. I have converted all of the files and have updated the tables. I need to modify this code so that it opens the PDF using the computer's default application. This file will be on a server so many different computers have access to it. This means that different versions and programs may be being used. Any help would be much appreciated. Thanks.

[RobDog888]

Use the ShellExecute API as it opens the passed files with their default associated application. Only the file to open's path is required, not the program itself.

Option Explicit
 
Private Declare Function ShellExecute 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
 
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMINIMIZED As Long = 2
Private Const SW_SHOWMAXIMIZED As Long = 3
 
Private Const ERROR_FILE_NOT_FOUND As Long = 2&
Private Const ERROR_PATH_NOT_FOUND As Long = 3&
Private Const ERROR_BAD_FORMAT As Long = 11&
Private Const SE_ERR_ACCESSDENIED As Long = 5
Private Const SE_ERR_ASSOCINCOMPLETE As Long = 27
Private Const SE_ERR_DDEBUSY As Long = 30
Private Const SE_ERR_DDEFAIL As Long = 29
Private Const SE_ERR_DDETIMEOUT As Long = 28
Private Const SE_ERR_DLLNOTFOUND As Long = 32
Private Const SE_ERR_FNF As Long = 2
Private Const SE_ERR_NOASSOC As Long = 31
Private Const SE_ERR_OOM As Long = 8
Private Const SE_ERR_PNF As Long = 3
Private Const SE_ERR_SHARE As Long = 26
 
Private Sub Command1_Click()
    On Error GoTo MyError
    Dim lRet As Long
    lRet = ShellExecute(Application.Hwnd, "Open", "C:\MyPath\MyFile.pdf", vbNullstring, "C:\", SW_SHOWNORMAL)
    If lRet <= 32 Then
        Select Case lRet
            Case 0
                'MsgBox "The operating system is out of memory or resources."
            Case ERROR_FILE_NOT_FOUND
                'MsgBox "The specified file was not found."
            Case ERROR_PATH_NOT_FOUND
                'MsgBox "The specified path was not found."
            Case ERROR_BAD_FORMAT
                'MsgBox "The .EXE file is invalid (non-Win32 .EXE or error in .EXE image)."
            Case SE_ERR_ACCESSDENIED
                'MsgBox "The operating system denied access to the specified file."
            Case SE_ERR_ASSOCINCOMPLETE
                'MsgBox "The filename association is incomplete or invalid."
            Case SE_ERR_DDEBUSY
                'MsgBox "The DDE transaction could not be completed because other DDE transactions were being processed."
            Case SE_ERR_DDEFAIL
                'MsgBox "The DDE transaction failed."
            Case SE_ERR_DDETIMEOUT
                'MsgBox "The DDE transaction could not be completed because the request timed out."
            Case SE_ERR_DLLNOTFOUND
                'MsgBox "The specified dynamic-link library was not found."
            Case SE_ERR_FNF
                'MsgBox "The specified file was not found."
            Case SE_ERR_NOASSOC
                'MsgBox "There is no application associated with the given filename extension."
            Case SE_ERR_OOM
                'MsgBox "There was not enough memory to complete the operation."
            Case SE_ERR_PNF
                'MsgBox "The specified path was not found."
            Case SE_ERR_SHARE
                'MsgBox "A sharing violation occurred."
        End Select
    End If
    Exit Sub
MyError:
    MsgBox Err.Number & " - " & Err.Description
End Sub

[mavt14]

Thanks man. That works awsome. I don't know how you guys figure this stuff out, but you rule. Thanks