lundi 20 avril 2015

Extract string from multiple files using vba

I need to extract a 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 text file is located after a key word. The data should be placed in an excel file (every data copied 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:\prova")
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

I wrote a macro to extract the data I need from a single file (and it works fine). The keyword is Read BRT Luminance and the macro is:

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()

'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

Aucun commentaire:

Enregistrer un commentaire