Date modified

Avalb

New Member
Joined
Apr 1, 2021
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
I need help-
The below macro does what is supposed to open the last two files created in a folder for the current date,. Files are saved starting at 2 AM. The problem is
strFilename2 is capturing the previous calendar date if there are two files saved, for example, 2/21 and 2/20. I need strFilename2 to show the same data as strFilename when there are two files created with different modified dates else strFilename and strFilename2 should open current day files were created.


Sub Auto_open()
Dim FileSys, objFile, myFolder, c As Object
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim strFilename, strFilename2
Dim sYesterda As Date
Application.DisplayAlerts = False
Application.ScreenUpdating = False


FolderName = "W:\$$$\$$$\EASYFOCUS 2022"
Set FileSys = CreateObject("Scripting.FileSystemObject")
Set myFolder = FileSys.GetFolder(FolderName)

dteFile = DateSerial(2022, 2, 1)
For Each objFile In myFolder.Files
If InStr(1, objFile.Name, ".csv") > 0 Then
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename2 = strFilename
strFilename = objFile.Name


End If
End If
Next objFile
'opening of latest file in the folder

Set wb1 = Workbooks.Open(FolderName & Application.PathSeparator & strFilename)
Sheets("Euroclear - EasyFocus predictiv").Select
Range("A2").Select

'This section format sheet for EasyFocus predictive report'
Cells.Select
Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:I").Select
Selection.NumberFormat = "#,##0.00"
Columns("J:J").Select
Selection.NumberFormat = "#,##0.00"
Range("A2").Select
ActiveWorkbook.SaveAs FileName:= _
"W:\I****\****\***\*******xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
'open next datemondfile'

Set w2 = Workbooks.Open(FolderName & Application.PathSeparator & strFilename2)
Cells.Select
Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:I").Select
Selection.NumberFormat = "#,##0.00"
Columns("J:J").Select
Selection.NumberFormat = "#,##0.00"
Range("A2").Select
ActiveWorkbook.SaveAs FileName:= _
"W:\I****\***\****.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
ActiveWorkbook.Close

Call Penalties

End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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
Back
Top