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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,516
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top