WebQuery

[LouLBI]

In using a WebQuery, if I use a specific date, the macro works fine as follows

Sub DerbyLane()
'
'
    Sheets.Add
 
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.derbylane.com/entriesresult/SP08-20-2014AENT.HTM", Destination _
        :=Range("A1"))
        .Name = "SP08-20-2014AENT"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    End Sub

But when I want to go to a date relative to the current date as follows, it hangs up at
".Refresh BackgroundQuery:=False"

Sub DerbyLane()
'
'
Sheets.Add

Dim URL As String, theDate As Date
theDate = Date - 1 'yesterday, for example
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.derbylane.com/entriesresult/SP" & Format(theDate, "MM-DD-YYYY") & "eENT.AENT.HTM", Destination _
        :=Range("A1"))
         .Name = "SP08-20-2014AENT"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    End Sub

This worked at one time, but now it does not.
Thanks
LouLBI

 

[mjbeam]

You have a typo in the second case. Try:

"URL;http://www.derbylane.com/entriesresult/SP" & Format(theDate, "MM-DD-YYYY") & "AENT.HTM", Destination _

 

[LouLBI]

You have a typo in the second case. Try:

"URL;http://www.derbylane.com/entriesresult/SP" & Format(theDate, "MM-DD-YYYY") & "AENT.HTM", Destination _

Sub DerbyLane()
'
'
    Sheets.Add
Dim URL As String, theDate As Date
theDate = Date - 1 'yesterday, for example
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.derbylane.com/entriesresult/SP" & Format(theDate, "MM-DD-YYYY") & "AENT.HTM", Destination _
        :=Range("A1"))
    'With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.derbylane.com/entriesresult/SP08-20-2014AENT.HTM", Destination _
        :=Range("A1"))
        .Name = "SP08-20-2014AENT"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    End Sub

I believe I copied it correctly, but it still does not work
LouLBI

 

[mjbeam]

I just copied and pasted your code into a module in Excel and it ran fine.

 

[mjbeam]

The code worked for me as is, but try this anyway:

Sub DerbyLane()
'
'
Sheets.Add
Dim URL As String, theDate As Date
theDate = Date - 1 'yesterday, for example
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.derbylane.com/entriesresult/SP" & Format(theDate, "MM-DD-YYYY") & "AENT.HTM", Destination _
        :=Range("A1"))
        .Name = "SP08-20-2014AENT"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .BackgroundQuery = False
        .Refresh
    End With
    
End Sub

 

[LouLBI]

Having trouble with my internet connection on my laptop. Now on my iPad. Will try your suggestion when I resolve the problem.

Thanks
LouLBI