Home > Forum Home > Automating Data Analysis Tasks > Excel web query results destination Share

Excel web query results destination

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


Forum TopicPost Reply Login

Excel Web Query Results Destination

Rate this:
(3/5 from 1 vote)
ConfusedIm 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 on
 
Replies - Displaying 1 to 4 of 4Order Replies By: Most recent | Chronological | Highest Rated
Confused
Rate this:
(3/5 from 1 vote)
is there any way ...do this on server with asp ?
 azbuzazbuz
 Posted by on
Confused
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 on
Applaud
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 on
Confused
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 on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Excel web query results destination in the