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)

<li>
    <span class="XSText">Senast<br/></span>
    <span class="lastPrice SText bold"><span class="pushBox roundCorners3"    title="Senast uppdaterad: 17:29:59">248,60</span></span>
</li>

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 couldn't get any to work. Since I'm quite basic with VBA, I might have got the structure wrong, so please be basic and patient with me, this was my test, but I got "Runtime error 429" ActiveX component can't create object

I might be totally on the wrong track

Private Sub CommandButton1_Click()
Dim ie As Variant
Set ie = CreateObject("InternetExplorer")
ie.navigate "https://www.avanza.se/aktier/om-aktien.html/5247/investor-b"
ie.Visible = True
Do
DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE
Application.Wait (Now() + TimeValue("00:00:016")) ' For internal page refresh or loading
Dim doc As Variant 'variable for document or data which need to be extracted out of webpage
Set doc = CreateObject("HTMLDocument")
Set doc = ie.document
Dim dd As Variant
dd = doc.getElementsByClassName("lastPrice SText bold")(0).innerText
MsgBox dd
End Sub

EDIT: 2014-05-12 Current code beeing tested 17:05

under the button command

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

' Send the form data To URL As POST binary request
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
'this should go from ready-busy-ready
IEWait IE

Application.StatusBar = "Searching for value. Please wait..."
' Dim Document As HTMLDocument
' Set Document = IE.Document
Dim dd As Variant
dd = IE.Document.getElementsByClassName("lastPrice SText bold")(0).innerText

MsgBox dd

' Show IE
IE.Visible = True

' Clean up
Set IE = Nothing
Set objElement = Nothing
Set objCollection = Nothing

Application.StatusBar = ""


End Sub

And in module1

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Function IEWait(p_ieExp As InternetExplorer)

'this should go from ready-busy-ready
Dim initialReadyState As Integer
initialReadyState = p_ieExp.ReadyState

'wait 250 ms until it's done
Do While p_ieExp.Busy Or p_ieExp.ReadyState <> READYSTATE_COMPLETE
    Sleep 250
Loop

End Function

As said earlier, I do not know if I got the structure right with this latest add-in, not to expired in this kind of coding I'm afraid.

Best Regards

Stop editing 2014-05-12 17:08

Here is a [download link](https://drive.google.com/file/d/0B8oK36klPLEWU1ZVa241NzNTa3c/edit?usp=sharing) to the test document I was using. It has two sheets, each sheet uses a different method. — Automate This, May 12 '14 at 15:41
If I add it above the row Private Sub CommandButton1_Click() I'll get the same behaviour as before If I put it just under that row I'll get an error saying "Expected end sub", guessing the function can't be inside of the command_button sub I Dunno if this is irrelevant or not, but did some advanced googling, what do you guys think ? Best Regards http://social.msdn.microsoft.com/Forums/office/en-US/872a705b-b4fd-42e9-9a7f-ebe081a98e18/internetexplorerapplication-behaving-badly-in-excel-2013-vba?forum=appsforoffice — Rycket, May 12 '14 at 15:44
I got the exact same error message described above in both cases Really thanks for the time spent on this issue. It seems like an excel 2013 problem, alternative, my computer set-up problem. Any idea how to continue searching for an answer for the issue ? — Rycket, May 12 '14 at 15:47
Yeah with your file I meant (with both cases) So it got to be computer/settings So I wonder how I get your code to work with my machine, got IE 9.0.8112.16421 Update version 9.0.26 any ideas ? — Rycket, May 12 '14 at 18:34
Managed to test it in excel 2010, same error, since I'm offshore at the moment, I don't have the possibility to test it on another machine, I'll try this weekend, any ideas why this error message are appearing ? I run windows 7 Enterprise 64-bit — Rycket, May 13 '14 at 05:15

Automate This · Accepted Answer · 2014-05-13T17:22:13.983

7

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:

enter image description here

Tested in Excel 2010 with the following references:

enter image description here

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)

edited May 13 '14 at 17:22

answered May 11 '14 at 15:27

Automate This

30,726
11
60
82

