title: VBA extract and parse data from website to Word
body: <p>I'm trying to extract some data from here: <a href="http://www.hnb.hr/tecajn/f140215.dat" rel="nofollow">http://www.hnb.hr/tecajn/f140215.dat</a></p>
  
  <p>This is the exchange rate list from the Croatian National Bank. The file name "f140215.dat" is basically a date, formatted in the following order:</p>
  
  <p>"f" "DDMMYY" ".dat"</p>
  
  <p>I intend to have the data organized in a Word table, which contains the following cells:</p>
  
  <ul>
  <li>Cell#1 where a user would manually input a date in the following
  format: "MMM DD, YYYY"</li>
  <li>Cell#2 where a user would manually input the requested currency code
  name (USD, GBP, etc)</li>
  <li>Cell#3 where the extracted exchange rate should appear for the
  specified date and currency.</li>
  </ul>
  
  <p>Underneath the table there is an "UPDATE" button that updates the Cell#3 information. The script I'm asking for should be connected to that button.</p>
  
  <p>After clicking the button, I'd like the script to do the following:</p>
  
  <ul>
  <li>Figure out which page to go to based on the date inputted in Cell #1.
  For example, if the Cell#1 contains "February 14, 2015", the script
  should point to "<a href="http://www.hnb.hr/tecajn/f140215.dat" rel="nofollow">http://www.hnb.hr/tecajn/f140215.dat</a>"</li>
  <li>On that page, grab the middle value for the currency specified    in
  Cell#2. For example, if Cell#2 contains "USD", the script should<br>
  extract "6,766508" which is the middle value for "840USD001". Only
  the middle value is relevant.</li>
  <li>Write this value to Cell#3.</li>
  </ul>
  
  <p>So to sum it up, based in the criteria specified in the two table cells, the script needs to identify which page to go to and what data to extract from it, and with that data populate the third cell.</p>
  
  <p>Hope I explained it well enough. This is only a part of the whole invoice generator I'm building. So far I've gotten everything to work, but this I really don't even know how to start. I can send the whole thing if needed, but figured it's not exactly relevant.</p>
  
  <p>EDIT:</p>
  
  <p>I watched some tutorials and played around, and this is what I got so far.</p>
  
  <pre><code>Enum READYSTATE
      READYSTATE_UNINITIALIZED = 0
      READYSTATE_LOADING = 1
      READYSTATE_LOADED = 2
      READYSTATE_INTERACTIVE = 3
      READYSTATE_COMPLETE = 4
  End Enum
  
  Sub Test()
  
  Dim ie As New InternetExplorer
  ie.Visible = False
  ie.navigate "http://www.hnb.hr/tecajn/f140215.dat"
  
  Do While ie.READYSTATE &lt;&gt; READYSTATE_COMPLETE
  DoEvents
  Loop
  
  Dim html As HTMLDocument
  Set html = ie.document
  
  MsgBox html.DocumentElement.innerText
  
  End Sub
  </code></pre>
  
  <p>I know it's not much, but like I said, I'm new at this. I was able to get the data into the message box, but I have no idea how to parse it, and without that I can't really do anything mentioned above. What now?</p>
  
  <p>EDIT 2:</p>
  
  <p>Alright!! Made some progress! I've managed to parse it by using the split function:</p>
  
  <pre><code>Sub Test()
  
  Dim ie As New InternetExplorer
  ie.Visible = False
  ie.navigate "http://www.hnb.hr/tecajn/f140215.dat"
  
  Do While ie.READYSTATE &lt;&gt; READYSTATE_COMPLETE
  DoEvents
  Loop
  
  Dim html As HTMLDocument
  Set html = ie.document
  
  Dim getData As String
  getData = html.DocumentElement.innerText
  
  'replaced all the space fields with line breaks
  Dim repData As String
  repData = Replace(getData, "       ", vbCrLf)
  
  'used line breaks as separators
  Dim splData As Variant
  splData = Split(repData, vbCrLf)
  
  MsgBox splData(1)
  MsgBox splData(2)
  MsgBox splData(3)
  
  End Sub
  </code></pre>
  
  <p>Right now it displays the parsed data in message boxes. The rest should be easy!</p>
  
  <p><strong>Addendum from OP's comment:</strong></p>
  
  <p>This is a part of the continued code:</p>
  
  <pre><code>Dim cur As String
  cur = ActiveDocument.SelectContentControlsByTitle("valCombo").Item(1).Range.Text
  If cur = "USD" Then
    ActiveDocument.Tables(1).Cell(7, 3).Range.Text = splData(40) &amp; " HRK"
  End If
  If cur = "EUR" Then
    ActiveDocument.Tables(1).Cell(7, 3).Range.Text = splData(20) &amp; " HRK"
  End If
  </code></pre>
  
  <p>This way it works, but I'd like to set <code>ActiveDocument.Tables(1).Cell(7, 3).Range.Text</code> as a string. However, once I do that, it doesn't do anything. Why is that?</p>