Hi All,
I am trying to copy the most recent data (Sheet1) in a directory, the file will always start with "SecuritiesBOD" but can have various endings.
There will be multiple files in the directory starting with "SecuritiesBOD"
Here is the code I currently have but is doesn't bring back the most recent file by date modified.
Any assistance on how to solve this would be greatly appreciated!
Sub CallFile()
Application.ScreenUpdating = False
Const FILEPATH As String = "G:\Datafiles\"
Const FILESPEC As String = "SecuritiesBOD*.csv"
Dim wbMyFile As Workbook
Dim wbxlFile As Workbook
Dim shData As Worksheet
Dim xlFile As String
Set wbMyFile = ThisWorkbook
Set shData = wbMyFile.Worksheets("Sheet1")
xlFile = Dir(FILEPATH & FILESPEC)
Set wbxlFile = Application.Workbooks.Open(Filename:=FILEPATH & xlFile)
shData.Range("A1:Z2000").Clear
wbxlFile.Worksheets(1).Range("A1:Z2000").Copy _
shData.Range("A1")
Debug.Print xlFile, wbxlFile.Name
Debug.Print wbMyFile.Name
wbxlFile.Close SaveChanges:=False
Range("A1").Select
Application.ScreenUpdating = True
End Sub
I am trying to copy the most recent data (Sheet1) in a directory, the file will always start with "SecuritiesBOD" but can have various endings.
There will be multiple files in the directory starting with "SecuritiesBOD"
Here is the code I currently have but is doesn't bring back the most recent file by date modified.
Any assistance on how to solve this would be greatly appreciated!
Sub CallFile()
Application.ScreenUpdating = False
Const FILEPATH As String = "G:\Datafiles\"
Const FILESPEC As String = "SecuritiesBOD*.csv"
Dim wbMyFile As Workbook
Dim wbxlFile As Workbook
Dim shData As Worksheet
Dim xlFile As String
Set wbMyFile = ThisWorkbook
Set shData = wbMyFile.Worksheets("Sheet1")
xlFile = Dir(FILEPATH & FILESPEC)
Set wbxlFile = Application.Workbooks.Open(Filename:=FILEPATH & xlFile)
shData.Range("A1:Z2000").Clear
wbxlFile.Worksheets(1).Range("A1:Z2000").Copy _
shData.Range("A1")
Debug.Print xlFile, wbxlFile.Name
Debug.Print wbMyFile.Name
wbxlFile.Close SaveChanges:=False
Range("A1").Select
Application.ScreenUpdating = True
End Sub