Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries in Modules
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 -- Tomski ------------------------------------------------------------------------ Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824 View this thread: http://www.excelforum.com/showthread...hreadid=569352 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries in Modules
"Tomski" wrote in
message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries in Modules
I'm sorry I can't actually see your answer, unless I'm being a complet 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 t me why I can't see it. Thanks To -- Tomsk ----------------------------------------------------------------------- Tomski's Profile: http://www.excelforum.com/member.php...fo&userid=2682 View this thread: http://www.excelforum.com/showthread.php?threadid=56935 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries in Modules
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 ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries in Modules
Sorry it's taken so long for me to get back to you, only just had tim at work to sort out my code. But it's working now, so that' brilliant. Thanks ever so much for you help. Cheers T: -- Tomsk ----------------------------------------------------------------------- Tomski's Profile: http://www.excelforum.com/member.php...fo&userid=2682 View this thread: http://www.excelforum.com/showthread.php?threadid=56935 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Public, Private, Event modules, Forms modules,,, | Excel Programming | |||
Basic question - modules and class modules - what's the difference? | Excel Programming | |||
When to code in sheet or userform modules and when to use modules | Excel Programming | |||
Modules | Excel Programming | |||
Class Modules vs Modules | Excel Programming |