Openfile with time stamp 2

[Jimmylaki]

need to write a code that can open a file which is saved to a common drive a number of times per day using yesterday`s business date with a time stamp. The file format is saved as `FxData2608_09_26.xls`. (09 & 26 are hour and minutes respectively)The problem is that the file is never saved at the same time and I wish to open the first saved file. My code is stuck on the time stamp part of the code which returns current time. If I could have some help on how to solve for the time stamp please. This is my code so far (i am still quite a basic vba user):

Dim Msg, Style, Title, Response
Dim yestdate As Date
Dim dtmdate As Date
Dim filename As String
Dim lastrow As Integer
Dim Numb_row As Integer

Msg = "Are you sure the FX Report has updated?" ' first message
Style = vbYesNo + vbInformation + vbDefaultButton3
Title = "Getting Data"
Response = MsgBox(Msg, Style, Title)

`opens the file excluding weekends
If Response = vbYes Then

yestdate = Format(Date - 1)
'If yestdate=sunday we remove 3 days
If Weekday(yestdate) = 1 Then
yestdate = Format(Date - 3)
Else
yestdate = Format(Date - 1)
End If

'Call Function Bus_Date to check if Business Date
yestdate = Bus_Date.busdate(yestdate)

Application.ScreenUpdating = False
filename = Format(yestdate, "ddmm" & Time, "_Hh_Nn ") & ".xls"

'to open the file for yesterday's business day
Workbooks.Open(filename:="I:\Reports\FXreport" & filename). _
RunAutoMacros Which:=xlAutoOpen

 

[formerTexan]

Hi Jimmy,

As you have surmised, if you want a timestamp you will have to create it yourself. I am not sure if the dtmdate variable is meant for this, however there is no place in your code where you set a value to it.

In general use the vba Now() function to get a current date/time.

 dtmdate = Now()

Cheers, Bill

 

[PWise]

Try

Dim MinTime As String
Dim Filename As String
Dim MinFileTime As date
Dim CurentFileTime As Date
Filename = dir("I:\Reports\FXreport\"&Format(yestdate, "ddmm") &"*.xls")
do until Filename = ""
CurentFileTime = timevalue(replace(mid(Filename ,12,5),"_",":"))
if MinFileTime > CurentFileTime then
 MinFileTime =CurentFileTime
 MinTime =  mid(Filename ,12,5)
end if
filename=dir
loop
filenae ="I:\Reports\FXreport\"&Format(yestdate, "ddmm") & "_" & MinTime  & ".xls")
 Workbooks.Open(filename = Filename_ 
        RunAutoMacros Which:=xlAutoOpen )

 

[Jimmylaki]

Thank you for the great suggestions. I have input the code but get a runtime error on

Workbooks.Open(Filename:=Filename). _
RunAutoMacros Which:=xlAutoOpen

the error is showing that the path as
filename="I:\Reports\FXreport2608_.xls

but today the file has saved at 9:32am ie.

"I:\Reports\FXreport2608_09_32.xls


anyother suggestion to vary this code?

 

[formerTexan]

Hi Jimmy,

Perhaps you can help clarify my thinking.

Over the course of a day, several versions of a file will be generated. For example:

FxData2608_09_26.xls
FxData2608_10_22.xls
FxData2608_11_38.xls

If I am following you correctly, your difficulty is not in naming the files, but in running a Dir loop that will find and return only the first or earliest file (FxData2608_09_26.xls).

Cheers, Bill

 

[PWise]

Sorry Error in Code
SB

Dim MinTime As String
Dim Filename As String
Dim MinFileTime As date
Dim CurentFileTime As Date
Filename = dir("I:\Reports\FXreport\"&Format(yestdate, "ddmm") &"*.xls")
do until Filename = ""
CurentFileTime = timevalue(replace(mid(Filename ,12,5),"_",":"))
if MinFileTime > CurentFileTime then
 MinFileTime =CurentFileTime
 MinTime =  mid(Filename ,12,5)
end if
filename=dir
loop
[COLOR=red]filename[/color] ="I:\Reports\FXreport\"&Format(yestdate, "ddmm") & "_" & MinTime  & ".xls")
 Workbooks.Open(filename = Filename_ 
        RunAutoMacros Which:=xlAutoOpen )

 

[Jimmylaki]

Thank you kindly for your help gents. The code has been tweaked a little to the below which is returning the correct file path:



Dim FichierSch As Variant
Dim hour_temp As Integer
Dim min_temp As Integer
Dim hour_last As Integer
Dim min_last As Integer
Dim x As Integer

hour_last = 60
min_last = 24

Set FichierSch = Application.FileSearch

With FichierSch
.LookIn = "I:\\Reports" 'Folder where the files are...
.Filename = "*.xls" 'Extension
If .Execute > 0 Then 'If there is xls file
For x = 1 To .FoundFiles.Count
If InStr(1, .FoundFiles(x), "FXReport" & Format(yestdate, "ddmm")) <> 0 Then 'the extended name of the file
hour_temp = Mid(Right(FichierSch.FoundFiles(x), 9), 1, 2)
min_temp = Mid(Right(FichierSch.FoundFiles(x), 9), 4, 2)
If (hour_temp = hour_last) And (min_temp < min_last) Then
Filename = FichierSch.FoundFiles(x)
hour_last = hour_temp
min_last = min_temp
End If
If hour_temp < hour_last Then
Filename = FichierSch.FoundFiles(x)
hour_last = hour_temp
min_last = min_temp
End If
End If
Next x
Else
MsgBox ("Please check if the has been saved to the drive")
End If
End With

Workbooks.Open(Filename:=Filename). _
RunAutoMacros Which:=xlAutoOpen