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?
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.
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.