Web query again

[lark047]

Hello,

I looked on MSDN about web queries with VBA. But the code I took from
there doesn't work.

Dim BaseURL As String
BaseURL = "some URL"
Const StartDate As Date = #11/2/2004#, EndDate As Date = #4/20/2005#

Dim d As Date
For d = StartDate To EndDate
Dim newSheet As New Excel.Worksheet

Set newSheet = Worksheets.Add
With newSheet
.Name = Replace(CStr(d), "/", ".")
Call .Activate
End With

'MsgBox ("URL;" & BaseURL)

'Next line gives an error
'Run-time error '5':
'Invalid procedure call or argument

With ActiveSheet.QueryTables.Add("URL;" & BaseURL, Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Worksheets(Replace(CStr(d), "/", ".")).Move
After:=Worksheets(Worksheets.Count)
Next d

The line that I get the error is noted above. The line looks correct to
me. Can someone help?

Thanks,
Andrew

 

[moi]

maybe this?

With ActiveSheet
.QueryTables.Add "URL;" & BaseURL, Range("A1")
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

 

[Andrew Clark]

maybe this?

With ActiveSheet
.QueryTables.Add "URL;" & BaseURL, Range("A1")
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

I figured it out. My code needed to be in 'ThisWorkbook'. Once I moved it
there, everything worked A-OK. Previously, I had it in 'Sheet1'.

Andrew