Peter T;630667 Wrote:

A simple WebQuery should get this for you, followed by a simple routine
to
copy the data to the last row on the prices sheet. Have a go with the
following, post back if not sure where to put it or how to run it.

Sub GetGoldPrice()
Dim wsWQ As Worksheet
Dim wsPrices As Worksheet
Dim qt As QueryTable
Dim wb As Workbook

Set wb = ActiveWorkbook
On Error Resume Next
Set wsWQ = wb.Worksheets("KitcoQuery")
If wsWQ Is Nothing Then
Set wsWQ = wb.Worksheets.Add
wsWQ.Name = "KitcoQuery"
End If


Set qt = wsWQ.QueryTables("KitcoGoldPrice")
If qt Is Nothing Then
If AddKitcoQT(wsWQ, qt) = False Then
MsgBox "Failed to add WebWuery"
Exit Sub
End If
Else
qt.BackgroundQuery = False
qt.Refresh
End If

Set wsPrices = wb.Worksheets("GoldPrices")
If wsPrices Is Nothing Then
Set wsPrices = wb.Worksheets.Add
wsPrices.Name = "GoldPrices"
wsPrices.Range("A1:H1").Value = wsWQ.Range("C4:J4").Value
End If

wsPrices.Parent.Activate
wsPrices.Activate

nLastRow = wsPrices.Range("A60000").End(xlUp).Row + 1

wsWQ.Range("C6:J6").Copy _
Destination:=wsPrices.Range(Cells(nLastRow, 1), _
Cells(nLastRow, 8))


End Sub

Function AddKitcoQT(ws, qt) As Boolean
On Error GoTo errH
Set qt = ws.QueryTables.Add(Connection:= _
"URL;http://www.kitco.com/market", Destination:=ws.Range("A1"))
With qt
.Name = "KitcoGoldPrice"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

AddKitcoQT = True
Exit Function
errH:
End Function


Regards,
Peter T

"angelfire20" wrote in message
...
Hi,

I want to set up an excel ssheet to automatically pull price data
from a
website into a cell. Furthermore, each day I want the new price to
be
added
into the next available cell down from yesterday's update, in order
for me
to
keep a historical record and graph it.

Eg. on 2 Jan I want the script to update the gold price from
'Live Market Quotes' (http://www.kitco.com/market/) to cell B5 and
save. On 3 Jan I want it to
update the price into cell B6 and save, without overwriting B5, and
so on.

Is this possible (using a macro or something), and if so what must I
do.

Please provide easy to follow steps as I'm clueless! (-;

Thanks,





I am working on a similar project, could you please share where the
different sections of code ought to be placed?

Thank you,

Scott


--
ScottO
------------------------------------------------------------------------
ScottO's Profile: http://www.thecodecage.com/forumz/member.php?u=2194
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=175527

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---