Trying to extract ONE value from a webpage with VBA in Excel

Question

I've been trying to find the information now for a couple of days, but all the examples I've found just has a small piece of the code, I need it all =) What I want to do is to extract one value from a homepage and put it into a cell in Excel (and then take another value from another page on the same site and put in the next cell etc etc.) The page is a swedish stock-exchange page, and the page I've used as a test-page is the stock for "Investor B" (https://www.avanza.se/aktier/om-aktien.html/5247/investor-b) And the value I'm interested in is the one called "Senaste" (this is the page-information surrounding it) ```

Senast
248,60

``` And it's the value 248,60 I'm after! I got some coding experience, but not for VBA-scripting, after reading some forum-posts (mostly here), I've been trying out a few example by myself, but co…

Accepted Answer

You are close but have a couple small errors.

Here is how I would set it up (Tested):

```
Private Sub CommandButton1_Click()
    Dim IE As Object

' Create InternetExplorer Object
    Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
    IE.Visible = False

' URL to get data from
    IE.Navigate "https://www.avanza.se/aktier/om-aktien.html/5247/investor-b"

' Statusbar
    Application.StatusBar = "Loading, Please wait..."

' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop

Application.StatusBar = "Searching for value. Please wait..."

Dim dd As String
    dd = IE.Document.getElementsByClassName("lastPrice SText bold")(0).innerText

MsgBox dd

' Show IE
    IE.Visible = True

' Clean up
    Set IE = Nothing

Application.StatusBar = ""
End Sub
```

Results:

Tested in Excel 2010 with the following references:

Edit - Option B

To get rid of a possible "Run-Time Error '91'" try changing a few lines like this:

```
Dim dd As Variant
Set dd = IE.Document.getElementsByClassName("lastPrice SText bold")

MsgBox dd(0).textContent
```

Edit - Option C

Yet another way to get elements:

```
Dim tag
Dim tags As Object
Set tags = IE.Document.getElementsByTagName("*")

For Each tag In tags
    If tag.className = "lastPrice SText bold" Then
        MsgBox tag.innerText
        Exit For
    End If
Next tag
```

(All three methods have been tested on Excel 2010 and IE10)

Trying to extract ONE value from a webpage with VBA in Excel

Solution

Problem

Related problems