Concatonating within commands???

Ok - I know I said no more scripting, but ...

Im still with Excel (VBA) macros and trying to find a way of inputting data from a html sheet (stored on the local machine) into the Excel sheet for manipulation of the stuff in the html. The problem is I need to access the latest file which is a product of the date (in yymmdd format) and some text . What I need to do is find a way of telling Excel which file to pull.

an example of what I have and works (but I have to change the macro every time I run it to get the file date right) is here ...

 Sub test()

Sheets("my data").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\myFiles\150805_final.html", Destination:=Range(
        "$A$1"))
        .Name = "150805_final_1"
End sub

and what I want is (sort of) here ...

Sub test()

theDate = DateSerial(Year(Now), Month(Now), Day(Now) - 1)
theDate1 = Format$(theDate, "yymmdd")
 
Sheets("my data").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\myFiles\" & "theDate1" & "_final.html", Destination:=Range(
        "$A$1"))
        .Name = "theDate" & "_final_1"

End sub

Sadly this doesnt work at all - Excel tells me Im stupid before I get to run thew macro.

Any ideas please - I will be forever in your debt ... or did I say that last time?