Web Queries in Modules

[Tomski]

Hi Guys,

I'm trying to produce a spreadsheet that pulls infomation from different web pages throughout the day. It needs to pull quite a large amount of information from different web pages hence I wanted to produce a subroutine which could be called with different variables for each web page, i.e. url, field, destination, etc.

To actually produce the query I recorded a macro and then used the code it produced. I then changed the URL to url, a string variable which holds the urls of the pages. The destion address was changed to destination, a range variable to store the destionation location, and the webTable was changed to table also a string variable to store the table number. This is shown below.


With ActiveSheet.QueryTables.Add(Connection:= _
url, Destination:= destination)
.Name = "q?s=%5EFTSE&m=L_137"
.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 = table
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

I'm not even sure if all this is needed, but it does then seem to work correctly if called with one set of variables, or at first, however if I then add more code and start trying to call it with others it doesn't. The error shown below is thrown:

'The destination range is not on the same worksheet that the Query table is being created on.'

Does anybody know how to solve this, or maybe tidy up my web query so that it can be used in the way I would like.

Thanks again

Tom

[Tomski]

Still not having any luck with this so if anybody knows any good web links of where to read about Excel and web queries it would be much appreciated.

Cheers T

[Rob Bovey]

"Tomski" <[email protected]> wrote in
message news:[email protected]...
>
> Still not having any luck with this so if anybody knows any good web
> links of where to read about Excel and web queries it would be much
> appreciated.

Are you sure you tried my answer? It worked fine for me here.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

[Tomski]

I'm sorry I can't actually see your answer, unless I'm being a complete wally. I can only now see 2 replies for this thread.

Would love to give it ago though if you could re-post it or explain to me why I can't see it.

Thanks

Tom

[Rob Bovey]

> With ActiveSheet.QueryTables.Add(Connection:= _
> url, Destination:= destination)

You're passing in a destination range that could be on any worksheet but
you're always trying to create the query table on the ActiveSheet. You could
fix this in the following manner:

With destination.Parent.QueryTables.Add(Connection:= _
url, Destination:= destination)

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Tomski" <[email protected]> wrote in
message news:[email protected]...
>
> I'm sorry I can't actually see your answer, unless I'm being a complete
> wally. I can only now see 2 replies for this thread.
>
> Would love to give it ago though if you could re-post it or explain to
> me why I can't see it.
>
> Thanks
>
> Tom
>
>
> --
> Tomski
> ------------------------------------------------------------------------
> Tomski's Profile:
> http://www.excelforum.com/member.php...o&userid=26824
> View this thread: http://www.excelforum.com/showthread...hreadid=569352
>

[Rob Bovey]

> With ActiveSheet.QueryTables.Add(Connection:= _
> url, Destination:= destination)

You're passing in a destination range that could be on any worksheet but
you're always trying to create the query table on the ActiveSheet. You could
fix this in the following manner:

With destination.Parent.QueryTables.Add(Connection:= _
url, Destination:= destination)

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Tomski" <[email protected]> wrote in
message news:[email protected]...
>
> I'm sorry I can't actually see your answer, unless I'm being a complete
> wally. I can only now see 2 replies for this thread.
>
> Would love to give it ago though if you could re-post it or explain to
> me why I can't see it.
>
> Thanks
>
> Tom
>
>
> --
> Tomski
> ------------------------------------------------------------------------
> Tomski's Profile:
> http://www.excelforum.com/member.php...o&userid=26824
> View this thread: http://www.excelforum.com/showthread...hreadid=569352
>

[Tomski]

Sorry it's taken so long for me to get back to you, only just had time at work to sort out my code. But it's working now, so that's brilliant.

Thanks ever so much for you help.

Cheers

T