VBA code to open latest file

Hi Mashiul

Try this:

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
 
Sub OpenGreatestFile()
     
    Dim xRow As Long
    Dim xDirect$, xFname$, InitialFoldr$, xFname2Open$
     
    InitialFoldr$ = "U:\Engineering\Design\DWG\" & Left(ActiveCell.Value, 3) & "\" '<<< Startup folder to begin searching from
     
            xFname$ = Dir(InitialFoldr$, 7)
            xFname2Open$ = xFname$
            Do While xFname$ <> ""
                If xFname$ > xFname2Open$ And xFname$ <> "Thumbs.db" Then
                    xFname2Open$ = xFname$
                End If
                xFname$ = Dir
            Loop
        
    ShellExecute 0, "Open", InitialFoldr$ & xFname2Open$, "", "", vbNormalNoFocus
        
End Sub

Regards,
Rick

Hi Rick,

Thank you very much for your code.

I am not an expert of VBA. Let me give you the steps what I really want,

1. Read the intial d7 value as let say, 151604
2. Search greatest version of pdf file as 151604EV.pdf (initial value is 151604AA.pdf) at the startup folder at U:\Engineering\Design\DWG\151\
3. Open 151604EV.pdf

I ran your code and I am getting error.

Any further help would be highly appreciated.

Thanks.

Mashiul

Hi Mashiul,

Then try this:

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
 
Sub OpenGreatestFile()
     
    Dim xRow As Long
    Dim xDirect$, xFname$, InitialFoldr$, xFname2Open$
     
    InitialFoldr$ = "U:\Engineering\Design\DWG\151\" '<<< Startup folder to begin searching from
     
            xFname$ = Dir(InitialFoldr$, 7)
            xFname2Open$ = Range("D7").Value
            Do While xFname$ <> ""
                If xFname$ > xFname2Open$ _
                   And xFname$ <> "Thumbs.db" _
                   And InStr(xFname$, Range("D7").Value) > 0 _
                Then
                    xFname2Open$ = xFname$
                End If
                xFname$ = Dir
            Loop
        
    ShellExecute 0, "Open", InitialFoldr$ & xFname2Open$, "", "", vbNormalNoFocus
        
End Sub

Regards,
Rick

Hi Rick,

It's working perfectly. That's what I really need.

Thanks very much & appreciated.

Mashiul

Hi Rick,

Can you please help me with adding one more command? I would like to save the opened latest pdf file to a folder let say, "C:\pdfs".

Your kind help would be highly appreciated.

Thanks.