Solved: How to get only specific information from a .txt file using macro

[rafi_07max]

4802

From the picture above we can see some information in a .txt file. In this txt file there are 1000 over lines and most of them I not needed. So what I want my macro to do is that, once I opened the .txt file it should

1. Look for the word “RESISTOR” and extract all the information that is written after the word RESISTOR”, and

2. Stops once it see the word “NODES”, which means all the information after the word “NODES” should not be included on my spreadsheet when I open the file.

Below is a sample of how my spreadsheet should look like once I click the button and open the txt file.

http://www.iimmgg.com/image/205d30e1...1686fbd097afd3 (http://www.iimmgg.com/image/205d30e1bb16e0a6b41686fbd097afd3)

This is the codes I used to open the txt file, after that I don’t know how to continue.



Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
myFile = Application.GetOpenFilename("Text Files,*.txt")
Workbooks.OpenText Filename:= _
myFile, Origin _
:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array _
(4, 1), Array(10, 1), Array(26, 1), Array(27, 1), Array(50, 1), Array(58, 1)), _
TrailingMinusNumbers:=True
ActiveSheet.Move After:=Workbooks("retriveFile.xls").Sheets(1)
ActiveWindow.WindowState = xlMaximized
Exit Sub
ErrorHandler:
MsgBox "Plese select a file", vbInformation, "unable to continue" '& Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

[stanleydgrom]

rafi_07max,

You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense and I doubt that you would get any answer.

Please attach your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet. This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

To attach your workbook (containing the command button and the data from the text file) click on the Post Reply button, then scroll down and click on the Manage Attachments button.

[Kenneth Hobs]

Like Stan said, it is easier to help with data. So, attach a TXT file and the XLSM in a ZIP file. Based on your picture and your code, there appears to be a conflict.

The solution in any case is easy.

While it is proper to start a new thread for a problem that might relate to a solved problem, it is best to add a link to your previous post.

[mdmackillop]

Option Explicit
Option Compare Text

Sub Test()
Dim fs, a
Dim t As String
Dim MyFile As String
Dim i As Long

MyFile = Application.GetOpenFilename("Text Files,*.txt")

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.openTextFile(MyFile)

Do
t = a.readline
Loop Until InStr(1, t, "resistor") > 0

i = 1
Cells(i, 1) = t
t = a.readline

Do
i = i + 1
Cells(i, 1) = t
t = a.readline
Loop Until InStr(1, t, "nodes") > 0

a.Close

End Sub

[rafi_07max]

Thanks mdmackillop for your help. But can u modify the same program such that, check the pic on the below website
http://www.iimmgg.com/image/2103daf86937a1553e1299480b1b312e

From the picture, I have drawn a rectangle around resistor. So what I want the macro to do is that only include the information that is surrounded by the rectangle and discard all the other information on the right of the rectangle.


