How to merge files?

[mrdigitalis]

Dear Formmembers,

I am new to VBA and wrote some code to open 2 files:
1. Mutation file (tab delimited)
2. EXP file (semicolon delimited)

Here is my code so far:

Sub MergeMutaties()

    Dim Mutatie_File As Variant
    Dim wsMaster1 As Worksheet
    Dim wbTextImport1 As Workbook

    Dim EXP_File As Variant
    Dim wsMaster2 As Worksheet
    Dim wbTextImport2 As Workbook

    Application.ScreenUpdating = False

    'Code om Mutatie-file te openen als text
    Mutatie_File = Application.GetOpenFilename(Title:="Selecteer de Mutatie-file ...", FileFilter:="Mutatie Files (*.txt*), *txt*")
    If Mutatie_File = False Then
        MsgBox "No file selected."
    Else
        Workbooks.OpenText _
                    Filename:=Mutatie_File, _
                    FieldInfo:=Array( _
                      Array(1, xlTextFormat), _
                      Array(2, xlTextFormat), _
                      Array(3, xlTextFormat), _
                      Array(4, xlTextFormat), _
                      Array(5, xlTextFormat), _
                      Array(6, xlTextFormat), _
                      Array(7, xlTextFormat), _
                      Array(8, xlTextFormat) _
                      ), _
                    StartRow:=1, _
                    DataType:=xlDelimited, _
                    Tab:=True
                    
        Set wbTextImport1 = ActiveWorkbook
        Set wsMaster1 = ThisWorkbook.Worksheets("Mutatie_File")

        wbTextImport1.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster1.Range("A1")
        
        wbTextImport1.Close False
    End If

    'Code om EXP-file te openen als text
    EXP_File = Application.GetOpenFilename(Title:="Selecteer de EXP-file ...", FileFilter:="EXP Files (*.exp*), *exp*")
    If EXP_File = False Then
        MsgBox "No file selected."
    Else
        Workbooks.OpenText _
                    Filename:=EXP_File, _
                    FieldInfo:=Array( _
                      Array(1, xlTextFormat), _
                      Array(2, xlTextFormat), _
                      Array(3, xlTextFormat), _
                      Array(4, xlTextFormat), _
                      Array(5, xlTextFormat), _
                      Array(6, xlTextFormat), _
                      Array(7, xlTextFormat), _
                      Array(8, xlTextFormat), _
                      Array(9, xlTextFormat), _
                      Array(10, xlTextFormat), _
                      Array(11, xlTextFormat) _
                      ), _
                    StartRow:=1, _
                    DataType:=xlDelimited, _
                    Tab:=False, _
                    Semicolon:=True
                    
                    
        Set wbTextImport2 = ActiveWorkbook
        Set wsMaster2 = ThisWorkbook.Worksheets("EXP_File")

        wbTextImport2.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster2.Range("A1")
        
        wbTextImport2.Close False
    End If

I have now three sheet tabs (Merge_Result / Mutation_File / EXP_File)
Here is a fragment of the EXP file:

Ingangsdatum:	21/07/2023
0000100	xxx	V	101	5:10	12:52	7h42
		Op/Afstaptijd		5:10	xxx	xxx	5:13
		Prep-out		5:13	xxx	xxx	5:15
MAT		1800101	56239	5:15	xxx	xxx	5:38
0	0000	1800101	1501	5:38	xxx	xxx	6:27
MAT		1800101	2233	6:27	xxx	xxx	6:29
0	0000	1800101	1510	6:33	xxx	xxx	7:00
		Loop/Reis		7:00	xxx	xxx	7:01
		Pauze		7:01	xxx	xxx	7:21
		Loop/Reis		7:21	xxx	xxx	7:22
0	0000	1800111	1511	7:22	xxx	xxx	7:53
0	0000	1800111	1505	7:55	xxx	xxx	8:14
0	0000	1800111	1507	8:26	xxx	xxx	8:35
0	0000	1800111	1508	8:35	xxx	xxx	8:43
0	0000	1800111	1510	8:53	xxx	xxx	9:16
0	0000	1800111	1538	9:18	xxx	xxx	9:43
		Loop/Reis		9:43	xxx	xxx	9:44
		Pauze		9:44	xxx	xxx	10:15
		Loop/Reis		10:15	xxx	xxx	10:16
