Macro - Yahoo stock update for each worksheet

[Mustang65]

I looked around and did not find another post like this. I have a spreadsheet that contains a worksheet for each stock I am watching. Currenly I manually update each worksheet every morning with the Yahoo closing stock quote from the previous day. (http://ichart.finance.yahoo.com/table.csv?s=bac&d=12&e=31&f=2012&g=d&a=0&b=1&c=2007&ignor=.csv) Each worksheet has a link with the stock for that worksheet, I then cut and paste the closing information into that work sheet. I have created a macro (pretty basic as I am not a programmer) and I am encountering a RUN TIME error '1004'. The debugger shows the first Yahoo file as open, as it is trying to open download the data from the second worksheet. The Macro shows closing it, prior to running the code for the next worksheet, but probably not enough time is passing before it is retrieving Yahoo data for the next worksheet. I tried adding a delay, but that did not work. I would try using VB, but that is beyond me. If you have a better idea for coding this, please do.
Any thoughts? Thanks for any assistance you may offer.
Don

Here is the line of code that the debugger highlited:
Workbooks.Open Filename:= _
"http://ichart.finance.yahoo.com/table.csv?s=met&d=12&e=31&f=2012&g=d&a=0&b=1&c=2007&ignor=.csv"
which is the first Yahoo request.

Here is what the error msg states:

A document with the name 'table.csv' is already open. You can not open 2 documents with the same name... To open the second document, either close the document that is currently open or rename one of the documents.

I am running this on a QuadCore 64 bit computer so I do not think that speed is an issue here.
-------------------------------
Sub Udate_Worksheets()
'
' Udate_Worksheets Macro
' Macro recorded 11/29/2013 by Don
'
'
Rows("8:8").Select
Selection.Insert Shift:=xlDown
Range("B4").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Workbooks.Open Filename:= _
"http://ichart.finance.yahoo.com/table.csv?s=met&d=12&e=31&f=2012&g=d&a=0&b=1&c=2007&ignor=.csv"
ActiveWindow.Visible = False
Windows("table.csv").Visible = True
Range("A2:G2").Select
Selection.Copy
ActiveWindow.Close
Range("A8").Select
ActiveSheet.Paste
Sheets("JCP Data").Select
Rows("8:8").Select
Selection.Insert Shift:=xlDown
Range("B4").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Workbooks.Open Filename:= _
"http://ichart.finance.yahoo.com/table.csv?s=jcp&d=12&e=31&f=2012&g=d&a=0&b=1&c=2007&ignor=.csv"
ActiveWindow.Visible = False
Windows("table.csv").Visible = True
Range("A2:G2").Select
Selection.Copy
ActiveWindow.Close
Range("A8").Select
ActiveSheet.Paste
Sheets("BAC Data").Select
Rows("8:8").Select
Selection.Insert Shift:=xlDown
Range("B4").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Workbooks.Open Filename:= _
"http://ichart.finance.yahoo.com/table.csv?s=bac&d=12&e=31&f=2012&g=d&a=0&b=1&c=2007&ignor=.csv"
ActiveWindow.Visible = False
Windows("table.csv").Visible = True
Range("A2:G2").Select
Selection.Copy
ActiveWindow.Close
Range("A8").Select
ActiveSheet.Paste
ActiveWorkbook.Save
End Sub

 

[alvin-chung]

Hi, have you tried using Excel built-in function to import data from a webpage then you can manipulate the data easier thereafter using formula/vba?
Get external data from a Web page - Excel - Office.com