Process and Parse Delimited Text Files

[l05r28]

Hi there,

I am a beginner in Excel VBA. I would like to process and copy delimited raw data text files into Excel.
A sample of the text file is as shown.

XCHN Pos 1
Param 1 Param 2 Param 3
20 104.84 38.4
20.51 104.8 36.61
21.48 104.93 34.43
22.46 104.9 33.03
23.44 105.03 31.91

XCHN Pos 2
Param 1 Param 2 Param 3
48.83 109.12 29.44
49.8 109.19 29.06
50.78 109.11 28.47
51.76 109.13 26.55
52.73 109.35 27.34

YCHN Pos 1
Param 1 Param 2 Param 3
58.59 109.24 26.74
59.57 108.92 22.95
60.55 109.38 25.17
61.52 108.81 24.19
62.5 109.37 23.96

YCHN Pos 4
Param 1 Param 2 Param 3
67.38 109.22 25.59
68.36 109.11 23.14
69.34 109.21 24.27
70.31 109.13 22.65
71.29 109.13 23.55

I would like to do the following to the data above;

- delete the third column (Param 3 and all of the values for all 4 data sets)
- copy and transpose the values of Param 1 & Param 2 into a new target worksheet based on the string specified above 'Param'
- the result is that the values are all copied into their respective existing worksheet (i.e.: 'XCHN Pos 1', 'XCHN Pos 2', etc) starting from a specific cell and will not overwrite previous data if the VBA function is run another time with a different raw data text file


Would gladly appreciate any help I could get, thanks!

 

[awoohaw]

i am not a power query expert. But, I am pretty certain this is done easier in power query.
if you put the text files in the same folder (there can be other files there as well) with a consistent naming convention that you can use to filter in Power Query you can import them and subsequent similar files quite easily.

 

[l05r28]

i am not a power query expert. But, I am pretty certain this is done easier in power query.
if you put the text files in the same folder (there can be other files there as well) with a consistent naming convention that you can use to filter in Power Query you can import them and subsequent similar files quite easily.

thanks for the direction, but i would really prefer if i could do this using normal VBA instead if it is in any way possible

 

[l05r28]

bump