Excel Q: Referencing Closed .txt files

[GoodRevrnd]

I have a folder where .txt files are downloaded every day conforming to .csv format with a predictable file name "p509mmdd.txt". I want to be able to reference "cells" in these files without having to first import them into excel. The goal is to sum all the values in a column corresponding to each customer. Is this even possible or am I stuck having to relink the file every day or creating a macro to manually import the data each time I use it?

Below is how I am attempting calculate the data. The formula itself returns expected results if I just reference the data in another sheet, but I can't get the external reference to work as it will always prompt for a file (and then still won't work after I select it).

="T:\Folder\SFTP\"&TEXT(B1, "yyyy")&"\p509"&TEXT(B1,"mmdd")&".txt"

where B1 is a manually entered date.

=SUMPRODUCT((Path!$H:$H)*(Path!$C:$C=VLOOKUP($I7,Ref!$A$5:$C$70,3,0)))

where 'Path' is a named range pointing to the previous formula. $H is value to be summed, and $C is the reference value. The vlookup is to convert the value in $I7 to the actual value format found in $C in the external file.

Halp.

 

[postmark]

I couldn't get the data to update without updating links in the data tab. I would think your best bet is to macro this.

 

[GoodRevrnd]

If all the files are converted to .xlsx first then they can be closed referenced without any problems. Hmmmm...