Macro to iterate through values in a column

[joand]

The macro below extracts information from a text file and pastes the data to cell D7. How do I change this so that: (1) it extracts information from multiple text files residing in C:\MyReports folder (the filename + path is stored in column G, starting in row 7); (2) pastes the data to column D, starting in row 7.

Sub GetInfoFromTextFile()
Dim strLine As String
Dim strLineOut As String
Dim i As Long
   
    With CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\MyReports\sample.txt")
        strLine = .ReadAll
        .Close
    End With
    
    With CreateObject("VBScript.RegExp")
        .Global = False
        .MultiLine = True
        .Pattern = "(<date)(.+)(/>)"
        
        If .Test(strLine) Then
            strLine = .Execute(strLine)(0)
            For i = 1 To Len(strLine)
                If IsNumeric(Mid(strLine, i, 1)) Then
                    strLineOut = strLineOut & Mid(strLine, i, 1)
                End If
            Next
            Range("D7").Value = CLng(strLineOut)
        End If
    End With
End Sub

 

[jim may]

Sub MyTextFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\Users\James\Documents\ExcelChest"
MyFile = Dir(MyFolder & "\*.txt")
Do While MyFile <> ""

    [COLOR="Red"]'Your Code here[/COLOR]

MyFile = Dir
Loop
End Sub

 

[joand]

thanks for the reply jim but how do you exactly change this?

Range("D7").Value = CLng(strLineOut)

 

[jim may]

Sorry, I havn't a Clue...

 

[joand]

The filename and path for each of the text file in C:\MyReports folder are listed in G column, e.g. G7 = C:\MyReports\Sample1.txt, G8 = C:\MyReports\Sample2.txt, and so on. The macro opens each text file and extracts the data (supposedly) to cell D7 (sample1.txt), cell D8 (sample2.txt), cell D9 (sample3.txt), and so on and so forth. My problem is I don't know how to iterate the row number such that it places the value to D7, then D8. All it does is pastes the value to cell D7 which is hard-coded in the code. Would appreciate your help.

 

[jim may]

You say,

"and extracts the data (supposedly) to cell D7 (sample1.txt)"

Do you mean you want to extract the data in cell D7 of Sample1.txt (and so on..) and
place the value in your Activeworkbook in a set of sequential cells downward?

 

[jim may]

Try modifying your code as follows...

Dim j as Long
j = 7

If .Test(strLine) Then
strLine = .Execute(strLine)(0)
For i = 1 To Len(strLine)
If IsNumeric(Mid(strLine, i, 1)) Then
strLineOut = strLineOut & Mid(strLine, i, 1)
End If
Next
Cells(j, 4).Value = CLng(strLineOut)
j = j + 1
End If