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

excelplus

New Member
Joined
Aug 5, 2021
Messages
3
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,132
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?
VBA Code:
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

New Member
Joined
Aug 5, 2021
Messages
3
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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?
VBA Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,483
Messages
5,770,361
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top