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]

Just realized posted the previous post wrong

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

should just be this:

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

becasue This

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

is adding 1 to LastRowColA then the offset is subtracting one.

Think of LasRowColA is a 2
the code is doing 2+1 and gets 3 then offset is 3-1 and still gives you two.


Or A2 + 1 = A3 then offest does A3 -1 = A2


so This code is shorter

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

Letters B-V go in the " " in the code. just showing the format. The letter needs to be in each " " .

Make sure to sure to update all the LastRowCol? to LastRowColA for each letter B -V code sets.

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>

 

[tyger0951]

Crtl + H and enter


+ 1).Offset(-1, 0) for find and replace with ) should work fast

or

depending on what code you've updated

+1) for the FIND and ) for the replace.

Don't use replace all incase it shows up in places you don't want to change but using find next then replace when it gets to the one you need should work faster than a copy paste method.

 

[Naed1]

tyger,

I often would like to go the other way, to output text from a .xlm Spreadsheet to a text file using VBA. The way I presently do this is to open a new Excel File, copy and paste or use the Range("Range").Value function to get the text I want to the new spreadsheet and then save the new spreadsheet as a text file. Is there another simple way of doing this without opening and then saving a New Excel worksheet as text.

Thanks,

 

[tyger0951]

There are several different methods out there. I've seen on that is like the reverse of a query where you build the txt file row by row and column by bolumn but thats normally if the data is gonna pulled back out or used in various different ways. I actually do use the method you mentiond already. I have to save txt files of a file I find on a HTML site. What i have the site do is copy and paste the whole webpage into a new workbook then save as txt.

A way to speed things up or less windows open is to use

 application.displayalerts = false

when closing or deleteing the new workbook/worksheet.

Application.DisplayAlerts = False ' turns off pop  ups  to avoid having to press ok when spreadsheet is closed/deleted 
workbook.Close false  ' The false enters do not save as default since you already saved as txt  
Application.DisplayAlerts = True  ' turns back on pop ups

Theres also the method of using .innertext and Write to copy the innertext of an html site and can store the info then Write it directly to a txt file.Haven't messed around with this one too much . I'll take a stab at it after a bit and post some code later

 

[tyger0951]

Best reponse I can ultimately have to this is . It all depends on what you plan on doing with the data later. I currently use the method you mentioned and it works great for me because the Txt file being saved is only a back up file of Chat logs. So the data is never brought back into excel. If this methood works for you and just want to clearn up a bit turning off some application settings and setting .close False to choose not save when closing work books would work great.

If the data needs to be reused though may want to use a method of breaking data from excel into a txt file. Using for statements. Excel VBA Programming - Write To A Text File

tyger,

I often would like to go the other way, to output text from a .xlm Spreadsheet to a text file using VBA. The way I presently do this is to open a new Excel File, copy and paste or use the Range("Range").Value function to get the text I want to the new spreadsheet and then save the new spreadsheet as a text file. Is there another simple way of doing this without opening and then saving a New Excel worksheet as text.

Thanks,