VBA’s not really my thing, so here’s hoping…</SPAN>
What we have:
Fairly simple file structure (2 / 3 levels, with one file per day at the root of each tree)
Files are either xls or txt
Files contain daily banking records, including a unique ID
Location of unique ID is fixed (column D or whatever)
Max records / file / day is about 20,000. Usually only files from last 12 months are relevant
Issue:
Team needs to be able to search for a unique ID (usually the most recent), identify the file it's in, go to file and get some other info from the corresponding row. At the moment, they are searching each file manually until they get a hit.
What I thought would work:
Run some code (daily) to list out something of the form:
unique ID | File reference | row reference
...in a worksheet somewhere so they could search a single xl file for the matches, then maybe link of some sort to open the file they want. Probably on big initial run to populate enough history, then code would need to be updatable such that it could be run each night and have the results appended to the rest of the list / not need to interrogate the entire file lists each time.
</SPAN>Obviously, any better ideas most welcome.
Details:
</SPAN>
For xls files:</SPAN>
Directory structure is …\YYYY\MM MMM YY\DDMMYYYYCL.xls</SPAN>
e.g. …\2014\05 MAY 2014\01052014CL.xls</SPAN>
Unique ID is in col N
</SPAN>
For txt files:</SPAN>
Directory structure is …\YYYY\MM MMM\V8 Cards_NZAP_YYYYMMDD.txt</SPAN>
e.g. …\2014\05 May\ V8 Cards_NZAP_20140501.txt</SPAN>
Unique ID is not in a consistent character position, but is always a number string located between the first instance of BD, DC, or AP and the next space.
</SPAN>
As ever, all help gratefully received </SPAN></SPAN></SPAN>
What we have:
Fairly simple file structure (2 / 3 levels, with one file per day at the root of each tree)
Files are either xls or txt
Files contain daily banking records, including a unique ID
Location of unique ID is fixed (column D or whatever)
Max records / file / day is about 20,000. Usually only files from last 12 months are relevant
Issue:
Team needs to be able to search for a unique ID (usually the most recent), identify the file it's in, go to file and get some other info from the corresponding row. At the moment, they are searching each file manually until they get a hit.
What I thought would work:
Run some code (daily) to list out something of the form:
unique ID | File reference | row reference
...in a worksheet somewhere so they could search a single xl file for the matches, then maybe link of some sort to open the file they want. Probably on big initial run to populate enough history, then code would need to be updatable such that it could be run each night and have the results appended to the rest of the list / not need to interrogate the entire file lists each time.
</SPAN>Obviously, any better ideas most welcome.
Details:
</SPAN>
For xls files:</SPAN>
Directory structure is …\YYYY\MM MMM YY\DDMMYYYYCL.xls</SPAN>
e.g. …\2014\05 MAY 2014\01052014CL.xls</SPAN>
Unique ID is in col N
</SPAN>
For txt files:</SPAN>
Directory structure is …\YYYY\MM MMM\V8 Cards_NZAP_YYYYMMDD.txt</SPAN>
e.g. …\2014\05 May\ V8 Cards_NZAP_20140501.txt</SPAN>
Unique ID is not in a consistent character position, but is always a number string located between the first instance of BD, DC, or AP and the next space.
</SPAN>
As ever, all help gratefully received </SPAN></SPAN></SPAN>