Fetch Website data into Excel

Question

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?

user3819867 · Accepted Answer · 2015-05-31 18:08:02Z

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.

answered May 31, 2015 at 18:08

user3819867

1,1181 gold badge8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4039065 Over a year ago

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

Collectives™ on Stack Overflow

Fetch Website data into Excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related