I need some VBA code that will scan through a specified folder and all sub-folders looking at all found text (TXT) files.
The code needs to read (parse) the files and import the transaction value data into a single Excel worksheet for all the files.
The text file contains some garbage information, so need to ignore all this and only import the transaction lines.
The data dates are in short form, so need to get the year from earlier in the file (where the date is shown in full with the year). This is in the same searchable place in every file.
Some statements run from DEC to JAN, so need to take care to +1 the year if this condition.
Rows need to be as follows:
Statement Filename, Statement Item No (added sequentially), Transaction Date, Processing Date, Description, Amount, Foreign Currency, Foreign Currency Amount
(The last 2 rows are optional and only apply when the transaction is in a foreign currency).
(Rows 1 and 2 are added automatically - not based on the text file data. These will be used for sorting.)
Example statement follows. Some details changed and details snipped.
Statement of xxxx xxxxx
12 OCT THANK YOU-PAYMENT THROUGH ABANK
123425XXXXXX1234 CHARLES DICKENS
123425XXXXXX1245 CHARLES DICKENS
HOME PRODUCT CENTER (PLOEYORK
DFT FLOATS S P/L - FLOSINGAPORE SG
Bu iş için 13 freelancer ortalamada $60 teklif veriyor
Hi, I am experienced on Microsoft Access , Excel and VBA, Powerpoint. I can start your job immediately also. Please contact me to discuss the project in details. Thanks
Hello, I am independent, experienced excel expert. I can help with this task with quick turnaorund. Looking to hearing from you. Kind regards Rina B.