0	0000	1800127	1544	10:16	xxx	xxx	10:47
MAT		1800127	55795	10:47	xxx	xxx	10:53
0	0000	1800127	1545	11:09	xxx	xxx	12:13
0	0000	1800127	1523	12:15	xxx	xxx	12:34
MAT		1800127	54674	12:34	xxx	xxx	12:48
		Prep-in		12:48	xxx	xxx	12:50
		Op/Afstaptijd		12:50	xxx	xxx	12:52
0000100	xxx	T	102	12:04	xxx	xxx
		Op/Afstaptijd		12:04	xxx	xxx	12:07
		Prep-out		12:07	xxx	xxx	12:09
MAT		1800515	54570	12:09	xxx	xxx	12:23
0	0000	1800515	1524	12:23	xxx	xxx	12:45
0	0000	1800515	1582	12:47	xxx	xxx	13:12
				13:12	xxx	xxx	13:23
0	0000	1800204	1539	13:23	xxx	xxx	13:50
0	0000	1800204	1548	13:56	xxx	xxx	14:47
0	0000	1800204	1555	14:56	xxx	xxx	15:20
		Loop/Reis		15:20	xxx	xxx	15:21
		Pauze		15:21	xxx	xxx	16:07
		Loop/Reis		16:07	xxx	xxx	16:10
		1800140		16:10	xxx	xxx	19:50
MAT		1800140	56367	19:50	xxx	xxx	19:56
		Prep-in		19:56	xxx	xxx	19:58
		Op/Afstaptijd		19:58	xxx	xxx	20:00
0000100	xxx	V	103	5:14	xxx	xxx
		Op/Afstaptijd		5:14	xxx	xxx	5:17
		Prep-out		5:17	xxx	xxx	5:19
MAT		1800102	57071	5:19	xxx	xxx	5:54
0	0000	1800102	V8002	5:54	xxx	xxx	6:22
		Pauze		6:22	xxx	xxx	6:50
0	0000	1800102	V8009	6:50	xxx	xxx	8:27
0	0000	1800102	V8018	8:33	xxx	xxx	10:05
0	0000	1800102	V8020	10:07	xxx	xxx	10:26
MAT		1800102	57072	10:26	xxx	xxx	10:29
		Loop/Reis		10:29	xxx	xxx	10:32
		Pauze		10:32	xxx	xxx	11:07
		Loop/Reis		11:07	xxx	xxx	11:08
0	0000	1800506	1039	11:08	xxx	xxx	11:31
0	0000	1800506	1048	11:41	xxx	xxx	12:40
0	0000	1800506	1057	12:45	xxx	xxx	13:20
	0000	Loop/Reis	127	13:23	xxx	xxx	13:29
		Op/Afstaptijd		13:29	xxx	xxx	13:31
0000100	xxx	T	104	12:09	xxx	xxx
		Op/Afstaptijd		12:09	xxx	xxx	12:12
		Prep-out		12:12	xxx	xxx	12:14
MAT		1800210	56297	12:14	xxx	xxx	12:20
0	0000	1800210	1628	12:20	xxx	xxx	13:10
0	0000	1800210	1629	13:12	xxx	xxx	13:48
MAT		1800210	56299	13:48	xxx	xxx	14:14
MAT		1800210	56300	14:27	xxx	xxx	14:29
0	0000	1800210	2032	14:29	xxx	xxx	14:58
0	0000	1800210	2035	15:00	xxx	xxx	15:26
MAT		1800210	56365	15:26	xxx	xxx	15:29
		Loop/Reis		15:29	xxx	xxx	15:32
		Pauze		15:32	xxx	xxx	16:07
		Loop/Reis		16:07	xxx	xxx	16:10
		1800210		16:10	xxx	xxx	19:50
MAT		1800210	56364	19:50	xxx	xxx	19:56
		Prep-in		19:56	xxx	xxx	19:58
		Op/Afstaptijd		19:58	xxx	xxx	20:00
0000100	xxx	V	105	5:17	xxx	xxx
		Op/Afstaptijd		5:17	xxx	xxx	5:20
		Prep-out		5:20	xxx	xxx	5:22
MAT		1800103	54406	5:22	xxx	xxx	5:36
0	0000	1800103	1001	5:36	xxx	xxx	6:24
0	0000	1800103	1006	6:34	xxx	xxx	7:25
0	0000	1800103	1015	7:33	xxx	xxx	8:05
		Loop/Reis		8:05	xxx	xxx	8:06
		Pauze		8:06	xxx	xxx	8:31
		Loop/Reis		8:31	xxx	xxx	8:32
