CODEWITHSUNDEEP
excelvba
Trying_hard
Trying_hard

Reputation: 9501

DATA Connection and MSN

I am trying to populate the MSN data connection that Excel has. I want to copy a list of symbols from another sheet and use that in the data feed, just like I would put in separated by commas, to get up to date prices.

This is the data feed macro, but I can't figure out how to enter the symbols.

 With ActiveSheet.QueryTables.Add(Connection:= _
    "FINDER;C:\Program Files\Microsoft Office\Office14\QUERIES\MSN MoneyCentral   Investor Stock Quotes.iqy" _
    , Destination:=Range("$A$1"))
    .Name = "MSN MoneyCentral Investor Stock Quotes"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 1
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingAll
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = True
    .Refresh BackgroundQuery:=False
End With
End Sub

Upvotes: 0

Views: 963

Answers (1)

datatoo
datatoo

Reputation: 2049

If you run this from Sheet1 and the Range of stock symbols are in Sheet2 The file you reference in your question,

"C:\Program Files\Microsoft Office\Office14\QUERIES\MSN MoneyCentral Investor Stock Quotes.iqy"

If you open it, refers to this underlying URL anyway, so just skip the reference locally and use this, and it should work for different versions as well. 

Sub getQuotes()
Dim quotStr
For Each cell In Sheets("Sheet2").Range("A1:A5") 'whatever range you want
quotStr = cell + ", " + quotStr
Next
quotStr = Left(quotStr, (Len(quotStr) - 2))

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=" & quotStr _
        , Destination:=Range("$A$1"))
        .Name = "MSN MoneyCentral Investor Stock Quotes"

---etc

Upvotes: 1

Related Questions