Update a Web Query and be able to change the WebAddress manually from a specific Cell

[Sven]

Hi,
I use an Excel Macro which updates stock prices from the internet b
using the existing Web Query functionality in Excel. (Data => Impor
External Data=> New Web Query) I use another macro to trigger the macr
(called: "UpdateWebPrices") below . Everything works fine and the price
are updated adn displayed nice and easy in the Excel sheet. So far s
god….

BUT:
I would like to be able to change the used Address (Web-Address
manually from a specific Cell in Excel – without having to go into th
Visual Basic Macro itself:

Example:
I would like to update different Stock prices from yahoo.com in m
Excel sheet. Each stock has a specific Quote name which is unique fo
every stock. For example the US company General Motor is called GM an
the company Google is called GOOG.

The Web-Address for the company General Motors
http://finance.yahoo.com/q?s=GM
The Web-Address for the company Googl
= http://finance.yahoo.com/q?s=GOOG

As seen in the two Web-addresses above the only difference is tha
General Motors has “GM” and Google “GOOG” in the end of eac
Web-address.

What I would like to do is:
1. Have a cell in Excel where I manually enter GM or GOOG etc. I ente
the text in for example the Cell “C1”

2. Click on a button which execute a Macro which then triggers th
following the Recorded Visual Basic Macro named “UpdateWebPrices”



Sub UpdateWebPrices()
Range("C1").Select
ActiveCell.FormulaR1C1 = "http://finance.yahoo.com/q?s=GM"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=GM"
Destination:=Range("D1"))
.Name = "Stock Price"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub



As seen in the recorded Macro above the Address is “hard-coded” a
http://finance.yahoo.com/q?s=GM and I would like to be able to chang
that address from a specific cell in my sheet. The manual work aroun
is of course to go into the WebQuery itself and change the Adres
manually – or go direct into the Visual Basic Module and manuall
change the URL from
"URL;http://finance.yahoo.com/q?s=GM", t
"URL;http://finance.yahoo.com/q?s=GOOG".

BUT:
The question is if it is possible to change the Web-address direct fro
a cell in Excel instead? I will use the function =CONCATENATE(A1;B1) i
Cell C1 to create the Web-Address itself.
The Sheet is built up with the following cells:

Example:
Cell A1:
Display the first part of the Address with the text:
http://finance.yahoo.com/q?s=

Cell B1:
Display the last part of the Address: for example the text GM or GOO
(This is the cell I would like to update by manually adding for exampl
GM

Cell C1:
Use the Excel function CONCATENATE (A1,B1) which display
http://finance.yahoo.com/q?s=GM

Cell D1:
Is the first Cell the Stock price data should be put into.


Does anyone know how this can be done please?
Many thanks in advance,
Sve