Extract string from multiple files using vba

Question

I need to extract data from a set of text files inside a folder. I tried several times without success, I hope that someone can help me.

All the files I have to read are inside the folder C:/test. The data I need to extract from the text files is located after the key word Read BRT Luminance. The data should be placed in an excel file, every data extracted from a single text file inside a different cell.

I tried with this macro, but it doesn't work:

Dim myFile As String, myFolder As String, text As String, textline As String, originatorName As String, entryDescription As String, amount As Long

Sub Button1_Click()

    Dim fs, f, f1, fc
    Dim cella

    cella = A2

    'Add column headers
    Range("A1").Value = "Brightness"

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder("C:\test")
    Set fc = f.Files
    For Each f1 In fc
        If InStr(1, f1.Name, ".txt") Then

            'Open file
            Open f1 For Input As #1

            Do Until EOF(1)
                Line Input #1, textline
                text = text & textline
            Loop

            'Close file
            Close #1

            ReadBRTLuminance = InStr(text, "Read BRT Luminance")

            ActiveCell.Offset(cella, 1).Value = Mid(text, ReadBRTLuminance + 31, 9)
            cella = cella + 1

        End If
    Next
End Sub

My macro to extract the data I need from a single file works fine:

Dim myFile As String, myFolder As String, text As String, textline As String, originatorName As String, entryDescription As String
Dim amount As Long

Sub Button1_Click()
    'Add column headers
    Range("A1").Value = "Brightness"

    'Show open file dialog box
    myFile = Application.GetOpenFilename()

    'Open file
    Open myFile For Input As #1

    Do Until EOF(1)
        Line Input #1, textline
        text = text & textline
    Loop

    'Close file
    Close #1

    ReadBRTLuminance = InStr(text, "Read BRT Luminance")

    Range("A2").Value = Mid(text, ReadBRTLuminance + 31, 9)

End Sub

Dim cella, cella = A2 and cella = cella + 1 does seem strange to me as you use it in an Offset, so it's suppose to be an integer, and if you add to many files, maybe a long. Try Dim cella as Integer and cella = 2 And when you post a code that doesn't work, please specify your error message and the line on which it happen. — R3uK
– R3uK, Commented Apr 20, 2015 at 13:58
Yes, I have changed the folder to C:/prova. I don't receive any error message. I have twenty files in my folder, and my script seems to read the same file 20 times, because I obtai a file with the same value repeated twenty times in twenty different cells — madman
– madman, Commented Apr 20, 2015 at 14:22

Tim Williams · Accepted Answer · 2015-04-20 16:23:36Z

3

You're not clearing the value of text between files, so that's why you always get the value from the first file...

answered Apr 20, 2015 at 16:23

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract string from multiple files using vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related