macro to import data from text file to excel worksheet

[hunna]

Hello, if it's possible to import data from notepad file as shown below to excel worksheet

###FS: #m/z: 471.93765 #charge 3
###MaxRes:#m/z: 0.00000 #charge-128
###MS: 401/
###MSMS: 400/
BEGIN IONS
TITLE= Cmpd 1, +MSn(qCID 471.93765), 10.5 min
PEPMASS=471.937648 52884640
CHARGE=3+
471.241338 1994913 1+
1043.610977 869664 1+
1243.691115 1882480 1+
1300.713939 3368120 1+
1413.796382 2822475 1+
END IONS

###FS: #m/z: 784.05656 #charge 3
###MaxRes:#m/z: 0.00000 #charge-128
###MS: 402/405/
###MSMS: 403/407/
BEGIN IONS
TITLE= Cmpd 2, +MSn(qCID 784.05658), 10.6 min
PEPMASS=784.056557 52754752
CHARGE=3+
832.440915 316937 1+
2021.019465 394193 1+
2333.134355 291084 1+
2350.152226 260149 1+
END IONS

###FS: #m/z: 601.77318 #charge 4
###MaxRes:#m/z: 0.00000 #charge-128
###MS: 405/
###MSMS: 406/408/
BEGIN IONS
TITLE= Cmpd 3, +MSn(qCID 601.77319), 10.7 min
PEPMASS=601.773181 19070720
CHARGE=4+
646.341532 898599 1+
2404.067064 365217 1+
END IONS

In excel worksheet I want to keep only the red part (column A is m/z and D is charge number)

A            B           C    D
471.9377    0           0    3
471.2413    1994913     1+    
1043.611    869664      1+    
1243.691    1882480     1+    
1300.714    3368120     1+    
1413.796    2822475     1+    
            
784.0566    0           0    3
832.4409    316937      1+    
2021.019    394193      1+    
2333.134    291084      1+    
2350.152    260149      1+    
            
601.7732    0           0    4
646.3415    898599      1+    
2404.067    365217      1+

I have a lot of data so I need macro to automate this work.

Any help would be appreciated.

[mohanvijay]

try this

[vba]


Dim O_Fso As Object
Dim Txt_File As Object
Dim File_Path As String
Dim T_Str As String, Str_Ma4 As String
Dim T_Bool As Boolean
Dim Hld_Data As Variant
Dim Ma_H_Boo As Boolean
Dim Ctr As Long
Dim i As Long
Const LL_Cha As String = "CHARGE="
Const LL_End As String = "END IONS"
Const LL_Pep As String = "PEPMASS="
T_Bool = False
Ma_H_Boo = False
Ctr = 0
i = 1
File_Path = "C:\Documents and Settings\User\Desktop\VBAA\tt.txt" 'change to your text file path

Set O_Fso = CreateObject("Scripting.FileSystemObject")
Set Txt_File = O_Fso.OpenTextFile(File_Path)
With Txt_File
Do Until .AtEndOfStream = True
T_Str = .ReadLine

If T_Str = LL_End Then
T_Bool = False
Ctr = 0
End If

If InStr(T_Str, LL_Pep) <> 0 Then Ma_H_Boo = True

If Ma_H_Boo Then
Hld_Data = Split(T_Str, Chr$(9))
i = i + 1
Cells(i, 1).Value = Replace(CStr(Hld_Data(0)), LL_Pep, "")
Cells(i, 2).Value = 0
Cells(i, 3).Value = 0
i = i + 1
Ma_H_Boo = False
End If

If T_Bool Then
Ctr = Ctr + 1
Hld_Data = Split(T_Str, Chr$(9))

Cells(i, 1).Value = Hld_Data(0)
Cells(i, 2).Value = Hld_Data(1)
Cells(i, 3).Value = Hld_Data(2)

If Ctr = 1 Then
Cells(i - 1, 4).Value = Str_Ma4
End If
i = i + 1



End If

If InStr(T_Str, LL_Cha) <> 0 Then
T_Bool = True
Hld_Data = Split(T_Str, "=")
Str_Ma4 = CStr(Hld_Data(1))
Str_Ma4 = Replace(Str_Ma4, "+", "")
End If

Loop
End With
Txt_File.Close
Set Txt_File = Nothing
Set O_Fso = Nothing

[/vba]

[hunna]

@ mohanvijay

omg, thank you very much !!!! your code works exactly what I want.

[hunna]

if I need to change the range(column) that I want to add data, how can I change it?

Thanks,

[mohanvijay]

change the following code

 
Cells(i, Change column number that you want ).Value

[hunna]

Thank you again!!!