View Full Version : Solved: How to get only specific information from a .txt file using macro
rafi_07max
10-29-2010, 11:51 PM
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
10-30-2010, 06:33 AM
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
10-30-2010, 07:25 AM
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
10-30-2010, 08:15 AM
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
10-31-2010, 08:41 PM
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
11-01-2010, 01:13 AM
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
11-01-2010, 03:17 AM
thanks a lot. Your help is appreciated.
Kenneth Hobs
11-01-2010, 05:45 AM
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
11-01-2010, 06:20 AM
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
11-01-2010, 06:29 AM
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
11-01-2010, 06:31 AM
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
11-01-2010, 06:47 AM
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
11-01-2010, 07:37 AM
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
11-01-2010, 08:18 AM
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
11-01-2010, 08:21 PM
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
11-02-2010, 05:51 AM
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
11-02-2010, 10:07 PM
Thanks kenneth for your help.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.