Extract data between specific lines of txt to worksheet in VBA

Regex

Enable Regex in Excel VBA

Standard

AsimpleRegexthatidentifiesagroupthatisbetweenMWTTanDeltaValues=andMWTTanDeltaTimeValues=:

MWTTanDeltaValues=\s*([\s\S]+)(?=MWTTanDeltaTimeValues=)

Demo on Regex101

VBA Code

Find txt file and remove string

Finds the file defined or if it does not find the user chooses the file. Opens the .txt and finds the desired String.

Sub EncontrarTXT()

Dim objStream As Object
Dim strData As String
Dim fileName As String, textData As String, fileNo As Integer
sFilename = "teste.txt"
'    sFilename = Sheets("Planilha1").Range("A1")
sFilepath = ThisWorkbook.Path & "\" & sFilename
fileNo = FreeFile                            'Get first free file number


Inicio:
If Dir(sFilepath) <> "" Then
    Open sFilepath For Input As #fileNo
    strData = Input$(LOF(fileNo), fileNo)

    Dim objMatches As Object, objRegExp As Object
    Set objRegExp = CreateObject("VBScript.RegExp")
    'Regex https://regex101.com/r/gXOEV9/1
    objRegExp.Pattern = "MWTTanDeltaValues=\s*([\s\S]+)(?=MWTTanDeltaTimeValues=)"
    objRegExp.Global = True

    Set objMatches = objRegExp.Execute(strData)
    If objMatches.Count <> 0 Then

        For Each m In objMatches
            'Imprime na janela de Verificação Imediata
'                Debug.Print m.Submatches(0)
            'Preenche a célula A1 da planilha Planilha1
'                Sheets("Planilha1").Range("A1") = m.Submatches(0)
        Next m
    End If
Else
    MsgBox "O arquivo txt não pôde ser carregado - Escolha o caminho."
    sFilepath = EscolherArquivo
    If Dir(sFilepath) <> "" Then GoTo Inicio
End If
'Close
Close #fileNo

End Sub

Choose File

If you do not find the path mentioned in the code, a window will open to choose the file in the system.

Public Function EscolherArquivo() As String
'Créditos: http://software-solutions-online.com/excel-vba-open-file-dialog/
Dim intChoice As Long
Dim strPath As String

'only allow the user to select one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
    'get the file path selected by the user
    strPath = Application.FileDialog( _
              msoFileDialogOpen).SelectedItems(1)
    'print the file path to sheet 1
    EscolherArquivo = strPath
End If
End Function

Result

This String is:

27.5766;27.5707;27.3737;26.6112;26.0126;26.2416;26.1120; 26.1621;25.7420;25.9710;25.6238;25.7683;25.8689;26.1269; 26.1321;26.2643;25.7848;25.1501;25.3091;25.0000;25.3175; 25.5920;24.8733;24.6167;24.6299;24.7430;25.4183;25.9896; 25.4958;25.4259;26.4650;25.7657;30.0259;30.5261;30.2207; 30.7683;30.5524;31.4316;30.5092;31.2188;31.3513;31.4804; 31.1870;31.5287;31.2671;30.7482;29.5514;28.6546;29.6851; 29.2009;29.2151;29.1309;29.1466;33.0232;31.8877;30.4890; 26.8053;27.0559;26.8480;25.6997;25.8613;26.7863;26.0611; 26.7878;27.2462;25.6071;25.9075;25.9302;25.8017;26.8502; 26.7850;26.3517;25.5865;26.1033;25.8408;26.2310;25.0309; 23.9557;24.0468;24.0217;23.7751;24.5628;24.3670;24.3429; 25.5378;27.6765;24.4876;24.7278;23.9403;

Explanation

• sFilename = "teste.txt"

String that defines the file name

• sFilepath = ThisWorkbook.Path & "\" & sFilename

String that defines the file path, where is the concatenation of the current file path of Excel & the name of the file.

• Inicio:

Tag the beginning of the code

• If Dir(sFilepath) <> "" Then

If the directory set to sFilepath is found, follow the code to open the txt and extract the String.

• Else

Otherwise ...

• sFilepath = EscolherArquivo

The user chooses the file to be used by calling the function EscolherArquivo

• GoTo Inicio

After choosing the file, it returns to Inicio:

• Open sFilepath For Input As #fileNo

Open the txt file

• strData = Input$(LOF(fileNo), fileNo)

Defines the string strData with the data of the txt file.

• Dim objMatches As Object, objRegExp As Object: Set objRegExp = CreateObject("VBScript.RegExp"):'Regex https://regex101.com/r/gXOEV9/1 : objRegExp.Pattern = "MWTTanDeltaValues=\s*([\s\S]+)(?=MWTTanDeltaTimeValues=)" : objRegExp.Global = True

Defines the parameters of Regex.

• Set objMatches = objRegExp.Execute(strData)

Run Regex on String strData

• If objMatches.Count <> 0 Then

If any result of the regex is found, then ...

• For Each m In objMatches: Next m

For each match found in Regex

• Debug.Print m.Submatches(0)

Immediate Check Group 1 Match

• Sheets("Planilha1").Range("A1") = m.Submatches(0)

Fill in cell A1 of worksheet Sheet1