Loop through txt doc , store values for use in vba

[Martynrbell]

hi all,
Just a question of possibility first of all.


I have a .txt file that contains two sets of data.
The sets always start with DXS and end with DXE.

First of all, I would like excel to read the .txt and take only the data between DXS and DXE and dump them side by side 1 line per row in Excel.

Secondly, is it possible for Excel to loop through each column and store values, For example
I will have data that looks like this

PA1*1*30
PA2*1*5*1*5
PA1*3*30
PA2*2*10*5*10

I need excel to read this and create an Array type Variable based on the PA1 and then store the data for example:

(i have used apa1 for data set 1 and would use bpa1 for data set 2)
APA1*1 should store 1,30,1,5,1,5
APA1*2 should store 3,30,2,10,5,10

The PA1* can number from 0 or be a value like A1 and may differ per file

I would also like to store other values from the .txt file

such as
VA1*249219*1236*249219*1236
VA3*0*4238*0*4238

however in this situation i would need it to be like the following

AVA1 should store 249219,1236,249219,1236
AVA3 should store 0,4238,0,4238.

once the txt has been read and values stored i need to be able to use them in the code for example

AVA1(2)-BVA1(2)

I have listed an example of the data below.

once the code has finished running, the values can be cleared from the store for a fresh set of data to be loaded.

If this kind of thing is possible i would be grateful for someone to help me out and buff my limited VBA skills

Thanks




DXS*9252131001*VA*V1/1*1
ST*001*0001
CA1*:::3403G110077*CF690*200*11177
CA2*249079*1232*249079*1232
CA3*479290*600*478690*0*479290*600*478690*0
CA4*119240*2584053614*119240*362350
CA8*8149*8149
CA9*247249*15844817
CA10*0*0
CA11*10*94*0*94*94*0*94
CA11*20*45*0*45*45*0*45
CA11*50*681*2048*681*681*0*681
CA11*100*4428*0*4428*4428*0*4428
CA11*200*3*3*0*3*3*0
CA11*0*0*0*0*56832*0*0
CA13*0*0*2281765376
CA13*0*0*54272
CA13*0*0*35328
CA13*0*0*41
CA13*0*0*4653056
CA14*0*0*0*125*0
CA15*1200*0
DA1*00000000000*00000000000*0*
DA2*367*4*140*4
DA3*140*140
DA4*1200*1200
EA3*0*000000*0000*DEXTERM*000000*0000*
EA4*000000*0000*
EA5*000000*0000***35
EA6*000000*0000*
EA7*285*285
ID1*MEI::::::::::::::**0***
ID4*2*41
PA1*1*30
PA2*0*0*0*0
PA4*0*0*0*0
TA2*0*0*0*0
TA3*0*0
TA4*0*0*0*0*0*0*0*0
TA5*0*0
TA6*0*0*0*0
VA1*249219*1236*249219*1236
VA3*0*4238*0*4238
AM1*:::3403G110077*CF690*V2.00
G85*5812
SE*1*0001
DXE*1*1

 

[Martynrbell]

Re: Tell me if this is possible , loop through txt doc , store values for use in vba

Going down a different route with this ... dont know how to delete thread