Import complex text fie into ms access table by vba

[adamdaban]

dear friends, I have one text file and I want to import to access table here is text file contains:
<!--:Begin:Chksum:1:-->
<!--:Begin:Msg:2:0:-->
<sample>
<ver>1.1</ver>
<instrinfo>
<p><n>PRDI</n><v>BM800</v></p>
<p><n>FIWV</n><v>2.9.3</v></p>
<p><n>SNO</n><v>26052</v></p>
<p><n>BRND</n><v>M</v></p>
<p><n>IAPL</n><v>H</v></p>
<p><n>IID</n></p>
<p><n>LMOF</n><v>1</v></p>
<p><n>PMPM</n><v>25</v></p>
</instrinfo>
<smpinfo>
<p><n>ID[/COLOR]</n><v>8798176</v></p>
<smpresults>
<p><n>RBC</n><v>0.00</v><l>0.00</l><h>0.01</h></p>
<p><n>MCV</n><l>0.5</l><h>0.0</h></p>
<p><n>HCT</n><l>0.3</l><h>0.0</h></p>

so I want to import specific part like these words have color red and bold, any one can help me?

Regards,

 

[arnelgp]

Public Function fnSpclParser(ByVal strToParsed As String) As String
    
    Dim lngPos As Long
    Dim lngPos2 As Long
    Dim strResult As String
    
    lngPos = InStr(1, strToParsed, "<n>ID", vbTextCompare)
    If lngPos <> 0 Then
        strResult = "ID:"
        lngPos = InStr(lngPos, strToParsed, "<v>", vbTextCompare)
    End If
    If lngPos <> 0 Then
        lngPos = lngPos + Len("<v>")
        lngPos2 = InStr(lngPos, strToParsed, "</v>", vbTextCompare)
        strResult = strResult & Mid(strToParsed, lngPos, lngPos2 - lngPos) & ";"
        lngPos = lngPos2
    End If
    If lngPos <> 0 Then
        lngPos = InStr(lngPos, strToParsed, "RBC", vbTextCompare)
    End If
    If lngPos <> 0 Then
        strResult = strResult & "RBC:"
        lngPos = InStr(lngPos + Len("RBC"), strToParsed, "<l>", vbTextCompare)
        lngPos = lngPos + Len("<l>")
        lngPos2 = InStr(lngPos, strToParsed, "</l>", vbTextCompare)
        strResult = strResult & Mid(strToParsed, lngPos, lngPos2 - lngPos) & ";"
        lngPos = lngPos2
    End If
    If lngPos <> 0 Then
        lngPos = InStr(lngPos, strToParsed, "MCV", vbTextCompare)
    End If
    If lngPos <> 0 Then
        strResult = strResult & "MCV:"
        lngPos = InStr(lngPos + Len("MCV"), strToParsed, "<l>", vbTextCompare)
        lngPos = lngPos + Len("<l>")
        lngPos2 = InStr(lngPos, strToParsed, "</l>", vbTextCompare)
        strResult = strResult & Mid(strToParsed, lngPos, lngPos2 - lngPos) & ";"
        lngPos = lngPos2
    End If
    If lngPos <> 0 Then
        lngPos = InStr(lngPos, strToParsed, "HCT", vbTextCompare)
    End If
    If lngPos <> 0 Then
        strResult = strResult & "HCT:"
        lngPos = InStr(lngPos + Len("HCT"), strToParsed, "<l>", vbTextCompare)
        lngPos = lngPos + Len("<l>")
        lngPos2 = InStr(lngPos, strToParsed, "</l>", vbTextCompare)
        strResult = strResult & Mid(strToParsed, lngPos, lngPos2 - lngPos) & ";"
        lngPos = lngPos2
    End If
    
    fnSpclParser = strResult
End Function

Private Sub test()
    Dim var1 As Variant
    Dim var2 As Variant
    Dim i As Integer
    Dim strToParsed As String
    strToParsed = "<!--:Begin:Chksum:1:-->" & _
    "<!--:Begin:Msg:2:0:-->" & _
    "<sample>" & _
    "<ver>1.1</ver>" & _
    "<instrinfo>" & _
    "<p><n>PRDI</n><v>BM800</v></p>" & _
    "<p><n>FIWV</n><v>2.9.3</v></p>" & _
    "<p><n>SNO</n><v>26052</v></p>" & _
    "<p><n>BRND</n><v>M</v></p>" & _
    "<p><n>IAPL</n><v>H</v></p>" & _
    "<p><n>IID</n></p>" & _
    "<p><n>LMOF</n><v>1</v></p>" & _
    "<p><n>PMPM</n><v>25</v></p>" & _
    "</instrinfo>" & _
    "<smpinfo>" & _
    "<p><n>ID[/COLOR]</n><v>8798176</v></p>" & _
    "<smpresults>" & _
    "<p><n>RBC</n><v>0.00</v><l>0.00</l><h>0.01</h></p>" & _
    "<p><n>MCV</n><l>0.5</l><h>0.0</h></p>" & _
    "<p><n>HCT</n><l>0.3</l><h>0.0</h></p>"
    
    var1 = Split(fnSpclParser(strToParsed), ";")
    
    For i = 0 To UBound(var1) - 1
        var2 = Split(var1(i), ":")
        Debug.Print var2(0) & vbTab & " = " & var2(1)
    Next
End Sub

 

[adamdaban]

Thanks so much but I don't know how to use this code in ms access

 

[adamdaban]

Thanks so much but I don't know how to use this code in ms access

here is my database template with text file