insert a text file with a changing name

[renderman7]

hi all,

i have a text file that i want to insert into excel daily. the issue is the name will change each day.

data_20121212.txt tomorrow it will be data_20121213.txt

I have a cell on my sheet with the date part, but i can not get excel to insert the cell data into the path name.

this is the start of the code i have now

datename = ThisWorkbook.Worksheets("Main").Range("A3").Value

    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;D:\data_'datename'", Destination:=Range("$A$1") _
        )
        .Name = "data_'datename'"
        .FieldNames = True

.RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(19, 349, 3, 6, 7)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        
    End With
End Sub

Any help would be great

Moderator's Note: You have to put code tags around codes. Select the code then hit the "#" sign. I'll do it for now. Thank you.