"GTVT06" wrote:

On Apr 3, 6:03 pm, Newq wrote:
"GTVT06" wrote:
On Apr 3, 1:02 pm, Newq wrote:
Does anyone know how to Declare and Call the dll to update stock quotes
combined with a timer so that quotes are automatically updated after the close

I'm not sure which part of MSN your wanting exactly, but here's a VBA
code I put together for you that will grab some US stocks and refresh
them every 15mins or every time you run the macro.

Sub Stocks()
Dim n As Single
Dim exist As Boolean
For n = 1 To Sheets.Count
Sheets(n).Activate
If ActiveSheet.Name = "Stocks" Then
exist = True
End If
Next n
If exist = False Then
Sheets.Add.Name = "Stocks"
End If
Worksheets("Stocks").Activate
Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://moneycentral.msn.com/investor/market/
usindex.aspx", Destination:= _
Range("A1"))
.Name = "usindex"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 15
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "6,7"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Thank you for your help. I will incorporate it into what i am dong and may
be back to you with another question. Thanks again for your fast response

Newq- Hide quoted text -

- Show quoted text -

No problem


I have tried to run your macro and cannot get VBA to accept the "With Active
Sheet" code. It looks like the URL is not accepted. Obviously when I try to
go to this URL I get a 402 error saying that while the URL is recognized it
cannot be opened which makes sense. What am I missing?

I believe I see what your code is doing. To clarify what i am doing I have
a single worksheet with a column of stocks and indices and am updating date,
last day and daily hi, low and Last prices. in subsequent columns. It is my
intention to run the macro once per day after the close of markets to get day
end closing prices, high and low.

Therefore, can you help me with the URL call and how would you modify the
code to accomplish my more precise objective?

NEWQ