copy specific from a text file to excel sheet using vba

[alex gomes]

I am trying to extract certain data from a text file(Source) into an Excel spreadsheet
I want copy the value from these two following line
TestTime Avg: 74.9451
TestTime StdDev: 3.1523


Attached sample txt file and required output excel file

Really appreciate your help!!!!

 

[Hui]

Alex

Firstly, Welcome to the Chandoo.org Forums

I have made an assumption that the data is always in the same format and the two lines you want are always the last two lines

If the above is true, then

Sub Sample()
  Dim MyData As String, strData() As String
  Dim tArr As Variant
   
  Open "C:\Users\mypcname\Desktop\Source.txt" For Binary As #1
  MyData = Space$(LOF(1))
  Get #1, , MyData
  Close #1
  strData() = Split(MyData, vbCrLf)
   
  tArr = Split(strData(UBound(strData, 1) - 1), " ")
  Range("A1") = tArr(2)
  tArr = Split(strData(UBound(strData, 1)), " ")
  Range("A2") = tArr(2)
   
End Sub

Will work

If not let me know

 

[alex gomes]

Thanks for your reply

No actual file is like this only,please go though the attached doc.
I want to copy every value in the line which is starting with
TestTime Avg:
TestTime StdDev:
there are multiple lines which is starting with these two

 

[Marc L]

Based on poor explanation, just mod this demonstration to your need :​

Sub Demo()
    V = ThisWorkbook.Path & "\Source.txt"
    If Dir(V) = "" Then Beep: Exit Sub
    F% = FreeFile
    Open V For Input As #F
    V = Filter(Filter(Split(Input(LOF(F), #F), vbNewLine), vbTab), "TestTime")
    Close #F
    If UBound(V) < 0 Then Beep: Exit Sub
    ReDim VA#(0 To UBound(V), 0)
    For F = 0 To UBound(V):  VA(F, 0) = Val(Split(V(F), vbTab)(1)):  Next
    With Worksheets(1).Cells(1)
        .CurrentRegion.Clear
        .Resize(F).Value = VA
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Deepak]

This might be explanation where same is posted by alex gomes in a new thread .

http://chandoo.org/forum/threads/co...-file-to-another-excel-sheet-using-vba.28930/

 

[alex gomes]

Thanks For the reply And sorry deepak for creating new thread

This is the code i modified, But this is working partially

Sub Demo()
  V = ThisWorkbook.Path & "C:\Users\subhajit\Desktop\data.txt"
  If Dir(V) = "" Then Beep: Exit Sub
  F% = FreeFile
  Open V For Input As #F
  V = Filter(Filter(Split(Input(LOF(F), #F), vbNewLine), vbTab), "TestTime Avg")
  Close #F
  If UBound(V) < 0 Then Beep: Exit Sub
  ReDim VA#(0 To UBound(V), 0)
  For F = 0 To UBound(V):  VA(F, 0) = Val(Split(V(F), vbTab)(1)):  Next
  With Worksheets(1).Cells(1)
  .CurrentRegion.Clear
  .Resize(F).Value = VA
  End With
  V = ThisWorkbook.Path & "C:\Users\subhajit\Desktop\data.txt"
  If Dir(V) = "" Then Beep: Exit Sub
  F% = FreeFile
  Open V For Input As #F
  V = Filter(Filter(Split(Input(LOF(F), #F), vbNewLine), vbTab), "TestTime StdDev")
  Close #F
  If UBound(V) < 0 Then Beep: Exit Sub
  ReDim VA#(0 To UBound(V), 0)
  For F = 0 To UBound(V):  VA(F, 0) = Val(Split(V(F), vbTab)(1)):  Next
  With Worksheets(1).Cells(2)
  'CurrentRegion.Clear = False
  .Resize(F).Value = VA
  End With
End Sub

Actual_Result.xlsx is what i am getting after running this code
but what i want is Expected_Result.xlsx

Plz go through the attached documents
Plz Help!!

 

[Marc L]

First, initial post must be crystal clear and complete …

If you ask for piece of a global need, it will take ages !
​

Sub Demo()
    V = ThisWorkbook.Path & "\Source.txt"
    If Dir(V) = "" Then Beep: Exit Sub
    F% = FreeFile
    Open V For Input As #F
    V = Filter(Filter(Split(Input(LOF(F), #F), vbNewLine), vbTab), "TestTime")
    Close #F
    If UBound(V) < 0 Then Beep: Exit Sub
    ReDim VA#(1 To (UBound(V) + 1) \ 2, 1 To 2)
    For F = 1 To UBound(V) Step 2
        If Not V(F) Like "*[#]IND" Then
                  L% = L% + 1
            VA(L, 1) = Val(Split(V(F - 1), vbTab)(1))
            VA(L, 2) = Val(Split(V(F), vbTab)(1))
        End If
    Next
    With Worksheets(1).Cells(1)
        .CurrentRegion.Clear
        .Resize(L, 2).Value = VA
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[alex gomes]

Sorry for the Unclear Question
And Thanks for the Reply