CODEWITHSUNDEEP
vbaexcel
Paul Gillespie
Paul Gillespie

Reputation: 1

Fetch Website data into Excel

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?

Upvotes: 0

Views: 168

Answers (1)

user3819867
user3819867

Reputation: 1120

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.

Upvotes: 2

Related Questions