How do I modify URL in a Web Query macro

[mseward]

I've recorded a macro to download stock data from MSN and it works great. However, I want to be able to use the same macro to download data for other stocks based on data entered in a given cell in Excel.

I would like to enter a stock symbol in cell B7 and have it download the same information as seen below (for MSFT in example) for the symbol the user enters into a certain cell on the spreadsheet.

I believe the line with the URL needs to be modified to make the query dynamic, but can't remember the exact changes.

I know this is a simple request for most of you. Please forgive me as I am just learning

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://moneycentral.msn.com/investor/invsub/results/hilite.asp?Symbol=MSFT" _
, Destination:=Range("G7"))
.Name = "hilite.asp?Symbol=MSFT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """pg"",3,5,6,7,8"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
​

 

[John_w]

For a dynamic web query (i.e. the query refreshes automatically when the value of a cell changes) you could use the method described here - http://www.jkp-ads.com/Articles/WebQuery.asp. All done via the Excel user interface and no VBA required.

You haven't said what you want to do with the stock data when it is retrieved. Successive refreshes of the web query will overwrite the destination cells, so if you want to copy the data to another sheet before that happens you'll need to use the VBA method on that page* and add some code to copy the data, or you could use something similar to http://www.mrexcel.com/forum/showthread.php?t=412633.

* - In my experience, despite .RefreshOnChange = True, this type of web query doesn't automatically refresh, and an explicit .Refresh is required to make it refresh.