title: Trying to extract ONE value from a webpage with VBA in Excel
body: <p>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 =)</p>
  
  <p>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.)</p>
  
  <p>The page is a swedish stock-exchange page, and the page I've used as a test-page is the stock for "Investor B" (<a href="https://www.avanza.se/aktier/om-aktien.html/5247/investor-b" rel="nofollow">https://www.avanza.se/aktier/om-aktien.html/5247/investor-b</a>)</p>
  
  <p>And the value I'm interested in is the one called "Senaste" (this is the page-information surrounding it)</p>
  
  <pre><code>&lt;li&gt;
      &lt;span class="XSText"&gt;Senast&lt;br/&gt;&lt;/span&gt;
      &lt;span class="lastPrice SText bold"&gt;&lt;span class="pushBox roundCorners3"    title="Senast uppdaterad: 17:29:59"&gt;248,60&lt;/span&gt;&lt;/span&gt;
  &lt;/li&gt;
  </code></pre>
  
  <p>And it's the value 248,60 I'm after!</p>
  
  <p>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</p>
  
  <p>I might be totally on the wrong track</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>EDIT: 2014-05-12 Current code beeing tested 17:05</strong></p>
  
  <p>under the button command</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And in module1</p>
  
  <pre><code>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 &lt;&gt; READYSTATE_COMPLETE
      Sleep 250
  Loop
  
  End Function
  </code></pre>
  
  <p>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.</p>
  
  <p>Best Regards</p>
  
  <p><strong>Stop editing 2014-05-12 17:08</strong></p>