Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update a Web Query and be able to change the WebAddress manually from a specific Cell
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 -- Sve ----------------------------------------------------------------------- Sven's Profile: http://www.excelforum.com/member.php...nfo&userid=954 View this thread: http://www.excelforum.com/showthread.php?threadid=52453 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to update one manually-entered value from another? | Excel Worksheet Functions | |||
I can only manually update? | Excel Programming | |||
Manually Update Listindex | Excel Programming | |||
Manually update links | Excel Worksheet Functions | |||
Stop to modify the SQL query manually entered into query ! | Excel Programming |