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

[rafi_07max]

Attachment 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

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]

[VBA]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
[/VBA]

[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/2103daf8...1299480b1b312e

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.


Attachment 4813

[mdmackillop]

To put data in a different sheet, just state that in the code
[vba]Sheets(2).Cells(i, 1) = t [/vba]
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]

[VBA] 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[/VBA]

[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
[vba]t = a.readline [/vba]
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

[VBA]Option Compare Text[/VBA]

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:
[vba] Loop Until InStr(1, t, "RESISTOR") > 0[/vba]

or
[VBA] Loop Until InStr(1, t, "resistor", vbTextCompare) > 0
[/VBA]

[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.
[VBA]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[/VBA]

[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/show...802#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

Attachment 4821

Someone Pls help

[Kenneth Hobs]

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

[VBA]
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
[/VBA]

[rafi_07max]

Thanks kenneth for your help.