My Web Query works in Excel but not with VBA

Question

I can run the Web Query from excel spreadsheet but when I put into VBA then it gets no data. Here is what a row of data looks like and the date is all in one column. The date has a quote in front that I want to remove but I will do it within VBA if I have to.

Monday, February 17, 2014   5   1   1

And here is the code segment:

DownloadURL = "http://www.wilottery.com/lottogames/pick3Allhistory.aspx/pick3history.xls"

On Error Resume Next 
With Ws1.QueryTables.Add(Connection:=DownloadURL, Destination:=Ws1.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 = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=-12

Ws1.Range("A1").TextToColumns Destination:=Ws1.Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1))

Remove On Error resume next, there's actually an error on QueryTable.Add — Alex, Feb 18 '14 at 22:45

score 2 · Answer 1 · edited Feb 21 '14 at 21:33

Try this code:

DownloadURL = "http://www.wilottery.com/lottogames/pick3Allhistory.aspx/pick3history.xls"
With Sheet1.QueryTables.Add(Connection:= _
    "URL;" + DownloadURL, Destination:=Sheet1.Range("$A$1"))
    .Name = "q?s=usdCAd=x_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

Thank-you. This did work for me although it also included website text about the data but I can delete that. — RoseAb, Feb 18 '14 at 23:48
np, well once you get the data down on your exel sheet, its all strings and data processing from there. What is it you can't delete? — Math4123, Feb 19 '14 at 07:11

My Web Query works in Excel but not with VBA

1 Answers1