0	0000	1800119	1524	8:32	xxx	xxx	9:02
0	0000	1800119	1529	9:08	xxx	xxx	10:00
MAT		1800119	2243	10:00	xxx	xxx	10:02
0	0000	1800119	1548	10:04	xxx	xxx	10:29
		Loop/Reis		10:29	xxx	xxx	10:30
		Pauze		10:30	xxx	xxx	10:52
		Loop/Reis		10:52	xxx	xxx	10:53
0	0000	1800504	1037	10:53	xxx	xxx	11:16
0	0000	1800504	1046	11:26	xxx	xxx	12:25
0	0000	1800504	1055	12:30	xxx	xxx	13:05
	0000	Loop/Reis	126	13:08	xxx	xxx	13:14
		Op/Afstaptijd		13:14	xxx	xxx	13:16
0000100	xxx	T	106	12:26	xxx	xxx
		Op/Afstaptijd		12:26	xxx	xxx	12:29
		Loop/Reis		12:29	xxx	xxx	12:30
0	0000	1800148	1523	12:30	xxx	xxx	12:42
0	0000	1800148	1517	12:49	xxx	xxx	13:25
0	0000	1800148	1520	13:27	xxx	xxx	14:04
MAT		1800148	56398	14:04	xxx	xxx	14:07
		Loop/Reis		14:07	xxx	xxx	14:10
		Pauze		14:10	xxx	xxx	14:32
		Loop/Reis		14:32	xxx	xxx	14:35
MAT		1800148	56399	14:35	xxx	xxx	15:01
0	0000	1800148	1638	15:01	xxx	xxx	15:44
0	0000	1800148	1639	15:46	xxx	xxx	16:22
0	0000	1800148	1644	16:31	xxx	xxx	17:14
0	0000	1800148	1645	17:16	xxx	xxx	17:50
MAT		1800148	57020	17:50	xxx	xxx	18:16
		Loop/Reis		18:16	xxx	xxx	18:19
		Pauze		18:19	xxx	xxx	18:35
		Loop/Reis		18:35	xxx	xxx	18:37
MAT		1800148	57021	18:37	xxx	xxx	18:40
0	0000	1800148	73	18:40	xxx	xxx	19:00
0	0000	1800148	74	19:03	xxx	xxx	19:24
MAT		1800148	56288	19:24	xxx	xxx	19:27
MAT		1800148	56289	19:38	xxx	xxx	19:40
0	0000	1800148	85	19:40	xxx	xxx	20:00
0	0000	1800148	86	20:03	xxx	xxx	20:24
MAT		1800148	55840	20:24	xxx	xxx	20:30
		Prep-in		20:30	xxx	xxx	20:32
		Op/Afstaptijd		20:32	xxx	xxx	20:34
0000100	xxx	V	107	5:19	xxx	xxx
		Op/Afstaptijd		5:19	xxx	xxx	5:22
		Prep-out		5:22	xxx	xxx	5:24
MAT		1800104	54585	5:24	xxx	xxx	5:36
0	0000	1800104	1502	5:36	xxx	xxx	6:24
0	0000	1800104	1509	6:38	xxx	xxx	7:30
MAT		1800104	2228	7:30	xxx	xxx	7:32
5	0000	1800104	1518	7:34	xxx	xxx	7:59
		Loop/Reis		7:59	xxx	xxx	8:00
		Pauze		8:00	xxx	xxx	8:21
		Loop/Reis		8:21	xxx	xxx	8:22
0	0000	1800136	1519	8:22	xxx	xxx	8:53
0	0000	1800136	1509	8:55	xxx	xxx	9:14
0	0000	1800136	1511	9:26	xxx	xxx	9:35
0	0000	1800136	1512	9:35	xxx	xxx	9:43
0	0000	1800136	1514	9:54	xxx	xxx	10:16
0	0000	1800136	1550	10:18	xxx	xxx	10:43
		Loop/Reis		10:43	xxx	xxx	10:44
		Pauze		10:44	xxx	xxx	11:11
		Loop/Reis		11:11	xxx	xxx	11:14
MAT		1800102	57073	11:14	xxx	xxx	11:16
0	0000	1800102	1522	11:16	xxx	xxx	11:57
0	0000	1800102	1520	11:59	xxx	xxx	12:29
0	0000	1800102	1521	12:31	xxx	xxx	12:59
0	0000	1800102	1527	13:01	xxx	xxx	13:30
		Loop/Reis		13:30	xxx	xxx	13:31
		Op/Afstaptijd		13:31	xxx	xxx	13:33
