Retreiving data from a fixed-width (non delimited) Flat-File based on criteria...

[Klelvin]

Hi,

Firstly, I'm wondering if this is possible in Excel VBA (2007 Windows xp):

I have a number of flat-files (no extension, but can convert them to txt if its easier) which contain account records in the following format:

TITLE of the FILE
001CRTA845461230022 NAME Address Amount
001CRTB898654230022 NAME Address Amount

for each record. The columns are split by fixed width (not tab / space delimited).

I need to have some VBA that will go through the file and firstly count the number of lines (which I can then minus 1 from to find out the number of records). Then Return the total of the amounts for all records. Then return the total of the amounts for all records with CRTA in the first field, then the total of the amounts for all records with CRTB in the first field.

Is this possible? And what do you think the best way to go about it would be? (I'm quite happy just to return the values to A1, B1, C1 etc)

I'm not necessarily asking for a coded solution, just some indication of posibility and how.

Thanks in advance

Owen

 

[Jon von der Heyden]

Hi Owen:

Total amount for all records:
=SUM(D:D)

Total for CRTA:
=SUMIF(A:A,"*CRTA*",D:D)

Total for CRTB:
=SUMIF(A:A,"*CRTB*",D:D)

 

[Klelvin]

Thanks Jon,

Unfortunately my formatting was removed from the above post and I didnt realise.

The data in the "columns" in the flat file is split by anything from 6 - 30 spaces (as opposed to the 1 space shown above) depending on the length of the address. This means that on an import, you have to manually choose text to columns and fixed width as a seperator before you get the data. Unfortunately the fixed width will then vary based on length of name and address.

I will be experimenting with importing the data and auto manipulation (but due to spaces in the name and address field any delimiting by spaces doesnt work either) but I would really like it to read from the file itself if possible.

Thanks again.

OWen