Macro to extract text from .txt file into Excel

striker359

Board Regular
Joined
Jun 19, 2014
Messages
64
Hi,

I'm completely new to macro building but here goes:

I would like to extract info from an external txt file into Excel. For example:

txt file:

/* ----------------- Dummy ------------------- */
JobName: Dummy
send_notifications: y
max_runs: 5


/* ----------------- TAF-O-FF401D ------------------- */
JobName: TAF-O-FF401D
send_notifications: y
max_runs: 5

I would require a way to read the item category (JobName, send_notifcations, max_runs), for lack of a better term, and then to populate the corresponding info into specific columns in Excel.

For example:

JobName (Dummy) in Column A
send_notifications (y) in Column B
max_runs (5) in Column C

With the /* ----------------- JobName ------------------- */ indicating a new job entry, I would require the next job entry to be on the next row and so on till there are no more job entries left.

Thanks in advance!
 
Last edited:
This to

Code:
I = I + 1


LastRowColU = wsResults.Range("U65536").End(xlUp).Row
                   If Cell.Value = wsOptions.Range("A" & I).Value Then
              Note = Cell.Offset(0, 1).Value
                   wsResults.Range("U" & LastRowColA + 1).Value = Note
                End If





Code:
I = I + 1


LastRowColU = wsResults.Range("U65536").End(xlUp).Row
                   If Cell.Value = wsOptions.Range("A" & I).Value Then
              Note = Cell.Offset(0, 1).Value
                   wsResults.Range("U" & LastRowColA + 1).Offset(-1, 0).Value = Note
                End If

For this you'll have to change the code to have an offset(-1,0) so it goes up one row
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
For this you'll have to change the code to have an offset(-1,0) so it goes up one row

So I suppose in this case, it would be from

Code:
I = I + 1


