Macro to extract text from .txt file into Excel

[striker359]

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 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!

 

[tyger0951]

This to

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

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

 

[striker359]

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

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

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?

 

[tyger0951]

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?

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.

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:

For Each Cell In wsResults.Range("A1:Z6000")
 If Cell.Value = "na" Then Cell.Clear
 Next

 

[tyger0951]

Following change to code will get you the correct

notification_msg:

<tbody>
</tbody>

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?

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.

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:

For Each Cell In wsResults.Range("A1:Z6000")
 If Cell.Value = "na" Then Cell.Clear
 Next

 

[tyger0951]

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

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

From

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

 

[tyger0951]

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

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

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?

 

[tyger0951]

Sry was wrong on this

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

' 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

 

[striker359]

The codes don't seem to work... keep getting error:

This applies to the codes from B to Z

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

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

From

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

 

[tyger0951]

with these two fixes . It should solve just about everything.

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

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

From

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

Following change to code will get you the correct

notification_msg:

<tbody>
</tbody>

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

 

[tyger0951]

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

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

LastRowColA

in the

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

insert_job:

<tbody>
</tbody>