Excel web query results destination

Excel Help for Excel Web Query Results Destination in Automating Data Analysis Tasks

Excel Web Query Results Destination

Rate this: (3/5 from 1 vote)

	Im trying to use excel to parse some data using queries.  What I have is a column of numbers which I use as the source for another query. The query needs to look at each cell in that column (lets say A1:A50 for example) and run the query with the number in each cell. I have a query that does this for me and Ill post the code for it below however, it puts the results of each and every query in the same cell (overwriting previous data). What I need to do is find a way to make the query results start at a cell such as C1 and move down as it runs to C2, C3 etc.  Is this possible? How would I go about doing this? My code: VBA Code: Private Sub CommandButton1_Click()     For Each cell In [A1:A50]         cell.Select         Range("Query_param").Value = cell.Value         Range("PRPT_query").Offset(0, 0).Select         Selection.QueryTable.Refresh BackgroundQuery:=False     Next cell End Sub "query_param" is a "named range" for the cell B1. The Query looks in this cell for the variable to be used in the web query. "PRPT_Query" is the name of my webquery which has a destination cell of D1. All the source data is in A1 through A50. I would actually prefer to use an IQY file for my queries but I cant figure out how to do that and still get the multiple queries to run in a loop.
	Posted by jtzako on 13 Aug 2006

Replies - Displaying 1 to 4 of 4

Order Replies By: Most recent | Chronological | Highest Rated

	on server with asp ? Rate this: (3/5 from 1 vote) is there any way ...do this on server with asp ?
	azbuzazbuz
	Posted by azbuz on 05 Apr 2007

What I did... Rate this: (3/5 from 1 vote) VBA Code: Sub stuff_Lookup() 'clear previous data     Sheets("Scrubber").Select     Range("I14:S100").Select     Application.CutCopyMode = False     Selection.ClearContents     Range("A9").Select 'Begin the query     Set dest = Range("I14")     For Each cell In [G14:G64]         cell.Select         Range("A9").Value = cell.Value 'Check for end of WTN list         If Range("A9") = "" Then GoTo Line1 Else     With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.stuff.com/support/api/stuff.cgi?item=[""item""]", Destination:=dest)         .RefreshStyle = xlOverwriteCells         .WebFormatting = xlWebFormattingNone         .AdjustColumnWidth = False         .PreserveFormatting = True     With ActiveSheet.QueryTables(1).Parameters(1)     .SetParam xlRange, Range("A9")     End With         .BackgroundQuery = True         .Refresh     End With Line1:     Range("A1").Select     Set dest = dest.Offset(1, 0)     Next cell End Sub Thats the code I ended up using and it works great.  I used the For/Next loop to make it run the query a certain number of times. Then I set the "destination" for the query as a variable that is moved down 1 cell each loop.  This makes my query results go down by 1 cell from the original one I defined. (using the offset command)  I generate the query itself in the code rather than using a query made in excel wizards also. Otherwise this wouldnt work at all.

Posted by jtzako on 02 Sep 2006

	Pass variable using VBA code Rate this: (3/5 from 1 vote) You can pass a cell variable to a web query using VBA code.  See the post here for an example of doing this. How did you solve the incremental query results destination issue in your first post? This could be interesting to other users that find this post.
	Excel Business Forums Administrator
	Posted by Excel Helper on 01 Sep 2006

Source for IQY query? Rate this: (3/5 from 1 vote) I figured out how to increment the destination by accident... so Im good there, now I have another issue. I want my web query IQY file to point to cell B1 on my spreadsheet to get its 'variable' example: The searched url is:  http://www.myserver.com/search?stuff=["stuff"]  "stuff" is the variable.  Right now it prompts in a dialog box for me to enter that value. If I do enter the value it works fine.  However, I want it to look in Cell B1 for that value and not prompt me. Anyone know how to do this in an IQY file? Or get the VB code to pass that variable to the IQY query so it doesnt prompt?

Posted by jtzako on 13 Aug 2006

Displaying page 1 of 1