title: Excel VBA automation of html form generated by php
body: <p>I am a non-coder/programmer trying to create an excel spreadsheet that utilizes some VBA automation to import some web-data based on certain cell values. I have managed to scrape together part of the process using Youtube and some other sites like this. However, I have hit a road block that I am hoping someone could help me with.</p>
  
  <p>Here is the setup:<br>
  I am trying to import some Co2 data based on 2 parameters (pressure and temperature) that will reside in 2 separate cells in my excel sheet.
  In my VBA code, I have managed to navigate to the first site (<a href="http://www.peacesoftware.de/einigewerte/co2_e.html" rel="nofollow">http://www.peacesoftware.de/einigewerte/co2_e.html</a>), and then find the table elements, fill them in, and submit the form.
  My problem is when IE navigates to the next page where the results are. I do not know how to import table elements from this new page. I assume I need to tell VBA to look at the new page, but it has a generic URL (<a href="http://www.peacesoftware.de/einigewerte/calc_co2.php5" rel="nofollow">http://www.peacesoftware.de/einigewerte/calc_co2.php5</a>), so I am confused on what to reference.
  I hope this is all the info needed to get a clear picture of my problem. Here is my current VBA code. The end  part after "submit" is a wild guess on my part. Once VBA is looking at the right table/page, I will then import the enthalpy and entropy values to my excel spreadsheet.
  Thanks in advance for your help!</p>
  
  <pre><code>' updates enthalpy, entropy data from peacesoftware site
  Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Row = Range("tempA").Row And Target.Column = Range("tempA").Column Then
   Dim IE As Object
   Dim pressA As String
   Dim tempA As String
   Dim denseA As String
   Dim enthA As String
   Dim entroA As Style
  
    tempA = Range("tempA")
    pressA = Range("pressA")
    Set IE = CreateObject("InternetExplorer.Application")
  ' setup internet explorer
    IE.Visible = True
    IE.navigate "http://www.peacesoftware.de/einigewerte/co2_e.html"
  Do
    DoEvents
    Loop Until IE.readyState = READYSTATE_COMPLETE
    ' find pressure and temp entry form
    IE.document.getElementsByName("druck")(0).Value = pressA
    IE.document.getElementsByName("temperatur")(0).Value = tempA
    IE.document.forms(0).submit
    'Do
    'DoEvents
    'Loop Until IE.readyState = READYSTATE_COMPLETE
    'update new energy data
    'IE.navigate table_url ???
   End If
  End Sub
  
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
  End Sub
  </code></pre>
  
  <p>***Update</p>
  
  <p>I think I found a solution, so I can at least get it functional. (thanks to this post: <a href="https://stackoverflow.com/questions/15300576/excel-vba-form-submit-and-parse-data-from-result?rq=1">excel vba form submit and parse data from result</a> )</p>
  
  <p>I needed to wait after submit and then search for the "td" element I needed. Since the actual value I needed was the next element after the element label, I used TDelement.nextElementSibling.innerText to grab it.</p>
  
  <pre><code>IE.document.forms(0).submit
    ' wait for new page to load
    Do While IE.Busy: DoEvents: Loop
  
    Set doc = IE.document
      Dim sdd As String
      Set TDelements = doc.getElementsByTagName("td")
      r = 0
      For Each TDelement In TDelements
          If TDelement.innerText = "Density : " Then
              Range("denseA") = TDelement.nextElementSibling.innerText
              r = r + 1
          End If
      Next
  </code></pre>
  
  <p>Thanks again for the help.</p>