tris070891
New Member
- Joined
- Sep 10, 2014
- Messages
- 12
I have successfully created a macro that opens the last modified CSV file in a folder as a separate workbook. Here is what it looks like:
When the above macro is executed it opens the latest modified CSV file in a seperate window.
The filename of the original workbook running the code does not change. As the Maco opens the latest modified CSV in a folder the file name is subject to change.
Here is the next part where I try to reference the CSV file using the Vlookup formula:
I think the problem is not setting "latestfile" as a workbook object?
Any help would be greatly appreciated. Thanks so much.
Dim strFolder As String
Dim strFile As String
Dim latestFile As String
Dim dtLast As Date
' assign variables
strFolder = "S:\Active Projects\8450 - Woolworths EDR Brand Tracker Research\8450 - Project Documents\8450 - Fieldwork\8450 - Sample\8450 - Project History\"
strFile = Dir(strFolder & "\*.*", vbNormal) ' Any File
' loop through files to find latest modified date
Do While strFile <> ""
If FileDateTime(strFolder & strFile) > dtLast Then
dtLast = FileDateTime(strFolder & strFile)
latestFile = strFolder & strFile
End If
strFile = Dir
Loop
Workbooks.Open (latestFile)
When the above macro is executed it opens the latest modified CSV file in a seperate window.
The filename of the original workbook running the code does not change. As the Maco opens the latest modified CSV in a folder the file name is subject to change.
Here is the next part where I try to reference the CSV file using the Vlookup formula:
' Switch back to the workbook running the code
ThisWorkbook.Activate
'Vlookup
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-7],'[latestfile]'!C2,1,FALSE)"
I think the problem is not setting "latestfile" as a workbook object?
Any help would be greatly appreciated. Thanks so much.