0000100	xxx	L	108	13:12	xxx	xxx
		Op/Afstaptijd		13:12	xxx	xxx	13:15
	0000	Loop/Reis	22	13:15	xxx	xxx	13:21
0	0000	1800513	1559	13:23	xxx	xxx	13:50
MAT		1800513	2238	13:50	xxx	xxx	13:52
0	0000	1800513	1596	14:02	xxx	xxx	15:00
MAT		1800513	2281	15:00	xxx	xxx	15:06
0	0000	1800513	1579	15:20	xxx	xxx	16:19
MAT		1800513	2248	16:19	xxx	xxx	16:21
0	0000	1800513	1626	16:32	xxx	xxx	16:57
		Loop/Reis		16:57	xxx	xxx	16:58
		Pauze		16:58	xxx	xxx	17:50
		Loop/Reis		17:50	xxx	xxx	17:51
0	0000	1800501	1595	17:51	xxx	xxx	18:20
MAT		1800501	54654	18:20	xxx	xxx	18:37
0	0000	1800501	V1549	18:58	xxx	xxx	19:38
MAT		1800501	56984	19:38	xxx	xxx	20:06
0	0000	1800501	V1555	20:28	xxx	xxx	21:08
MAT		1800501	54657	21:08	xxx	xxx	21:14
		Prep-in		21:14	xxx	xxx	21:16
		Op/Afstaptijd		21:16	xxx	xxx	21:18

And a frament of the Mutation file:

T 102	0000100	16:10	19:50	Go to bla bla
V 103	0000100	5:54	6:22	Do something
V 103	0000100	10:07	10:26	Do something
T 104	0000100	16:10	19:50	Do something
T 106	0000100	14:35	15:01	Do something
T 106	0000100	17:50	18:16	Do something
L 108	0000100	19:38	20:06	Do something
V 109	0000100	7:46	8:27	Do something
V 109	0000100	10:40	10:43	Do something
V 109	0000100	10:43	10:59	Do something
L 110	0000100	17:35	18:01	Do something

What I want to do is read the first row of the mutation file, and then search for 0000100 in the EXP file, and when that is found, search for 102 in the same row. When not found go to the next 0000100 and search again for 102. When that is found search for 16:10 AND 19:50 in the rows below found 102 and the result must be found before the next 0000100. When times are found past Go to bla bla in the EXP file in the same row as found the times, in column I. And the next row and so on. The search direction is from top to bottom. What is the best approach for something like this?
The result looks like this:

0000100	xxx	T	102	12:04	xxx	xxx
		Op/Afstaptijd		12:04	xxx	xxx	12:07
		Prep-out		12:07	xxx	xxx	12:09
MAT		1800515	54570	12:09	xxx	xxx	12:23
0	0000	1800515	1524	12:23	xxx	xxx	12:45
0	0000	1800515	1582	12:47	xxx	xxx	13:12
				13:12	xxx	xxx	13:23
0	0000	1800204	1539	13:23	xxx	xxx	13:50
0	0000	1800204	1548	13:56	xxx	xxx	14:47
0	0000	1800204	1555	14:56	xxx	xxx	15:20
		Loop/Reis		15:20	xxx	xxx	15:21
		Pauze		15:21	xxx	xxx	16:07
		Loop/Reis		16:07	xxx	xxx	16:10
		1800140		16:10	xxx	xxx	19:50	Go to bla bla
MAT		1800140	56367	19:50	xxx	xxx	19:56
		Prep-in		19:56	xxx	xxx	19:58
		Op/Afstaptijd		19:58	xxx	xxx	20:00

I have no idea how to do that. Who will show me the way?
Thanks in advance!

 

[mrdigitalis]

No one an idea of how to retrieve the data? I am asking for hints. Not for a complete script . Do I need the find method or the index match method or make an array. Any help and suggestions are welcome!

 

[mrdigitalis]

This worked for me:

