Vlookup in Macro with variable date filename 2

[hjgoldstein]

Hi all.

I have a macro in Excel 2007 which is referencing a filename in a vlookup.

The relevant part of the macro, when I set it up yesterday, was

    "=VLOOKUP(RC[-1],'2013-02-27_Utilisation.csv'!C15:C16,2,FALSE)"

which was fine.

Today, of course, it should refer to the same filename with today's date and here is my problem.

I have tried various permutations and the closest I can get is:

Dim FilenamePrefix As String
    FilenamePrefix = Format(Date, "yyyy-mm-dd")

Dim CSVFilename As String
    CSVFilename = FilenamePrefix & "_Utilisation.csv"
    
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1]," & CSVFilename & "!C15:C16,2,FALSE)"

which brings up a dialogue box asking for a filename. It seems to have taken the CSVFilename variable but left off the yyyy-mm and only included the dd part.

Where am I going wrong?


Aspiring to mediocrity since 1957

 

[SkipVought]

Hi,

You didn't include the apostrophes around the CSVfilename in the formula

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hjgoldstein]

Thanks for coming back so quickly, Skip.

I put in apostrophes as you suggested and get a Compile Error: Expected: expression error. Any more advice?

Aspiring to mediocrity since 1957

 

[ClulessChris]

ActiveCell.FormulaR1C1 = _ 
"=VLOOKUP(RC[-1],'" & CSVFilename & "'!C15:C16,2,FALSE)"

???

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[hjgoldstein]

Thanks guys. Have a star each for speed, accuracy and being there ;-)

Aspiring to mediocrity since 1957