CODEWITHSUNDEEP
vbaexcel
user2482756
user2482756

Reputation: 61

VBA get element by id

Hello I would appreciate some advice , I need to copy and past some data from webpage to excel but I am if this can be possible and how .

Lets' say I have a file as :

 A        B          
Link     Data

I need to open the link on "A" copy the <span id="JS_topStoreCount"></span> result of the page and Paste on "B"

I have many rows so has to repeat the same to all the rows

Upvotes: 0

Views: 1709

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12645

You can create a custom function with an XMLHTTP request: 

Public Function giveMeValue(ByVal link As String) As String
    Set htm = CreateObject("htmlFile")
    With CreateObject("msxml2.xmlhttp")
       .Open "POST", link, False
       .send
       htm.body.innerhtml = .responsetext
    End With
    With htm.getelementbyid("JS_topStoreCount")
       giveMeValue = .innerText
    End With
    htm.Close
    Set htm = Nothing
End Function

and just insert the function in the cell, for example in B1: 

=giveMeValue(A1)

Of course, if you prefer not to have a function, you can embed the content of the function into a Do Loop and dynamically run only once through all the cells by redefining each time the value of link and the place where the .innerText should be printed.

Upvotes: 1

Related Questions