title: Importing web query data into a VBA variable instead of excel spreadsheet cell
body: <p>I wish to perform a web query from my excel spreadsheet.  However I do not want to display the data on the worksheet.  I want to store it directly into a VBA array.</p>
  
  <p>I found this example on the internet <a href="http://www.ozgrid.com/forum/showthread.php?t=141316" rel="nofollow noreferrer">Return Sql Query Results To Vba Variable Instead Of Cell</a></p>
  
  <p>Here is the coded solution from the link with an ODBC connection.  I would like to adapt this to a web query solution.  Not sure how to modify it.</p>
  
  <pre><code>Dim ws As Workspace, db As Database, rs As Recordset 
  Dim sqlstr As String, ToolID As String 
  
  Private Sub OpenODBC(ws As Workspace, db As Database, dsn As String, id  As String, pwd As String) 
    Dim dsnStr As String 
    Set ws = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC) 
    Workspaces.Append ws 
    ws.LoginTimeout = 300 
    dsnStr = "ODBC;DSN=" &amp; dsn &amp; ";UID=" &amp; uid &amp; ";PWD=" &amp; pwd 
    Set db = ws.OpenConnection(dsn, dbDriverNoPrompt, False, dsnStr) 
    db.QueryTimeout = 1800 
  End Sub 
  
  Sub Tool() 
  
    On Error Goto errhandler: 
  
    Call OpenODBC(ws, db, "AC", "USERNAME", "PASSWORD") 
  
    sqlstr = "SELECT FHOPEHS.LOT_ID, FHOPEHS.TOOL_ID" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "FROM DB2.FHOPEHS FHOPEHS" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "WHERE (FHOPEHS.LOT_ID='NPCC1450.6H') AND (FHOPEHS.TOOL_ID Like 'WPTMZ%')" 
  
    Set rs = db.OpenRecordset(sqlstr, dbOpenSnapshot) 
  
    ToolID = rs("TOOL_ID") 
  
    Goto ending 
  
    errhandler: 
    If Err.Number = 1004 Then 
        Goto ending 
    End If 
    ending: 
  
    MsgBox ToolID 
  
  End Sub 
  </code></pre>
  
  <p>I do not have an external link to share, this is an intranet, but below is my code which I'm trying to modify to store the result in an array instead of a worksheet cell - As shown below in my code the destination is cell "A1" on the worksheet.</p>
  
  <p>The initial example I posted shows how to store the data directly in the variable "<strong><em>Set rs = db.OpenRecordset(sqlstr, dbOpenSnapshot)</em></strong> ".  </p>
  
  <p>Other solutions I found on the net, store the data to location on a worksheet and then move it into an array, completing the action with deleting the content on the worksheet.  I'm not interested in doing that procedure, I wish to go directly into the variable from the query result.</p>
  
  <pre><code>    Sheets("Raw Data").Select
  
  Cells.Select
  Selection.ClearContents
  Selection.QueryTable.Delete
  
  With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;http://myInternalAddress/myServerSideApp.php", Destination:=Range("A1"))
      .Name = "AcctQry"
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = True
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .WebSelectionType = xlEntirePage
      .WebFormatting = xlWebFormattingNone
      .WebPreFormattedTextToColumns = True
      .WebConsecutiveDelimitersAsOne = True
      .WebSingleBlockTextImport = False
      .WebDisableDateRecognition = False
      .WebDisableRedirections = False
      .Refresh BackgroundQuery:=False
  End With
  </code></pre>
  
  <p>The expected result will be a list of names and their initials</p>
  
  <p>The php code streaming out the data looks like this</p>
  
  <pre><code>    function getEngineers()
      {
          $sql = 'select `engname` as `name`, `engineer` as `initials` from `engineers`';
          if ( $result = $db-&gt;query($sql) )
          {
              if ($result-&gt;num_rows &gt; 0)
              {
  ?&gt;
                      &lt;!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"&gt;
                      &lt;html lang="en"&gt;
                          &lt;head&gt;&lt;/head&gt;
                          &lt;body&gt;
                              &lt;table&gt;
                                  &lt;tbody&gt;
  &lt;?php
                                      while ($n = $result-&gt;fetch_array()) 
                                      {
                                          echo '&lt;tr&gt;&lt;td&gt;'.$n['name'].'&lt;/td&gt;&lt;td&gt;'.$n['initials'].'&lt;/td&gt;&lt;/tr&gt;';
                                      }
  ?&gt;
                                  &lt;/tbody&gt;
                              &lt;/table&gt;
  
                          &lt;/body&gt;
                      &lt;/html&gt;
  &lt;?php
              }else{
                  throw new Exception('No names returned');
              }
          }else{
              throw new Exception("Query to get engineer's names failed");
          }
      }
  </code></pre>
  
  <p>Here is the output from the browser. Basically there are two columns, 1. the name, 2. the initials</p>
  
  <p><a href="https://i.stack.imgur.com/b9Ttw.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/b9Ttw.gif" alt="enter image description here"></a></p>
  
  <p>Ok here is the screen shot of the HTML code, nothing unique
  <a href="https://i.stack.imgur.com/WEeGK.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WEeGK.gif" alt="Screen shot of html output"></a></p>