Excel web query results destination

Excel Help for Excel web query results destination in Automating Data Analysis Tasks

Find Excel Solutions

Search the Forums:
	Search
Recent Activity:
Delete Cell Items In Spreadsheet
Importing Data From Pdf Files
New Portfolio Optimization Template Version
Financial Market Data Feed
Run Time Error 1004
Data Downloader Crashes Excel

Follow Business Spreadsheets On:

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

Forum Topic

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

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

	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

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

	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

Displaying page 1 of 1

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

Find Excel templates
and add-ins in the
Excel Business Solutions Directory

© 2013 Business Spreadsheets. All Rights Reserved. Legal | About Us