need to open a pdf file saved using VBA

[leejoos]

Hello there,

I have a code that saves an excel file into a desktop folder with name, date and time stamp. I need to open and view the last saved PDF file once saved into the folder.Kindly help on this! My folder path is "C:\Users\augustin.EPPELHEIM\Desktop\Application sheet" and my file name looks like "Liju_AN-123_14.09.2017_04 45 PM"

Best regards,

 

[pbornemeier]

Option Explicit

'From: https://www.mrexcel.com/forum/excel-questions/1013053-vba-open-file-using-windows-default-program-extension-file-type.html
'Which referenced a John Walkenbach book
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 And Win64 Then
Private Declare PtrSafe 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
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
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
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Sub OpenLatestPDFInDir()

    'If bOpenInBrowser = True then open with Internet Explorer
    'If bOpenInBrowser = False then open with default program
    Const bOpenInBrowser As Boolean = False
    
    Dim sFilePath As String
    'The following will set sFilePath to Documents path for the current user. Edit as necessary.
    'sFilePath = Environ("USERPROFILE") & "\Documents"
    sFilePath = "C:\Users\augustin.EPPELHEIM\Desktop\Application sheet"
    
    Dim ofldr As Object
    Dim oFile As Object
    Dim lFileIndex As Long
    Dim dteFile As Date
    Dim sFilePathNameExt As String
    Dim FSO As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set ofldr = FSO.GetFolder(sFilePath)
    
    dteFile = DateSerial(2000, 1, 1)
    If ofldr.Files.Count > 0 Then
        For Each oFile In ofldr.Files
            If UCase(Mid(oFile.ShortName, InStrRev(oFile.ShortName, ".") + 1)) = "PDF" Then
                If oFile.DateLastModified > dteFile Then
                    sFilePathNameExt = oFile.Path
                    dteFile = oFile.DateLastModified
                End If
            End If
        Next
    End If
    
    If LenB(sFilePathNameExt) > 0 Then
        If bOpenInBrowser Then
            OpenPDFFileInBrowser sFilePathNameExt
        Else
            OpenFileWithDefaultProgram sFilePathNameExt
        End If
    End If
    
    Set ofldr = Nothing
    Set FSO = Nothing
    
End Sub
    
Sub OpenFileWithDefaultProgram(sFilePathNameExt As String)

    'From a John Walkenbach book
    Dim Result As Long
    Result = ShellExecute(0&, vbNullString, sFilePathNameExt, _
        vbNullString, vbNullString, vbNormalFocus)
    If Result < 32 Then MsgBox "Error"
    
End Sub

Sub OpenPDFFileInBrowser(sFilePathNameExt As String)

    Dim pdf As Object
    Dim sFilePath As String
    
    Set pdf = CreateObject("internetexplorer.application")
    pdf.Visible = True
    pdf.Navigate ("file://" + sFilePathNameExt)
    
    Set pdf = Nothing

End Sub