Open .xlsx File from VBA, the name containing Date & Time which changes.

[Muhammad_Bilal]

I have an Excel file on my Desktop with name "Results_2017_08_31_05_45.xlsx". I want to open it through VBA. As you can see the name contains;

Text "Result",
Date "2017_08_31" and
Time "05_45" it could be "05_50" or "05_55" and so on.

While opening file from VBA

(Workbooks.Open)

we can handle Date by

Now()

, but as time changes with every other file. So how do I able to open this file with Variable time.

 

[GTO]

... but as time changes with every other file. So how do I able to open this file with Variable time.

Could you please explain that part of your question?

Thank you,

Mark

 

[Muhammad_Bilal]

let me explain , what I do.

first of all when I download the file on desktop its name would be Results_2017_09_01_05_45.

I will open this file through VBA use do some work on it and then delete it. Next time I download another file its name changes to
Results_2017_09_01_05_50.
as you can see only time had changed.
Now I want a code which can handle both of the situation at the same time.

 

[GTO]

Hi Muhammad,

I am still not sure I understand, but if we are looking to find a filename based upon the current time (or nearly the current time), maybe something like:

In a Standard Module:

Option Explicit
  
' "Results_2017_08_31_05_45.xlsx"
Sub example()
Const NEG_MIN_OFFSET = 20


Dim WB As Workbook
Dim dateTimeInitial As Date
Dim dateTimeAdjusted As Date
Dim n As Long
Dim sDesktopPath As String
Dim sFileName As String
  
  dateTimeInitial = Time - TimeSerial(0, NEG_MIN_OFFSET, 0)
    
  sDesktopPath = CreateObject("Wscript.Shell").SpecialFolders("Desktop") & "\"
  
  For n = 0 To 25
    dateTimeAdjusted = dateTimeInitial + TimeSerial(0, n, 0)
    
    sFileName = _
      Dir(sDesktopPath & "Results_" & CStr(Year(Date)) & "_" & Format(Month(Date), "00") & "_" & _
          Format(Day(Date), "00") & "_" & Format(Hour(dateTimeAdjusted), "00") & "_" & _
          Format(Minute(dateTimeAdjusted), "00") & ".xlsx")
    
    If Len(sFileName) Then Exit For
    
  Next
  
  If Len(sFileName) Then
    Set WB = Workbooks.Open(sDesktopPath & sFileName)
    
    MsgBox "Do stuff with " & WB.Name
    
    WB.Close False
    DoEvents
    Kill sDesktopPath & sFileName
    
  Else
    MsgBox "File not found..."
  End If
  
End Sub

PLEASE NOTE: As we are killing/deleting the file, please create junk files to test against.

Hope that helps,

Mark