How to copy attribute of svg graphic (from html to excel)

780 views Asked by At

I have created a macro that finds people from the website online base (using their pin numbers) and I want to copy some information about them to excel (like their name, status etc.). The search reasult is a table that contains text and objects (svg) and I have the problem with copying objects to excel. The search result looks like that:

<td data-label="Full name">Name</td>
<td data-label="Status" class="results-status">
    <svg role="presentation"><use xlink:href="/static/img/sprite.svg#status-caution"></use></svg>
</td>
<td data-label="Location">UK</td>
<td data-label="Details"></td>

So I wrote this:

Set elements = doc.getElementsByTagName("td")
            Cells(i, 1).Value = elements(0).innerText
            Cells(i, 3).Value = elements(1).innerText
            Cells(i, 4).Value = elements(2).innerText
            Cells(i, 5).Value = elements(3).innerText

And unfortunately I don't get anything from elements(1). I guess it doesn't work because this is an object not a text.

I have to get status information from the 2nd row (/static/img/sprite.svg#status-caution) and I don't have any idea how to do this. I also triedimg = elements(1).getAttribute() but msgbox(img) showed only this: [native code]

1

There are 1 answers

2
泰宏卓 On BEST ANSWER

Please try this:

Cells(i, 3).Value = elements(1).Children(0).Children(0).getAttribute("xlink:href")

Explain:

  1. Because elements are set as doc.getElementsByTagName("td") and the tagname "use" is under two level, add Children(0).Children(0). or firstElementChild.firstElementChild to get href.

  2. elements(1).Children(0).Children(0).href gets nothing, instead, take .getAttribute("xlink:href") to replace .href.

Or another solution would be:

Set elements = doc.getElementsByClassName("results-status")

Cells(i, 3).Value = elements(0).firstElementChild.firstElementChild.getAttribute("xlink:href")

Attached screenshot of working code and tested