extract specific rows of .tbl files in excel-CodePudding

I have an excel file with the following links:

These links are connected to files with the following data:

I want the yellow part of the notepad file to be read into the .xlsx file yellow parts (the notepad is an opened version of the .tbl file). The dotted parts differ for each Version number. (This code is used as a check that the right discount curve is used). However, the discount_curve.tbl format is the only format the next programme used is able to handle. Therefore, it has the same name just in a different folder.

Is there a way excel/vba can read in every third line whilst the file read in depends on the folder link? I strongly prefer to have the whole process automated since there are many many version numbers. Furthermore, I do not want to change the file formatting, since I want the process to be as clean as possible.

Could someone help me out? Kind regards.

CodePudding user response：

Please, try the next function, if the necessary data to be extracted exists in a single file, at every three rows.. It will return a 2D array able to be dropped at once in the range you need:

Function extractThirdLine(filePath As String) As Variant
     Dim arrTxt, i As Long, arrFin, k As Long
     'read the file content in an array:
     arrTxt = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(filePath, 1).ReadAll, vbCrLf)
     ReDim arrFin(1 To Int(UBound(arrTxt) / 3)   1, 1 To 1)
     For i = 2 To UBound(arrTxt) Step 3 'start from 2, because  arrTxt is 1D array
        k = k   1
        arrFin(k, 1) = arrTxt(i) 'build the filal array containing the necessary rows
     Next i
     extractThirdLine = arrFin
End Function

Your picture does not show the rows and columns headers. So, supposing that the range you show us exists in columns "A:C" and you need to place the extracted data in column "D:D", please use the next way:

Sub testExtractThirdLine()
   Dim filePath As String, arrVal, el
   filePath = "your text file full name" 'please write here the correct file  name
   arrVal = extractThirdLine(filePath)
   Range("D1").Resize(UBound(arrVal), 1).value = arrVal
End Sub

If the range you show is not the one I supposed, you cam easily adapt Range("D1") to the immediately after the columns range and its row to be the first row of the range in discussion.

If something not clear enough, please do not hesitate to ask for clarifications.

Edited:

But if each third line can be found in a file, for each row, and the path to the respective file is obtained by concatenation of the three columns, the next function will do the job:

Function extractLine(filePath As String) As String
   extractLine = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(filePath, 1).ReadAll, vbCrLf)(2)
End Function

It can be called as:

Sub extractStrings()
 Dim i As Long, arr, arrFin, lastRow As Long
 
 lastRow = Range("A" & rows.count).End(xlUp).Row 'supposing that 'C:\' exists in A:A column
 arr = Range("A2:C" & lastRow).value
 ReDim arrFin(1 To UBound(arr), 1 To 1)
 
 For i = 1 To UBound(arr)
    arrFin(i, 1) = extractLine(arr(i, 1) & arr(i, 2) & arr(i, 3))
 Next i
 'drop the processed array content at once:
 Range("D2").Resize(UBound(arrFin), 1).value = arrFin
End Sub

CodePudding user response：

Seems like you're looking for common I/O opearations i.e. reading file line by line. Pretty good example was shown [here][1]

To reach your goal we need to add some if-conditions to extract every third line of your text files. Modulo division will be a good helper. For example we have 'i' as row number then we just need to make an if condition looks smth like that:

If (i mod 3) = 0 Then ...

It means that we're looking for every 'i' which divided by 3 gives us a remainder of 0 This way our code will look something like this

Sub ReadFileLineByLine()
Dim my_file As Integer
Dim text_line As String
Dim file_name As String
Dim i As Integer   

file_name = "C:\text_file.txt"   

my_file = FreeFile()
Open file_name For Input As my_file   

i = 1   

While Not EOF(my_file)
    Line Input #my_file, text_line
    If (i mod 3) = 0 Then
        Cells(i, "A").Value = text_line
    End If
    i = i   1
Wend

End Sub

[1]: https://excel.officetuts.net/vba/read-a-text-file/#:~:text=Reading a file line by line,-Let's read text&text=Open VBA Edit (Alt + F11,and insert the following code.&text=First, a new file is,places it inside a worksheet.

CodePudding user response：

You can create a User function that will read the lines from the given file and return the third one.

Here is such a function (Disclaimer: there is no error management in this code it can probably be improved a lot)

Function Get3rdLine(filename As String)
    Dim f As Long
    f = FreeFile
    Open filename For Input As f
    Line Input #f, Get3rdLine ' just ignore this line
    Line Input #f, Get3rdLine ' and this one too
    Line Input #f, Get3rdLine ' and return this one
    Close #f
End Function

You can call it with the path of the file you want to read from:

=Get3rdLine(CONCATENATE(A1,B1,C1)) for example if your path is defined by cells A1, B1 and C1.