LastRowColp = wsResults.Range("P65536").End(xlUp).Row
                   If Cell.Value = wsOptions.Range("A" & I).Value Then
              Note = Cell.Offset([COLOR=#ff0000]0[/COLOR], 1).Value
                   wsResults.Range("P" & LastRowColp + 1).Value = Note
                End If


To


Code:
I = I + 1


LastRowColp = wsResults.Range("P65536").End(xlUp).Row
                   If Cell.Value = wsOptions.Range("A" & I).Value Then
              Note = Cell.Offset([COLOR=#ff0000]-1[/COLOR], 1).Value
                   wsResults.Range("P" & LastRowColp + 1).Value = Note
                End If

Right?
 
Upvote 0
The first code inserts NA in all cells in column A of the pullsheet so that the loops don't mistake a blank cell as a marker to stop. The second does the same to column b. Both column a and b data is being extracted from and it makes it easier for the code to perform its tasks if na is in a blank rather than a blank.



the third removes any NA 's that were pasted into the results sheets.


These three peieces of code makes it possible for your BLANK data fields to appear in the final results sheet


Right now there are 2 main problems I've encountered:

1. When there's a space in the data, only the first word is extracted. (I understand that it's caused by the space delimiter, but when the space delimiter is turned off, nothing gets extracted.)

2. The data printed occasionally end up in the wrong row (aka data from one job is printed onto the row of another job instead).


PS. Is this supposed to be created for every column?

Code:
For Each Cell In Range("B1:B6000")
If Cell.Value = "" Then
Cell.Value = "na"
End If
Next

If so, I've made it to become like that and as expected, it took a way longer time to finish executing because of the greater range involved.

Code:
For Each Cell In Range("A1:AA6000")
If Cell.Value = "" Then
Cell.Value = "na"
End If
Next


Even so, I failed to notice any difference between the 2 codes, so I was wondering what this code actually did, considering the fact that I saw no "na" even when i removed this code:

Code:
For Each Cell In wsResults.Range("A1:Z6000")
 If Cell.Value = "na" Then Cell.Clear
 Next
 
Upvote 0
Following change to code will get you the correct [TABLE="width: 223"]
<tbody>[TR]
[TD="class: xl63, width: 223"]notification_msg:[/TD]
[/TR]
</tbody>[/TABLE]



Code:
I = I + 1


LastRowColN = wsResults.Range("N65536").End(xlUp).Row
                   If Cell.Value = wsOptions.Range("A" & I).Value Then
                   If Cell.Offset(0, 2).Value = "na" Then
                   Cell.Offset(0, 2).Clear
                   End If
                   If Cell.Offset(0, 3).Value = "na" Then
                   Cell.Offset(0, 3).Clear
                   End If
              Note = Cell.Offset(0, 1).Value & " " & Cell.Offset(0, 2).Value & " " & Cell.Offset(0, 3).Value
                   wsResults.Range("N" & LastRowColA + 1).Offset(-1, 0).Value = Note
                End If




Right now there are 2 main problems I've encountered:

1. When there's a space in the data, only the first word is extracted. (I understand that it's caused by the space delimiter, but when the space delimiter is turned off, nothing gets extracted.)

2. The data printed occasionally end up in the wrong row (aka data from one job is printed onto the row of another job instead).


PS. Is this supposed to be created for every column?

Code:
For Each Cell In Range("B1:B6000")
If Cell.Value = "" Then
Cell.Value = "na"
End If
Next

If so, I've made it to become like that and as expected, it took a way longer time to finish executing because of the greater range involved.

Code:
For Each Cell In Range("A1:AA6000")
If Cell.Value = "" Then
Cell.Value = "na"
End If
Next


Even so, I failed to notice any difference between the 2 codes, so I was wondering what this code actually did, considering the fact that I saw no "na" even when i removed this code:

Code:
For Each Cell In wsResults.Range("A1:Z6000")
 If Cell.Value = "na" Then Cell.Clear
 Next
 
Upvote 0
Code:
wsResults.Range(" " & LastRowColA + 1).Offset(-1, 0).Value = Note

Probably should update all code to look like the above. For letters b-z

From

Code:
wsResults.Range(" " & LastRowColA + 1).Value = Note
 
Upvote 0
No

Offset(-1,0)

Must watch. Excel VBA Introduction Part 5 - Selecting Cells (…: http://youtu.be/c8reU-H1PKQ

So I suppose in this case, it would be from

Code:
I = I + 1


LastRowColp = wsResults.Range("P65536").End(xlUp).Row
                   If Cell.Value = wsOptions.Range("A" & I).Value Then
              Note = Cell.Offset([COLOR=#ff0000]0[/COLOR], 1).Value
                   wsResults.Range("P" & LastRowColp + 1).Value = Note
                End If


To


Code:
I = I + 1


LastRowColp = wsResults.Range("P65536").End(xlUp).Row
                   If Cell.Value = wsOptions.Range("A" & I).Value Then
              Note = Cell.Offset([COLOR=#ff0000]-1[/COLOR], 1).Value
                   wsResults.Range("P" & LastRowColp + 1).Value = Note
                End If

Right?
 
Last edited:
Upvote 0
Sry was wrong on this


Code:
I = I + 1LastRowColp = wsResults.Range("P65536").End(xlUp).Row                   If Cell.Value = wsOptions.Range("A" & I).Value Then              Note = Cell.Offset([COLOR=#ff0000]0[/COLOR], 1).Value                   wsResults.Range("P" & LastRowColp + 1).Value = Note                End If

should be



Code:
' Correct code 

[COLOR=#333333][FONT=monospace]I = I + 1

LastRowColp = wsResults.Range("P65536").End(xlUp).Row

If Cell.Value = wsOptions.Range("A" & I).Value Then             

 Note=Cell.Offset([/FONT][/COLOR][COLOR=#ff0000][FONT=monospace]0[/FONT][/COLOR][COLOR=#333333][FONT=monospace], 1).Value  

 wsResults.Range("P" & LastRowColp + 1).offset(-1,0).Value = Note           

     End If[/FONT][/COLOR][COLOR=#333333][FONT=monospace]
[/FONT][/COLOR]




No

Offset(-1,0)

Must watch. Excel VBA Introduction Part 5 - Selecting Cells (…: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) - YouTube
 
Last edited:
Upvote 0
The codes don't seem to work... keep getting error:

nDIeAN.jpg


u7rKK1.jpg


This applies to the codes from B to Z

Code:
wsResults.Range(" " & LastRowColA + 1).Offset(-1, 0).Value = Note

Probably should update all code to look like the above. For letters b-z

From

Code:
wsResults.Range(" " & LastRowColA + 1).Value = Note
 
Upvote 0
with these two fixes . It should solve just about everything.

Code:
wsResults.Range(" " & LastRowColA + 1).Offset(-1, 0).Value = Note

Probably should update all code to look like the above. For letters b-z

From

Code:
wsResults.Range(" " & LastRowColA + 1).Value = Note

Following change to code will get you the correct [TABLE="width: 223"]
<tbody>[TR]
[TD="class: xl63, width: 223"]notification_msg:[/TD]
[/TR]
</tbody>[/TABLE]



Code:
I = I + 1


LastRowColN = wsResults.Range("N65536").End(xlUp).Row
                   If Cell.Value = wsOptions.Range("A" & I).Value Then
                   If Cell.Offset(0, 2).Value = "na" Then
                   Cell.Offset(0, 2).Clear
                   End If
                   If Cell.Offset(0, 3).Value = "na" Then
                   Cell.Offset(0, 3).Clear
                   End If
              Note = Cell.Offset(0, 1).Value & " " & Cell.Offset(0, 2).Value & " " & Cell.Offset(0, 3).Value
                   wsResults.Range("N" & LastRowColA + 1).Offset(-1, 0).Value = Note
                End If
 
Upvote 0
Code:
wsResults.Range("B" & LastRowColA +1).Offset(-1, 0).Value = Note


'Change to  


wsResults.Range("B" & LastRowColA + 1).Value = Note



wsResults.Range("C" & LastRowColA +1).Offset(-1, 0).Value = Note

'change to 


wsResults.Range("C" & LastRowColA + 1).Value = Note


And do so for Letters B - V


There should not be a set of


Code:
I = I + 1




LastRowColW = wsResults.Range("W65536").End(xlUp).Row
                   If Cell.Value = wsOptions.Range("A" & I).Value Then
              Note = Cell.Offset(0, 1).Value
                   wsResults.Range("W" & LastRowColA + 1).Offset(-1, 0).Value = Note
                End If

Because W is reserved for JOB_Type and thats done in a whole different code below all those toward the end of the code



Make sure your using
Code:
LastRowColA
in the
Code:
wsResults.Range("ALL LETTERS B - V" & LastRowColA + 1).Offset(-1, 0).Value = Note


This makes sure to put data into next open cell based off the last cell in column A will data in it then the offset goes up one cell and places NOTE . This ensures that the data is put in correct spot. it references column A because every entry has a [TABLE="width: 171"]
<tbody>[TR]
[TD="class: xl65, width: 171"]insert_job:[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top