title: Basic Authentication with Excel VBA "WebQuery"?
body: <p>In VBA I am trying to leverage WebQuery for a tool I am writing that retrieves financial data.<br>
  I create a WebQuery with similar code to this:</p>
  
  <pre><code>    url = "URL;http://my.url.com/"
      With Worksheets("Sheet1").QueryTables.Add(Connection:=url, Destination:=Worksheets("Sheet1").Range("A1"))
      .PostText = "Param1=Foo&amp;Param2=Bar"
      .Name = "My Query"
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlOverwriteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 1
      .WebSelectionType = xlEntirePage
      .WebFormatting = xlWebFormattingNone
      .WebPreFormattedTextToColumns = True
      .WebConsecutiveDelimitersAsOne = True
      .WebSingleBlockTextImport = False
      .WebDisableDateRecognition = False
      .WebDisableRedirections = False
      .Refresh BackgroundQuery:=False
  End With
  </code></pre>
  
  <p>The problem I am coming across is that <strong>my.url.com</strong> requires a <a href="https://en.wikipedia.org/wiki/Basic_access_authentication" rel="nofollow">Basic Authentication</a> userid and password.<br>
  If I simply run this code, I get a Runtime Error 1004; it does not prompt for authentication credentials.</p>
  
  <p>But if I go into the Excel "Data" ribbon, select "From Web", and then go to <a href="http://my.url.com/" rel="nofollow">http://my.url.com/</a> in their mini web browser, I am prompted for userid and password.<br>
  After closing this window and then re-running my VBA code without changing anything, the query works.
  But upon closing Excel and re-running, it fails again unless I go through this extremely ugly manual authentication step. Even if I have told Internet Explorer to save these credentials permanently.</p>
  
  <p>How can I get VBA to <em>programmatically</em> authenticate a <strong>WebQuery</strong> with Basic Authentication so I don't need to take this extra step?  </p>
  
  <p>(<strong>Edit:</strong> Either this, or is there a setting within Excel to force this prompt to happen, regardless of how the WebQuery was initiated?)</p>
  
  <p>I am using Excel 2010 (32-bit), on Windows 7, but my target users might be using a variety of Excel versions (all on Windows).</p>