Tweaking VBA

[Steve]

Hi

I hope this is reasonably straightforward.

I've recorded a macro to pull data from a text file into the Data Wizard and populate a worksheet. The problem is that I can only link to a specific file, but we get 1 file per day. So what I was wondering, is - is there an easy way to change this script:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\finance\docs\00222020112912TT.txt" _
, Destination:=Range("$A$6"))

so that the '20112912' (YYYYDDMM) part of the file referred to is replaced by the contents of a cell? 00222 is our office designation, and is part of every txt file, as is the 'TT'.

My thought was to have say, C1, as an input cell where the YYYYDDMM date is input. Another cell links the '\\finance\docs\001540' & C1 & 'TT.txt'

Does anyone have any ideas on how to do this, please; any answers would be greatly appreciated.

Thanks

Steve

 

[Steve]

.... not sure where the 001540 comes from near the end; should have said 00222.