VBA code to open last save excel workbook in a specific file path

[excelplus]

the code I have currently opens a specific file instead of the last saved file. This file will be open by different users with the same file path names. I don't want to use the file name detailreport (22) because each time the number increases by one when saved. The VBA code needs to find the last saved file because the number will always increase by 1. And once it opens the path to the Downsloads folder. I need the folder to close.

Sub HRIS()

'
' HRIS Macro
'

'
    Dim strUser As String
strUser = Environ("username")
Shell "explorer C:\Users\" & strUser & "\downloads"
    Workbooks.Open fileName:= _
        "C:\Users\hello1234\Downloads\DetailReport (22).xls"
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
     Rows("1:1").Select
    Selection.RowHeight = 33.75
    Windows("Org Chart.xlsm").Activate
End Sub

 

[John_w]

Here is your macro updated to find and open the latest "DetailReport*.xls" in the downloads folder and close the downloads folder explorer window. Why do you want to open the downloads folder in File Explorer and then close it?

Public Sub HRIS()
    
    Dim downloadsFolder As String
    Dim workbookFile As String
    
    downloadsFolder = "C:\Users\" & Environ("username") & "\downloads"
    
    Shell "explorer """ & downloadsFolder & """"
    
    workbookFile = Find_Latest_File(downloadsFolder, "DetailReport*.xls")
    
    Workbooks.Open fileName:=workbookFile
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Rows("1:1").Select
    Selection.RowHeight = 33.75
    Windows("Org Chart.xlsm").Activate

    Close_Explorer_Window Mid(downloadsFolder, InStrRev(downloadsFolder, "\") + 1)
    
End Sub


Private Function Find_Latest_File(ByVal folder As String, Optional matchFiles As String = "*.*") As String

    Dim fileName As String
    Dim fileTime As Date, latestTime As Date
    
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    
    Find_Latest_File = ""
    fileTime = 0
    fileName = Dir(folder & matchFiles)
    While fileName <> vbNullString
        fileTime = FileDateTime(folder & fileName)
        If fileTime > latestTime Then
            Find_Latest_File = folder & fileName
            latestTime = fileTime
        End If
        fileName = Dir
    Wend

End Function


Private Sub Close_Explorer_Window(windowTitle As String)

    Shell "cmd /c TASKKILL /F /FI ""IMAGENAME eq explorer.exe"" /FI ""WINDOWTITLE eq " & windowTitle & """", vbHide
   
End Sub

 

[excelplus]

Here is your macro updated to find and open the latest "DetailReport*.xls" in the downloads folder and close the downloads folder explorer window. Why do you want to open the downloads folder in File Explorer and then close it?

Public Sub HRIS()
   
    Dim downloadsFolder As String
    Dim workbookFile As String
   
    downloadsFolder = "C:\Users\" & Environ("username") & "\downloads"
   
    Shell "explorer """ & downloadsFolder & """"
   
    workbookFile = Find_Latest_File(downloadsFolder, "DetailReport*.xls")
   
    Workbooks.Open fileName:=workbookFile
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Rows("1:1").Select
    Selection.RowHeight = 33.75
    Windows("Org Chart.xlsm").Activate

    Close_Explorer_Window Mid(downloadsFolder, InStrRev(downloadsFolder, "\") + 1)
   
End Sub


Private Function Find_Latest_File(ByVal folder As String, Optional matchFiles As String = "*.*") As String

    Dim fileName As String
    Dim fileTime As Date, latestTime As Date
   
    If Right(folder, 1) <> "\" Then folder = folder & "\"
   
    Find_Latest_File = ""
    fileTime = 0
    fileName = Dir(folder & matchFiles)
    While fileName <> vbNullString
        fileTime = FileDateTime(folder & fileName)
        If fileTime > latestTime Then
            Find_Latest_File = folder & fileName
            latestTime = fileTime
        End If
        fileName = Dir
    Wend

End Function


Private Sub Close_Explorer_Window(windowTitle As String)

    Shell "cmd /c TASKKILL /F /FI ""IMAGENAME eq explorer.exe"" /FI ""WINDOWTITLE eq " & windowTitle & """", vbHide
  
End Sub

Thanks, but I don't want the latest detailreport.xls. I want the last xls report in the downloads folder.

 

[John_w]

Thanks, but I don't want the latest detailreport.xls. I want the last xls report in the downloads folder.

In that case, change "DetailReport*.xls" to "*.xls".

 

[excelplus]

In that case, change "DetailReport*.xls" to "*.xls".

thanks that worked