Macro to reference a CSV variable in a Vlookup

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:

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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
Try something like

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-7],'[" & latestFile & "]'!C2,1,FALSE)"

latestFile is a string variable that would be concatenated within the formula string.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,330
Messages
5,528,043
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top