Web Queries in Modules
 

Hi Guys,

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

To actually produce the query I recorded a macro and then used the cod
it produced. I then changed the URL to url, a string variable whic
holds the urls of the pages. The destion address was changed t
destination, a range variable to store the destionation location, an
the webTable was changed to table also a string variable to store th
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 wor
correctly if called with one set of variables, or at first, however i
I then add more code and start trying to call it with others i
doesn't. The error shown below is thrown:

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

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

Thanks again

Tom:

--
Tomsk
-----------------------------------------------------------------------
Tomski's Profile: http://www.excelforum.com/member.php...fo&userid=2682
View this thread: http://www.excelforum.com/showthread.php?threadid=56935

Web Queries in Modules
 

Hi Tom,

Your immediate problem is he

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" wrote in
message ...

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
------------------------------------------------------------------------
Tomski's Profile:
http://www.excelforum.com/member.php...o&userid=26824
View this thread: http://www.excelforum.com/showthread...hreadid=569352