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!

 

[striker359]

Ahh.... I see... thanks for the heads up!!

Ok now it's about some minor adjustments..

Note that in the test txt, job "dummy3" did not have the send_notifications nor max_runs categories.

Is it possible to code such that if there is no existence of the category in the job, to leave a "" value inside the cell?

And I notice that (number) becomes -number in Excel. Is this solvable with an if condition where if a number enclosed by brackets would be given a '(number) so that the value would retain it's bracket enclosure?

Another option is to change the code :

[COLOR=#333333][I]If Cell.Value = Range("JobName").Value Then[/I][/COLOR]

to something like

[COLOR=#333333][I]If Cell.Value = "insert_job:"   Then[/I][/COLOR]

 

[striker359]

Oh btw tyger,

I used this code to solve the JobName issue:

lastRowCOlA = Sheets("Results").Range("A65536").End(xlUp).Row    
    If Cell.Value = "insert_job:" Or Cell.Value = "update_job:" Then
          JobName = Cell.Offset(0, 1).Value
         Sheets("Results").Range("A" & lastRowCOlA + 1).Value = JobName
    End If

 

[tyger0951]

 lastRowCOlA = Sheets("Results").Range("A65536").End(xlUp).Row
    If Cell.Value = Range("JobName").Value Then
          JobName = Cell.Offset(0, 1).Value

Add

  If JobName =  "" then 
               JobName = "n/a" 
             End if

Quickest i can think of . then Write code to change /delete cell that contains n/a

 

[tyger0951]

Only used and before. Good to know or works also . Good job

Oh btw tyger,

I used this code to solve the JobName issue:

lastRowCOlA = Sheets("Results").Range("A65536").End(xlUp).Row    
    If Cell.Value = "insert_job:" Or Cell.Value = "update_job:" Then
          JobName = Cell.Offset(0, 1).Value
         Sheets("Results").Range("A" & lastRowCOlA + 1).Value = JobName
    End If

 

[tyger0951]

I dont understand.

Ahh.... I see... thanks for the heads up!!

And I notice that (number) becomes -number in Excel. Is this solvable with an if condition where if a number enclosed by brackets would be given a '(number) so that the value would retain it's bracket enclosure?

If i get it. Change .value to .text on the items that are not numbers.

 

[striker359]

If you typed (3019) into an Excel cell, the output would become -3019.

This is what's happening currently with the extraction.

Was wondering if it's possible to create an IF condition such that if you had (3019) or any other number enclosed in brackets, a ' would be affixed to the start of the string so that the output becomes (3019) instead of -3019.

I dont understand.




If i get it. Change .value to .text on the items that are not numbers.

 

[striker359]

I was thinking of

if(len(JobName.Value) <1 then JobName = ""

(i'm not sure how to translate this into vba though)

 lastRowCOlA = Sheets("Results").Range("A65536").End(xlUp).Row
    If Cell.Value = Range("JobName").Value Then
          JobName = Cell.Offset(0, 1).Value

Add

  If JobName =  "" then 
               JobName = "n/a" 
             End if

Quickest i can think of . then Write code to change /delete cell that contains n/a

 

[tyger0951]

Cant be done like that in code. If u set it in code to "" then its pasting a blank then the code cant find the next cell for the next job name. Just as before work to get data then manipulate it. Use the code to input na when blank then afterwards change na on results sheets to blanks....baby steps one thing at a time in code. Cant cram everything into on line of code.

 

[tyger0951]

Amazing video set :

Excel VBA Introduction: Excel VBA Introduction - YouTube

If you got free time I'd suggest watching them all.

 

[striker359]

How about " " ? Would that be a workaround?

Cant be done like that in code. If u set it in code to "" then its pasting a blank then the code cant find the next cell for the next job name. Just as before work to get data then manipulate it. Use the code to input na when blank then afterwards change na on results sheets to blanks....baby steps one thing at a time in code. Cant cram everything into on line of code.