title: Download Excel File via VBA
body: <p>I am trying to download a file from IBM Cognos via Excel VBA. The script will execute, but I only get a 9KB Excel file that will not open. How do I make this work?<br><br>
  Here is my code:</p>
  
  <pre><code>Sub ado_stream()
  'add a reference to Microsoft XML v6 and MS ActiveX Data Objects
  'via Tools/References
  'This assumes the workbook is saved already, and that you want the file in the same folder
  Dim fileStream As ADODB.Stream
  Dim xmlHTTP As MSXML2.xmlHTTP
  Dim strURL As String
  
  strURL = "http://foo.bar"
  
  Set xmlHTTP = New MSXML2.xmlHTTP
  xmlHTTP.Open "GET", strURL, False, "username", "password"
  xmlHTTP.Send
  
  If xmlHTTP.status &lt;&gt; 200 Then
      MsgBox "File not found"
      GoTo exitsub
  End If
  
  Set fileStream = New ADODB.Stream
  With fileStream
      .Open
      .Type = adTypeBinary
      .Write xmlHTTP.responseBody
      .Position = 0
      .SaveToFile "C:\Users\myname\Downloads\Test.xlsx"
      .Close
  End With
  
  exitsub:
  Set fileStream = Nothing
  Set xmlHTTP = Nothing
  
  End Sub
  </code></pre>