Another thing is that once I click the button and open the file, the information of the file is stored at the same sheet as the button (see attachment below). How can I make it such as the txt file information is not it in the same sheets as the button (e.g. if the button is in sheet 1, then the file I open shouldn’t be in sheet 1.


I have attached a sample txt file and workbook. In the sheet1 of the workbook there is a button with your codes and its result, in sheet2 is what I expecting to get.


4813

[mdmackillop]

To put data in a different sheet, just state that in the code
Sheets(2).Cells(i, 1) = t
If you only want the first column of data, record a macro using use Text to Columns in the Data menu to split this off

[rafi_07max]

thanks a lot. Your help is appreciated.

[Kenneth Hobs]

Private Sub CommandButton1_Click()
Dim fs, a
Dim t As String
Dim MyFile As String
Dim i As Long

MyFile = Application.GetOpenFilename("Text Files,*.txt")

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.openTextFile(MyFile)

Do
t = a.readline
Loop Until InStr(1, t, "resistor") > 0

i = 1
Sheet2.Cells(i, 1).Value = t
t = a.readline

Do
i = i + 1
Sheet2.Cells(i, 1).Value = Split(t)(2)
t = a.readline
Loop Until t = ""

a.Close
Set fs = Nothing
Set a = Nothing
End Sub

[rafi_07max]

Thanks for your help Kenneth Hobs but it didn't work for me.When i tried open the file it showed the following error,

Run time error '62:
Input past end of the file

and when i clicked debug, it highlighted the following code
t = a.readline
i tried your codes in both excel 2007 and 2002 both showed the same error

[mdmackillop]

t = a.readline

This line occurs 3 times; on which one does it fail? I would anticipate this error if your code does not contain the Resistor and Nodes lines. Are they both in your text file, in the expected order?

[rafi_07max]

Actually Kenneth your codes does work.But i just need to add

Option Compare Text

at the top of your codes to prevent the error.

[Kenneth Hobs]

Right, I was just building on the fine work already done.

You can leave that option out if you use:
Loop Until InStr(1, t, "RESISTOR") > 0

or
Loop Until InStr(1, t, "resistor", vbTextCompare) > 0

[Kenneth Hobs]

I don't generally use fso for file reading so this is the route that I would have probably used to do it.
Sub Resistor()
Dim fn As Integer, MyFile, i As Long, t As String, tf As Boolean

MyFile = Application.GetOpenFilename("Text Files,*.txt")
If MyFile = False Then Exit Sub

fn = FreeFile
Open MyFile For Input As #fn
Do While Not EOF(fn)
Line Input #fn, t

If tf Then
If t = "" Then Exit Do
i = i + 1
Sheet2.Cells(i, 1).Value = Split(t)(2)
End If

If InStr(1, t, "RESISTOR") > 0 Then
tf = True
i = i + 1
Sheet2.Cells(i, 1).Value = t
End If
Loop
Close fn
End Sub

[rafi_07max]

Thanks a lot Kenneth for your codes. When you are free do take a look at my latest thread and see if you could provide any help.

http://www.vbaexpress.com/forum/showthread.php?p=228802#post228802

Thank you have a nice day.

[rafi_07max]

i wanted mofify this prgram such as when i open the file it should create a new sheet after the the sheet where the button is.
i came up with this code by myself and it was not successful. It did opened the .txt file in a new sheet but it did not stop after the word "Nodes", instead it continue until the end of the .txt file



Option Explicit
Option Compare Text

Sub Test()
Dim fs, a
Dim t As String
Dim MyFile As String
Dim i As Long
Dim wb As Workbook: Set wb = ThisWorkbook

MyFile = Application.GetOpenFilename("Text Files,*.txt")
If MyFile = "False" Then Exit Sub
Workbooks.OpenText Filename:=MyFile, Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1), _
TrailingMinusNumbers:=True

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.openTextFile(MyFile)


Do
t = a.readline
Loop Until InStr(1, t, "Resistor") > 0


i = 1
Cells(i, 1) = t

t = a.readline
Do
i = i + 1
Cells(i, 1) = t
t = a.readline

Loop Until InStr(1, t, "nodes") > 0

ActiveSheet.Move After:=wb.Sheets(wb.Sheets.Count)

a.Close
end sub


i have attached the files i used

4821

Someone Pls help

[Kenneth Hobs]

Use False rather than "False". Why use OpenText? You inserted the sheet after everything was done.


Sub Resistor()
Dim fn As Integer, MyFile, i As Long, t As String, tf As Boolean

MyFile = Application.GetOpenFilename("Text Files,*.txt")
If MyFile = False Then Exit Sub

Sheets.Add after:=ActiveSheet

fn = FreeFile
Open MyFile For Input As #fn
Do While Not EOF(fn)
Line Input #fn, t

If tf Then
If t = "" Then Exit Do
i = i + 1
ActiveSheet.Cells(i, 1).Value = Split(t)(2)
End If

If InStr(1, t, "RESISTOR") > 0 Then
tf = True
i = i + 1
ActiveSheet.Cells(i, 1).Value = t
End If
Loop
Close fn
End Sub

[rafi_07max]

Thanks kenneth for your help.