Hi,
I use an Excel Macro which updates stock prices from the internet by using the existing Web Query functionality in Excel. (Data => Import External Data=> New Web Query) I use another macro to trigger the macro (called: "UpdateWebPrices") below . Everything works fine and the prices are updated adn displayed nice and easy in the Excel sheet. So far so 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 the Visual Basic Macro itself:

Example:
I would like to update different Stock prices from yahoo.com in my Excel sheet. Each stock has a specific Quote name which is unique for every stock. For example the US company General Motor is called GM and 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 Google = http://finance.yahoo.com/q?s=GOOG

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

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

2. Click on a button which execute a Macro which then triggers the 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” as http://finance.yahoo.com/q?s=GM and I would like to be able to change that address from a specific cell in my sheet. The manual work around is of course to go into the WebQuery itself and change the Adress manually – or go direct into the Visual Basic Module and manually change the URL from
"URL;http://finance.yahoo.com/q?s=GM", to "URL;http://finance.yahoo.com/q?s=GOOG".

BUT:
The question is if it is possible to change the Web-address direct from a cell in Excel instead? I will use the function =CONCATENATE(A1;B1) in 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 GOOG (This is the cell I would like to update by manually adding for example 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,
Sven