0

The most recent answer to Fetch Website data into Excel was:

   Sub FetchData()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.zillow.com/homes/comps/67083361_zpid", Destination:=Range( _
        "$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .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
End Sub

It works well for my project as well except the URL of my interest is:

URL;http://reservations.example.com/cp/reports/daily_usage.php?type=date_reservations&date=06%2F03%2F2015&date_end=&vessel=&club=6&location=135", Destination:=Range( _
        "$A$1"))

I have used a fake URL above for privacy reasons, but as you can see, the URL has a date code within. In this case it is June 3rd, 2015 as designated by 06%2F03%2F2015.

Using VBA, how can I make the date within the URL a variable, defined within a work sheet?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Paul Gillespie
  • 1

1 Answers1

2

With the Format() function most probably. A wild guess would be

Format(Range("$A$1").Value2, "mm""%2F""dd""%2F""yyyy")

You can also reconstruct it by yourself using the Day(), the Month() and the Year() functions and some playing around with IIf(Len(x)=2,x,0&x).
My sample gave "06%2F03%2F2014" for 2014-06-03.

user3819867
  • 1,114
  • 1
  • 8
  • 18
  • 1
    You can avoid having to deal with doubling quotes inside quoted strings by using the backslash as an escape character for any reserved symbols you wish to make literal in a format mask; e.g. `mm\%2Fdd\%2Fyyyy` –  May 31 '15 at 19:03