I have a xml file created by the application:
<Post>PC 21000 Split</Post>
<Date_Of_Delivery>12.5.2016</Date_Of_Delivery>
<Card>S</Card>
<Number>I</Number>
<For>21276 Vrgorac</For>
<PP1></PP1>
<VP1></VP1>
<PP2></PP2>
<VP2></VP2>
<Remark></Remark>
<Items>Registered mail|RR444444444RS|
Registered mail|RR555555555RS|
Registered mail|RR666666666RS|
Registered mail|RR777777777RS|
Parcel|CP111111111RS|
Parcel|CP222222222RS|
Parcel|CP333333333RS|
EMS mail|EE888888888RS|
EMS mail|EE999999999RS|
</Items>
What I need is: in excel worksheet in cell A1, get content of <Items></Items>, without opening the XML file.
I use Macro:
Option Explicit
Sub test()
Dim MyArray() As Variant
Dim Data As Variant
Dim Temp As String
Dim c As Long
Dim myToday As String
Dim strFileName As String
myToday = Format(Date, "dd.mm.yyyy")
strFileName = myToday & "-I" & ".xml"
'Change the path and filename, accordingly
Open "C:\Archive\" & strFileName For Input As #1
c = 1
Do Until EOF(1)
Line Input #1, Data
Select Case True
Case Data Like "<Items>*"
Temp = Replace(Data, "<Items>", "")
Temp = Application.Substitute(Temp, "[/Items]", "")
If IsNumeric(Temp) Then Temp = Val(Temp)
ReDim Preserve MyArray(1 To 1, 1 To c)
MyArray(1, c) = Temp
c = c + 1
End Select
Loop
Close #1
'Write the array to the active worksheet, starting at A1
Range("A1").Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = MyArray
End Sub
Existing macro gives one line only. It seems that the problem is in the spaces in the XML file, because when I delete these spaces I get the full content of <Items></ Items>. Help, please.