Read part of a txt file and copy values into excel

[Craig12345]

Hi All,

need to search a txt file which will "always" be in this format:

MCB # 2 ACQ 26-May-06 at 8:13:07 RT = 7201.0 LT = 7200.0
SEG # 1 ALPHA_1
No sample description was entered
ROI# RANGE( keV) GROSS NET +/- CENTROID FWHM FW.1M LIBRARY ( keV) Bq +/-
1 5044.46 5252.55 450 300 26 5207.31 15.97 60.45 No close library match.
2 5272.36 5480.44 954 943 31 5402.95 42.41 87.99 No close library match.

.......i need to get the date, and the gross and net values for each entry, this file has 2 entires 1 and 2, but sometimes there is a third. I have got some code which asks for the file, and a seperator, then prints everything to an excel sheet using the seperator to decide what goes in each cell, however this is not what i want, I just want the 3 items i mentioned above,e.g date gross and net values. Im using a macro on the excel file to open the txt file and extract the contents. I just dont know how to get just what i want, not the whole lot.

Thx, craig

[Guido Geurs]

Hi All,

need to search a txt file which will "always" be in this format:

MCB # 2 ACQ 26-May-06 at 8:13:07 RT = 7201.0 LT = 7200.0
SEG # 1 ALPHA_1
No sample description was entered
ROI# RANGE( keV) GROSS NET +/- CENTROID FWHM FW.1M LIBRARY ( keV) Bq +/-
1 5044.46 5252.55 450 300 26 5207.31 15.97 60.45 No close library match.
2 5272.36 5480.44 954 943 31 5402.95 42.41 87.99 No close library match.

.......i need to get the date, and the gross and net values for each entry, this file has 2 entires 1 and 2, but sometimes there is a third. I have got some code which asks for the file, and a seperator, then prints everything to an excel sheet using the seperator to decide what goes in each cell, however this is not what i want, I just want the 3 items i mentioned above,e.g date gross and net values. Im using a macro on the excel file to open the txt file and extract the contents. I just dont know how to get just what i want, not the whole lot.

Thx, craig

Dear,

Are the 3 data always on the same place in the textfile?
Is it possible to set the 3 data Bold in your example ? you want to extract date + ?? + ??

br,

[Craig12345]

Dear,

Are the 3 data always on the same place in the textfile?
Is it possible to set the 3 data Bold in your example ? you want to extract date + ?? + ??

br,

Hi ggeu,

Yes the file data will always be in the same place on the txt file.
I have bolded the data i want to extract above.

However sometimes there may be a third line of data e.g.

MCB # 2 ACQ 26-May-06 at 8:13:07 RT = 7201.0 LT = 7200.0
SEG # 1 ALPHA_1
No sample description was entered
ROI# RANGE( keV) GROSS NET +/- CENTROID FWHM FW.1M LIBRARY ( keV) Bq +/-
1 5044.46 5252.55 450 300 26 5207.31 15.97 60.45 No close library match.
2 5272.36 5480.44 954 943 31 5402.95 42.41 87.99 No close library match.
3 5272.36 5480.44 650 291 31 5402.95 42.41 87.99 No close library match.

...so i would also need the code to check if it was extracting 2 or 3 data lines.

Thx in advance for any help.
Craig

P.S. I have been trying to use the Line Input function and the mid() function but its not working correctly.

[Guido Geurs]

Dear,

I have used arrays to solve it. (see attachment).
How it works:
1- read the file into an array.
2- split the 1e line on SPACE and look for the DATE.
3- split the Data lines 2 or 3 in an array with 2 var's: GROSS and NET
4- dump the data in the worksheet.

PS:
I have changed the data in the testfiles (Data2.txt and data3.txt) to 2xx and 3xx for checking if the right data is loaded !

I hope this will help you.

br,

[Craig12345]

Dear,

I have used arrays to solve it. (see attachment).
How it works:
1- read the file into an array.
2- split the 1e line on SPACE and look for the DATE.
3- split the Data lines 2 or 3 in an array with 2 var's: GROSS and NET
4- dump the data in the worksheet.

PS:
I have changed the data in the testfiles (Data2.txt and data3.txt) to 2xx and 3xx for checking if the right data is loaded !

I hope this will help you.

br,

Thx ggeu,

Your solution has helped me :)

Regards,
Craig