Hi Thanks for your time, and the very structured code, I didn't really get it to work, I got an error saying "Run-Time Error '91': Object variable or With block variable not set" pointing at the row ` dd = IE.Document.getElementsByClassName("lastPrice SText bold")(0).innerText ` After some googling the comments I found was to put Set dd and then the same row above, but the same error occured, do I missing some reference or some add-ins ? Cause it seems to be working just fine for you =) Would be thankful for another reply Best Regards – Rycket May 12 '14 at 05:44
I just tested on a different computer by copying this code into a new document and it ran just fine. What version of Excel are you using? I edited my post to show which references I have active. Let me know if that helps. After that, try running it from a new file just to rule out any existing variables that might be causing a problem. – Automate This May 12 '14 at 13:37
Consider adding a Busy Waiting loop instead of using `Application.Wait` for the page to load. I have seen this error before and I think it arises because the document has not finished loading. Here is how to do that: http://stackoverflow.com/questions/21335370/vba-inconsistent-error-91-in-loop-w-ie-doc-reference/21338071#21338071 – David Zemens May 12 '14 at 13:43
@DavidZemens Good idea, thanks. Just a note to the OP - If you use David's suggestion you'll need to add a reference to "`Microsoft Internet Controls`". Then replace the three lines of the `Do While` loop with the `IEWait IE` and copy enderlands code block to the top of your module. – Automate This May 12 '14 at 13:53
Or use late binding :) – David Zemens May 12 '14 at 14:15
Confused, but on a higher level =) I run Excel 2013 (have 2010 also, but same results). I tried to add your bits @DavidZemens , but I'm not sure I did it right, the original was in a button, so I created a module with your code in, and then got the same error message as in my previous comment. I'm not sure if I entered your stuff in the right place. But I did as suggested, and added IEWait IE instead of the DoWhile IE.Busy I also added the reference suggested (MS Internet Controls and those in the picture), I'm sitting offshore atm, but got reasonable fast internet. any other ideas ? =) – Rycket May 12 '14 at 14:50
Can you update your question to include the code you are currently using? – David Zemens May 12 '14 at 15:00
1

I've added Option B & C which may help with getting rid of the error you see. These have been added to the file I provided a link to in the separate module. – Automate This May 13 '14 at 17:22
The method 2 worked :D :D :D Thank you alot! Maybe you should write it as an "answer" so people can find it without going through all the comments and files =) Thanks a lot for the time and effort spent! – Rycket May 14 '14 at 07:03
Ehe, it seems like it only works sometimes I'm afraid, I'm wondering if its due to my limited internetspeed or something like that =/ – Rycket May 15 '14 at 05:11
If your using the wait method I posted above try playing around with the time duration. Change 1 sec to 4 sec and see if you have a higher success rate: `Application.Wait DateAdd("s", 4, Now)` If your using the other method that David linked to (recommended) change `Sleep 250` to somthing like `Sleep 1500` – Automate This May 15 '14 at 13:30
Acctually, I need to run your script once, after that my scripts works, can't really understand why =) (100% success rate at that option) – Rycket Jun 08 '14 at 11:46

score 0 · Answer 2 · answered May 21 '15 at 15:55

I just wanted to add the code I'm currently running which works perfectly fine at the moment, if people run into the same problem. This is to get two values into dedicated cells.

Private Sub CommandButton10_Click()
Dim IE As Object
    Dim dd As Variant
    ' Create InternetExplorer Object
    Set IE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}")
    IE.Visible = False

    ' Send the form data To URL As POST binary request
    IE.Navigate "https://www.avanza.se/aktier/om-aktien.html/52476/alk-abell-b"

    Application.StatusBar = "Loading, Please wait..."
    IEWait IE

    Application.StatusBar = "Searching for value. Please wait..."
    dd = IE.Document.getElementsByClassName("lastPrice SText bold")(0).innerText

    Range("Y2").Value = dd
    
    IE.Navigate "https://www.avanza.se/aktier/om-aktien.html/52380/alm--brand"

    Application.StatusBar = "Loading, Please wait..."
    IEWait IE

    Application.StatusBar = "Searching for value. Please wait..."
    dd = IE.Document.getElementsByClassName("lastPrice SText bold")(0).innerText

    Range("Y3").Value = dd

' Clean up
    Set IE = Nothing
    Set objElement = Nothing
    Set objCollection = Nothing

    Application.StatusBar = ""
End Sub

If one wants more data, it is just to copy the part starting with IE.Navigate "https://www.pagewhereyourdatayouwanttoextractis.com" and stops with Range("Y2").Value = dd

It is ofcourse based if the page you want to extract data from has a similiar structure to the one above.

Hope this can help some people out there.

Best Regards

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

2 Answers2

Linked