ruturajs7rs
New Member
- Joined
- Jan 26, 2021
- Messages
- 11
- Office Version
- 365
- Platform
- Windows
Dear all,
I am looking for a VBA code to open and copy data from a file that is uploaded recently than the current file in the folder to compare data. I have 4 folders - Test1,Test2,Test3, Test4. The files in the folders are in the format "Test1_YYYYMMDD" (in folder Test1 and YYYYMMDD is the date of creation of file) and similarly in other folders. Now for example I consider folder Test1 and perform macro in Today's file (i.e. Test1_20200203), then the macro should open and copy data from the file which is present before the file in which macro is run. The file can be yesterday's or one week before (whichever the macro finds first).
I can perform it in a single folder but I want to know how can I make it generalized so it works in the other 3 folders as well within a single macro.
Code I am using for single folder-
Public Sub openfile()
Dim dtTestDate As Date
Dim sStartWB As String
Const sPath As String = "C:\Test\"
Const dtEarliest = #1/1/2021#
dtTestDate = Date
sStartWB = ActiveWorkbook.name
While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
On Error Resume Next
Workbooks.Open sPath & "Test1_" & Format(dtTestDate, "YYYYMMDD" & ".xls"
dtTestDate = dtTestDate - 1
On Error GoTo 0
Wend
If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."
I am looking for a VBA code to open and copy data from a file that is uploaded recently than the current file in the folder to compare data. I have 4 folders - Test1,Test2,Test3, Test4. The files in the folders are in the format "Test1_YYYYMMDD" (in folder Test1 and YYYYMMDD is the date of creation of file) and similarly in other folders. Now for example I consider folder Test1 and perform macro in Today's file (i.e. Test1_20200203), then the macro should open and copy data from the file which is present before the file in which macro is run. The file can be yesterday's or one week before (whichever the macro finds first).
I can perform it in a single folder but I want to know how can I make it generalized so it works in the other 3 folders as well within a single macro.
Code I am using for single folder-
Public Sub openfile()
Dim dtTestDate As Date
Dim sStartWB As String
Const sPath As String = "C:\Test\"
Const dtEarliest = #1/1/2021#
dtTestDate = Date
sStartWB = ActiveWorkbook.name
While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
On Error Resume Next
Workbooks.Open sPath & "Test1_" & Format(dtTestDate, "YYYYMMDD" & ".xls"
dtTestDate = dtTestDate - 1
On Error GoTo 0
Wend
If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."