Hi folks,
I am sent regularly an XML file which I have to import into access. Sometimes however, the XML file is missing some end tags
e.g:
I need some VBA code to go through the text and find lines that are missing the end tag and add it in before i run the import routine.
Any help with this is very much appreciated!
P.S. I tried the following code which i found online and changed a bit but im getting an error "Type Mismatch"
I am sent regularly an XML file which I have to import into access. Sometimes however, the XML file is missing some end tags
e.g:
Code:
<T_COMMENTS>
<COMMENT_NO>Comment 1</COMMENT_NO>
<STATUS>Old</STATUS>
<USER>User1</USER>
<COMMENT>blah blah blah</COMMENT>
</T_COMMENTS>
<T_COMMENTS>
<COMMENT_NO>Comment 2</COMMENT_NO>
<STATUS>Old
<USER>User2</USER>
<COMMENT>asfjhewlfjkhasdlfkjhasdlfkj</COMMENT>
</T_COMMENTS>
I need some VBA code to go through the text and find lines that are missing the end tag and add it in before i run the import routine.
Any help with this is very much appreciated!
P.S. I tried the following code which i found online and changed a bit but im getting an error "Type Mismatch"
Code:
Sub FixTags(filename As String)
Dim fso As New FileSystemObject
Dim ts As TextStream
Set ts = fso.OpenTextFile(filename, ForReading)
Dim ThisLine As String
Dim i As Integer
i = 0
Do Until ts.AtEndOfStream
ThisLine = ts.ReadLine
i = i + 1
'Debug.Print "Line " & i, ThisLine
If Left(9, ThisLine) = "<STATUS>" And Right(1, ThisLine) <> ">" Then
ThisLine = ThisLine & " </STATUS>"
Else
End If
Loop
ts.Close
End Sub
Last edited: