Retrieve Data from Web site

[Les Stout]

Hi all, i need to retrieve a certain figure from the following site
"http://www.lme.com/lead.asp"
I need to take the fValue for the "CASH BUYER" and insert it in a
Worksheet called "LEAD" in the workbook "TM-20 infosheet.xls" in column
"B" at the end of the data, with yesterday's date on the left in column
"A", however if it is a Monday then it must be Friday's date. If
possible i need to do this with the spreadsheet closed.

Any help and assistance with code would be appreciated.

Les Stout

 

[Tim Williams]

What part are you having a problem with?

Web data can be got with a web query (Data >> Import External Data >> New web query).
Refreshing the query (once set up) will get the latest value.

It's likely you'll have to open the other file to update the value.

'****************************************************
dim wb as workbook, r as range

set wb= workbooks.open("C:\stuff\TM-20 infosheet.xls")
with wb.sheets("LEAD")
set r = .cells(.rows.count,2).end(xlup).offset(1,0)
r.value = thisworkbook.sheets("WebQuery").range("D34").value
r.offset(0,-1).value=GetYesterdaysDate()
end with

wb.save
wb.close
'*****************************************************

Function GetYesterdaysDate()
'some code here....
end function


Tim

 

[Les Stout]

Thank you Tim, sorry for the delay... live in South Africa

Les Stout