Extract data from text file to Excel

Question

I am currently working on a data extraction project. The program will prompt user to enter the number of text files they want to extract. Afterwards, the user will select the files they want. However, the data extracted is only based on the first text file selected. How do I go about solving this issue.

Private Sub CommandButton1_Click()
    Dim fileStringBasic As String
    Dim i As Integer
    Dim k As Integer
    Dim iFile As Integer: iFile = FreeFile
    k = InputBox("Give me some input")

    For i = 1 To k
        fileStringBasic = Application.GetOpenFilename()

        If fileStringBasic <> "False" Then
            Open fileStringBasic For Input As #iFile
            Do Until EOF(iFile)
                Line Input #iFile, textline
                Text = Text & textline
            Loop

            Close #iFile
            pos1 = InStr(Text, "Datalog report")
            pos2 = InStr(Text, "BOARD PN")
            pos3 = InStr(Text, "BOARD SN")
            pos4 = InStr(Text, "TESTER")
            pos5 = InStr(Text, "DEVICE")
            pos6 = InStr(Text, "USER NAME")
            Range("A" & ActiveCell.Row).Value = Mid(Text, pos1 + 18, 11)
            Range("B" & ActiveCell.Row).Value = Mid(Text, pos2 + 18, 10)
            Range("D" & ActiveCell.Row).Value = Mid(Text, pos3 + 18, 8)
            Range("E" & ActiveCell.Row).Value = Mid(Text, pos4 + 18, 9)
            Range("F" & ActiveCell.Row).Value = Mid(Text, pos5 + 18, 11)
            Range("H" & ActiveCell.Row).Value = Mid(Text, pos6 + 18, 11)
        End If
        Selection.Offset(1, 0).Select
    Next i
End Sub

patel · Accepted Answer · 2019-03-08 09:17:21Z

1

you have to clear the variable text at beginning of the loop

For i = 1 To k
    Text = ""
    fileStringBasic = Application.GetOpenFilename()

answered Mar 8, 2019 at 9:17

patel

4401 gold badge4 silver badges9 bronze badges

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

1 Comment

Daniel Teo Over a year ago

Hi, patel thank you so much for the help. The program works perfectly now!

Collectives™ on Stack Overflow

Extract data from text file to Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related