Ask Experts Questions for FREE Help !
Ask
    Pajoooo's Avatar
    Pajoooo Posts: 60, Reputation: 1
    Junior Member
     
    #1

    May 24, 2016, 02:38 PM
    Read xml in array
    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.

Check out some similar questions!

Array need help [ 4 Answers ]

Write a program that will ask the user to enter a number and then place the 10 consecutive numbers in a single-dimensional array starting from the number entered by the user. The program will then display the elements inside index 2 and 4 and 6.

How to read child of child nodes using xml in java [ 0 Answers ]

Hi I am using the following XML file and would like to read the data for respective date in the child nodes. I want to pass date and want to retrieve data for that particular date. This is the first time I am calling child of child node. Please help. Thanks in advance. <Event> ...

Basic XML Schema and Valid XML generation [ 1 Answers ]

Hi, I have an XML schema file as below. Creating and parsing the XML results works but I don't understand how to validate. I understand the concept of a namespace but not the implementation. Why do I need to refer to "http://www.w3.org/2001/XMLSchema"? I just want to create a validation based on...

An array [ 1 Answers ]

How many ratings do you wish to enter? 10 Rating #1: 9 Rating #2: 5 Rating #3: 9 Rating #4: 2 Rating #5: 11 Invalid entry, score must be in range of 1 and 10; try again. Rating #5: -1 Invalid entry, score must be in range of 1 and 10; try again. Rating #5: 8


View more questions Search
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.