Import file with name changing daily

[fraudit]

I'd like to automate creating an Excel spreadsheet built by several files (stored in different sheets), including a daily export file in CSV format. My problem is that this CSV file has date as a part of its name (all other files are named without any changing parts), e.g. REPORT_20150216.csv.

Is there a way to build a wildcard (or similar) import mechanism that would look for files whose filenames start with REPORT? I've tried to put "REPORT*.csv, but it didn't work . At the moment I simply created a single Application.GetOpenFilename entry for this particular file, but it would be perfect to have it imported automatically, without manual selection.

For other files I'm using the simplest code:

  With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\myusername\Desktop\myfilename.csv", Destination:=Range("$A$1"))

followed by respective parameters.

Is there a way to play with a wildcard here?

 

[fraudit]

OK, by gathering several tips from VBA forums I've come with the following, non-working, code:

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\myusername\Desktop\REPORT_" & Format(Date, "yyyymmdd") & ".csv*", Destination:=Range("$A$1"))

The problem is that by running it I'm receiving well-known 1004 error and while debugging I'm getting the following part of the code highlighted:

.Refresh BackgroundQuery:=False

Anyone is able to assist me with that?