Sub ZoekLetterEnNummer()
    Dim expSheet As Worksheet
    Dim mutSheet As Worksheet
    Dim lastMutRow As Long
    Dim lastExpRow As Long
    Dim i As Long
    Dim j As Long
    Dim foundRow As Long
    Dim Mut_Dienst As String
    Dim Mut_DagNummer As String
    Dim Mut_BeginTijd As String
    Dim Mut_EindTijd As String
    Dim Mut_Tekst As String
    Dim dienstLetter As String
    Dim dienstNummer As String

    ' Schakel het werkblad "Mutatie_File" in
    Set mutSheet = Sheets("Mutatie_File")

    ' Vind het laatste gebruikte rijnummer in kolom A van het werkblad "Mutatie_File"
    lastMutRow = mutSheet.Cells(mutSheet.Rows.Count, "A").End(xlUp).Row

    ' Loop door elke rij in het werkblad "Mutatie_File"
    For i = 1 To lastMutRow
        ' Haal de waarden op voor de huidige rij uit "Mutatie_File"
        Mut_Dienst = mutSheet.Cells(i, "A").Value
        Mut_DagNummer = mutSheet.Cells(i, "B").Value
        Mut_BeginTijd = mutSheet.Cells(i, "C").Value
        Mut_EindTijd = mutSheet.Cells(i, "D").Value
        Mut_Tekst = Trim(mutSheet.Cells(i, "E").Value) & " " & Trim(mutSheet.Cells(i, "F").Value) & " " & Trim(mutSheet.Cells(i, "G").Value) & " " & Trim(mutSheet.Cells(i, "H").Value)

        ' Controleer of de waarde in cel A van "Mutatie_File" een spatie bevat om de dienstletter en dienstnummer te scheiden
        If InStr(1, Mut_Dienst, " ") > 0 Then
            ' Split de waarde in twee delen: dienstletter en dienstnummer
            dienstLetter = Trim(Left(Mut_Dienst, InStr(1, Mut_Dienst, " ") - 1))
            dienstNummer = Trim(Mid(Mut_Dienst, InStr(1, Mut_Dienst, " ") + 1))
        Else
            ' Als er geen spatie is gevonden, geef een foutmelding en ga door met de volgende rij
            MsgBox "Geen spatie gevonden in cel A" & i & " van Mutatie_File.", vbExclamation
            GoTo NextRow
        End If

        ' Schakel het werkblad "EXP_File" in
        Set expSheet = Sheets("EXP_File")

        ' Vind het laatste gebruikte rijnummer in kolom A van het werkblad "EXP_File"
        lastExpRow = expSheet.Cells(expSheet.Rows.Count, "A").End(xlUp).Row

        foundRow = 0 ' Reset de gevonden rij naar 0 voor elke nieuwe rij in "Mutatie_File"

        ' Doorloop elke rij in het werkblad "EXP_File" om de overeenkomende letter en nummer te vinden
        For j = 1 To lastExpRow
            If Trim(expSheet.Cells(j, "A").Value) = Mut_DagNummer And Trim(expSheet.Cells(j, "C").Value) = dienstLetter And Trim(expSheet.Cells(j, "D").Value) = dienstNummer Then
                ' Als de overeenkomende letter en nummer zijn gevonden, sla de rij op en verlaat de loop
                foundRow = j
                Exit For ' Verlaat de loop nadat de overeenkomende rij is gevonden
            End If
        Next j

        ' Als er geen overeenkomende rij is gevonden, toon een berichtvenster
        If foundRow = 0 Then
            MsgBox "Geen overeenkomende rij gevonden in EXP_File voor rij " & i & " van Mutatie_File.", vbInformation
            GoTo NextRow ' Ga door met de volgende rij in "Mutatie_File"
        End If

        ' Zoek nu vanaf de gevonden rij naar de volgende waardes van cel C (Mut_BeginTijd) en D (Mut_EindTijd)
        For j = foundRow To lastExpRow
            If Trim(expSheet.Cells(j, "E").Value) = Mut_BeginTijd And Trim(expSheet.Cells(j, "H").Value) = Mut_EindTijd Then
                expSheet.Cells(j, "J").Value = Mut_Tekst
                ' Als de waarden van cel C en D zijn gevonden, toon een berichtvenster met de gevonden celwaarden
                Debug.Print "Gevonden in rij " & j & " - Kolom E waarde: " & expSheet.Cells(j, "E").Value & ", Kolom H waarde: " & expSheet.Cells(j, "H").Value
                Debug.Print "Ik heb hem gevonden!"
                Exit For ' Verlaat de loop nadat de waarden van cel C en D zijn gevonden
            End If
        Next j

        ' Als er geen overeenkomende waarden zijn gevonden, toon een berichtvenster
        If j > lastExpRow Then
            Debug.Print "Geen overeenkomende waarden gevonden in EXP_File voor rij " & i & " van Mutatie_File.", vbInformation
        End If

NextRow:
    Next i

    Debug.Print "Einde programma"
End Sub