I have some VBA code that pulls stock quotations from Yahoo into Excel. The code has been running on Excel 2003 for years. Recently, I copied the application to a new desktop running W10, Office 2003, 2007 and 2013. It runs fine there under 2003. I also copied the application to a laptop running W10, Office 2003 and 2007. Most of the functions work on the laptop fine except the query that pulls data from Yahoo. The first time I run it, I get the '-2147417848 (80010108) automation error (object invoked disconnected' error. If I reset the macro and run again, I get the '1004 - Application - defined or object-defined error'. Thereafter, I can't even save or close the workbook. Excel hangs and has to be killed.
Here's some code:
sDate = "100115"
eDate = "100915"
smo = Int(Left(sDate, 2))
sda = Int(Right(Left(sDate, 4), 2))
syr = Int(Right(sDate, 2))
emo = Int(Left(eDate, 2))
eda = Int(Right(Left(eDate, 4), 2))
eyr = Int(Right(eDate, 2))
With Sheets("Web Data")
.Range("A:Z").Delete
theGetDataUrl = _
"http://ichart.finance.yahoo.com/table.csv?" & "a=" & smo - 1 & "&b=" & sda & "&c=" & syr _
& "&d=" & emo - 1 & "&e=" & eda & "&f=" & eyr & "&g=d&s=" & "RAD"
Do
Set YahooQuery = .QueryTables _
.Add(Connection:= _
"URL;" & theGetDataUrl, Destination:=.Cells(1, 1))
With YahooQuery
'.WebSelectionType = xlAll
.WebSelectionType = xlAllTables
'.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
'.WebSingleBlockTextImport = True
.WebSingleBlockTextImport = False
'.WebDisableDateRecognition = True
.WebDisableDateRecognition = False
.RefreshStyle = xlOverwriteCells
.FieldNames = True
'.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
'.RefreshOnFileOpen = False
.SavePassword = False
'.SaveData = True
'.RefreshPeriod = 0
.AdjustColumnWidth = True
.WebDisableRedirections = False
'.BackgroundQuery = True
.Refresh BackgroundQuery:=False
End With
Loop Until .Cells(1, 1) <> ""
End With
The error occurs on the 'Refresh' command.
Thanks for any help or suggestions.
Bookmarks