Editing Data source from imported web query

[Orisopha]

Hi,

I've currently got an Excel sheet with a table I've imported from a
web page I've created. This was done using the Data->Import External
Data->New Web Query. The page itself is a CGI script and as such
takes arguments in the address.

I was wondering if anyone knows how I can alter that URL from a cell
in Excel. Specifically I want to be able to change the hostname in a
cell in Excel and the corresponding ?hostname='blah' to change.

Any ideas would be greatly appreciated.

Jim

 

[immanuel]

It seems to me that you'll need a macro to do what you're trying to
accomplish.

Let's say I have inserted a new web query on Sheet1. The data source is
[http://cobrands.hoovers.com/global/cobrands/ap/factsheet.xhtml?Ticker=orcl]
and I name the query "MyWebQuery" (in Data -> Import External Data -> Data
Range Properties...).

In cell H1, I insert "ORCL".

Now, right-click Sheet1 and click View Code. Insert the following:

Sub UpdateData()
Sheet1.QueryTables.Item("MyWebQuery").Connection = _

"URL;http://cobrands.hoovers.com/global/cobrands/ap/factsheet.xhtml?Ticker="
& _
Sheet1.Cells(1, 8)
Sheet1.QueryTables.Item("MyWebQuery").Refresh
End Sub

Run UpdateData whenever you want to refresh your data. Enter different
stock symbols (e.g. MSFT) in H1 and run the macro again to test.

An more elegant solution may involve creating an event handler to handle
changes in H8.

/i.