PDA

View Full Version : Extracting data from .txt file to excel using vba



Mylleri
08-22-2018, 03:11 PM
I am trying to extract some data from a .txt file to a sheet in excel. Tried a few different basic approaches from what i could find searching the web but now I am stuck.

The .txt file and the excel file are always in the same folder and the text file are always named resultat.txt.
The .txt file always have the same general appearance:

Three tables starting with program, result and step.
The table "program" starts one empty row from the top and the following tables are separated by one empty row.
The data is separated by spaces and the decimal separator is a dot (.).
The number of entries in the different tables will change (both colums and rows) so "hard coding"(?) what to extract isn't an option.

My goal would be to type some kind of macro to search the .txt file, find out what row "step" is on (the third table) and then extract that row and every following row until the end of the .txt file to a sheet in excel keeping the data in separate cells of course.

I would like the macro to format the extracted data to be decimal separated by comma (,) instead of dot (.) and showing one decimal.
and to get rid of minuses making negative number positive
and show the values extracted and formatted starting at the active cell in excel.


When the .txt file is updated i would like to run the macro again showing the new data beginning at the active cell (which will probably be one blank row below the previous entry).

Example .txt (get error message trying to upload my .txt file):



program 1 2 3 4 5 6 7 8
1 1.000 2.000 3.000 4.000 5.000 6.000 7.000 8.000
2 350.000 350.000 350.000 350.000 350.000 350.000 350.000 350.000


result 1 2 3 4 5 6 7 8
1 500.892 511.405 518.509 525.790 533.260 441.499 452.343 460.456
2 546.337 545.381 544.621 543.843 543.047 554.339 553.222 552.196
3 -4816.672 -4817.538 -4818.342 -4819.164 -4820.004 -4806.113 -4807.212 -4808.310

step 1 2 3 4 5 6 7 8 9 10
1 1.000 350.000 945.000 1500.000 -1300.000 1.000 1.000 1.000 1.000 1.000
2 2.000 350.000 945.000 1500.000 -1300.000 1.000 1.000 1.000 1.000 1.000





I hope someone can help me get this macro running =)