Web query again

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

maybe this?

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



<lark047@yahoo.com> schreef in bericht
news:1114217004.162350.302340@g14g2000cwa.googlegroups.com...
> 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" <coffeecop@_NOSPAM_planet.nl> wrote in news:d4dftl$7jf$1
@reader13.wxs.nl:

> 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