Results 1 to 3 of 3

Thread: Extract Numeric Values From Multiple Text Files In Folder By Scanning

  1. #1
    Junior Member
    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    Extract Numeric Values From Multiple Text Files In Folder By Scanning

    Code:
    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.

    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.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    This has got to go to someone with good hold of regular expressions. Let us hope someone here can lend some help
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    Me too. I don't mine the whole code being deleted and starting over. I've tried everything I can.
    Basically, I have about 500 .txt files. Here is a link to example files, 4 in total. The red parts of the files are the figure I need importing into excel. So there is about 500 txt files with 4 different variations of the file.

    I also would like to import the filename of the txt file in a column. So column A would read, AMAB3.txt and column B would be the figure pulled from the txt file, say 1.9 or 5.6 etc. In a long list.

    I'm sorry if I've explained this terribly and I will try to clear up any issues. All help on this matter is appreciated beyond imagination.

Similar Threads

  1. Moving Multiple Files From One Folder To Another
    By galang_ofel in forum Excel Help
    Replies: 5
    Last Post: 05-10-2013, 12:43 AM
  2. Replies: 2
    Last Post: 03-21-2013, 08:51 PM
  3. Extracting Numeric Values From Alphanumeric Text
    By Safal Shrestha in forum Excel Help
    Replies: 3
    Last Post: 03-21-2013, 12:04 PM
  4. Excel Formula To Extract Values From Text
    By Howardc in forum Excel Help
    Replies: 1
    Last Post: 08-22-2012, 09:05 PM
  5. Replies: 2
    Last Post: 02-29-2012, 08:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •