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