title: VBA - date minus X days from today
body: <p>Here's a snippet of my invoice generator, particularly the crawler SUB that grabs the exchange rate from a webpage who's URL contains the exchange rate date. I'm trying is force it to go to a URL from Saturday if the referring date (datIzd, pulled from datIzdCtrl content control) is Sunday or Monday, because the page isn't yet generated on those days. Currently Sunday and Monday send out "out of range" error.</p>
  
  <p>Perhaps a much better way would be for the date to keep going 1 day back until it hits a page that exists (and notifying in MsgBox how far back it went), because the same thing applies to holidays - bank doesn't release the new exchange rate, so the one from last working day is relevant.</p>
  
  <p>Can someone show me how this is done? I tried using </p>
  
  <pre><code>If Weekday(Now(), vbMonday)
  </code></pre>
  
  <p>and playing around with it, but it didn't get far.</p>
  
  <p>Also I'm aware that in the code I'm seemingly unnecessarily reformating dates multiple times, but this is a must since US and Croatian date formats aren't the same, and they have to be properly presented on respective invoices, and recalculated for URL name between conversions.</p>
  
  <p>Here's what I have.</p>
  
  <pre><code>Dim splData As Variant
  
  Enum READYSTATE
      READYSTATE_UNINITIALIZED = 0
      READYSTATE_LOADING = 1
      READYSTATE_LOADED = 2
      READYSTATE_INTERACTIVE = 3
      READYSTATE_COMPLETE = 4
  End Enum
  
  Sub Crawler()
  
      Dim url As String, datIzd As Date, xmlHTTP As MSXML2.ServerXMLHTTP60
      Dim getData As String
  
      Set xmlHTTP = New MSXML2.ServerXMLHTTP60
  
      ActiveDocument.SelectContentControlsByTitle("datIzCtrl")(1).Range.ParentContentControl.DateDisplayFormat = "MM-DD-YYYY"
  
      datIzd = ActiveDocument.SelectContentControlsByTitle("datIzCtrl")(1).Range.Text
  
      With xmlHTTP
          url = "http://www.hnb.hr/tecajn/f" &amp; Format(datIzd, "ddmmyy") &amp; ".dat"
          .Open "GET", url, False
          .setRequestHeader "Content-Type", "text/xml"
          .send
          getData = .responseText
      End With
  
      repData = Replace(getData, "       ", vbCrLf)
      repData = Replace(getData, "      ", vbCrLf)
      splData = Split(repData, vbCrLf)
  
      If OptionPredracun.Value = True Or OptionRacunPredujam.Value = True Or OptionRacunUkupniIznosHR.Value = True Then
          ActiveDocument.SelectContentControlsByTitle("datIzCtrl")(1).Range.ParentContentControl.DateDisplayFormat = "DD. MMMM YYYY."
      Else
          ActiveDocument.SelectContentControlsByTitle("datIzCtrl")(1).Range.ParentContentControl.DateDisplayFormat = "MMMM DD, YYYY"
      End If
  
  
  End Sub
  </code></pre>
  
  <p>Note: if this sounds confusing, you can have a look at my previous post regarding this same project that goes into more detail on what I'm trying to do:</p>
  
  <p><a href="https://stackoverflow.com/questions/28517857/vba-extract-and-parse-data-from-website-to-word/28522759?noredirect=1#comment45370228_28522759">VBA extract and parse data from website to Word</a></p>