PDA

View Full Version : Need some code tweaking. To scan text files to find certain figures



X82
09-18-2012, 01:38 PM
Sub test()
Dim myDir As String, fn As String, txt As String
Dim myMax As Double, myName As String, myLoc As String, n As Long
Dim myMatch As Object, x, y, temp As String, i As Long, ii As Long
myDir = ThisWorkbook.Path & "\"
fn = Dir(myDir & "*.txt")
If fn <> "" Then
n = n + 1
Sheets(1).Cells(n, 1).Resize(, 4).Value = _
[{"FileName","Name","Location","Max"}]
With CreateObject("VBScript.RegExp")
.IgnoreCase = True
Do While fn <> ""
myName = Empty: myLoc = Empty: myMax = 0
txt = CreateObject("Scripting.FileSystemObject") _
.OpenTextFile(myDir & fn).ReadAll
.Global = True
.Pattern = "[^\n]+"
Set myMatch = .Execute(txt)
.Global = False
For i = 0 To myMatch.Count - 1
.Pattern = "\b(I(out)?Max( B1)?|Ph ?I(.A)?)(?=\t)"
If .test(myMatch(i)) Then
temp = .Execute(myMatch(i))(0)
x = Application.Match(temp, Split(myMatch(i), vbTab), 0)
myMax = Val(Split(myMatch(i + 1), vbTab)(x - 1))
Exit For
Else
.Pattern = "\b(IMax Ph1)(?=\t)"
If .test(myMatch(i)) Then
temp = .Execute(myMatch(i))(0)
x = Application.Match(temp, Split(myMatch(i), vbTab), 0)
For ii = x - 1 To x + 1
myMax = myMax + Val(Split(myMatch(i + 1), vbTab)(ii))
Next
Exit For
End If
End If
Next
.Pattern = "(\d{2}(?:[/\.])){2}\d{4}\t(\d{2}:){2}\d{2}\t[^\t]+\t([^\t]+)\t([^\t]+)"
If .test(txt) Then
myName = .Execute(txt)(0).submatches(2)
myLoc = .Execute(txt)(0).submatches(3)
End If
n = n + 1
Sheets(1).Cells(n, 1).Resize(, 4).Value = _
Array(fn, myName, myLoc, myMax)
fn = Dir
Loop
End With
Else
MsgBox "No file found"
End If
End Sub

I have a system of about 500 text files from our powerbar readings taken each night. I needed a script to run which would scan each of the text files, find a certain set of figures and put them into a spreadsheet. Above is what some kind soul did for me. It works beautifully, with one small problem which was my own. This will take you to the 4 example files of the types of file this script will scan.

docs.google.com/folder/d/0B0U_dFIXO1labHBPaXZKMWlSUGs/edit

It works perfectly to pick up all red information (which you will see in the link above). The problem is ,with File 2, it must scan for the two figures in red, and add them together. But I forgot to explain this to the creator who is now away for a long period. With his code it will only add the 2nd digits in red, 1.9 from file 2. I have now correctly highlighted the first set of figures I need, in this case 1.8. I looked at the code over and over and don't know where to begin to modify myself to do this. With this complete it would be perfect!

Any ideas how I can modify this code slightly to fix this error? Any help on this matter is greatly appreciated.

snb
09-19-2012, 12:59 AM
What do you mean with:
'find a certain set of figures'
Which set of figures ?

X82
09-19-2012, 01:18 AM
If you look at the "link" to google docs I posted, you will find 4 files. Each with a red text highlight. These are the figures that the script will "pull" from the txt files and put into a spreadsheet. However, for File 2, you will see 2 red figures. But the script is only pulling the red figure on the right, not both. Which will also need adding together. Is there a way to ammend this code to fix that issue?

snb
09-19-2012, 02:40 AM
Can you post the original file2.txt file here ?
Since you converted the file into an rtf file some characteristics of the original file may have got lost.

X82
09-22-2012, 10:05 AM
Sorry for the delay, I had to find the correct file.

Since I can't post links yet... here is the file in question.

dropbox.com/s/i7nwyxvwpkbdica/File%202.txt

The values in that file I need and then added together are 1.8 & 1.9.

However, since I am not at work for a few days, I cannot be 100% sure I need all 3 values. So 1.8 + 1.9 + 1.3.

Is it possible to modify the above script the have 2 versions? After all, I dont want you awesome people to help me with this, only for me to come back and ask for it to be changed again..

All help in this is appreciated like you wouldn't believe.

X82
09-23-2012, 04:23 PM
bump :banghead: