Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Fails at this Line: .Refresh BackgroundQuery:=False
Application.ScreenUpdating = False
Dim sngStart As Double sngStart = Now Dim str1 As String Dim str2 As String Dim c As Range Dim Stocks As Range Dim bFound As Boolean Dim ws As Worksheet Set Stocks = Application.InputBox( _ "Type 'Symbols' in the input box below", Type:=8) For Each c In Sheets("Firms, Import").Range("Symbols") bFound = False For Each ws In Worksheets If ws.Name = c.Value Then bFound = True Exit For End If Next ws If bFound = False Then Worksheets.Add.Name = c.Value End If '---------------------------------------------------------- Sheets(c.Value).Select Cells.Select Range("A1:IV65536").ClearContents str1 = "URL;http://finance.yahoo.com/q/ks?s=" & _ c.Value With ActiveSheet.QueryTables.Add(Connection:=str1 _ , Destination:=Range("A1")) .Name = str1 .Name = "ks?s=c.Value" .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = True On Error GoTo Errorhandler: .Refresh BackgroundQuery:=False End With Range("B:D").Select Selection.Delete Shift:=xlToLeft €˜etc€¦€¦€¦€¦. Code sometimes fails on this line: ..Refresh BackgroundQuery:=False (just a few lines above) Im baffled; Im not sure why this happens. Sometimes the code runs fine, and completes all import request (stock symbols listed in a column) and sometimes it fails for no apparent reason. I added a few simple error handlers (On Error Resume Next) and then the code ceases to do anything at all. Does anyone know what may cause this behavior? TIA, Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Fails at this Line: .Refresh BackgroundQuery:=False
After doing several more tests and some research on the web, I came to the
conclusion that it just fails when it is trying to find stocks that are not followed very faithfully, or not well researched, or the like. It appears to work fine for all the 'blue chips' and the popular securities; it definitely becomes unreliable when querying for lesser known issues. I guess it's a suggestion, of sorts, to stay away from those esoteric securities. -- RyGuy "ryguy7272" wrote: Application.ScreenUpdating = False Dim sngStart As Double sngStart = Now Dim str1 As String Dim str2 As String Dim c As Range Dim Stocks As Range Dim bFound As Boolean Dim ws As Worksheet Set Stocks = Application.InputBox( _ "Type 'Symbols' in the input box below", Type:=8) For Each c In Sheets("Firms, Import").Range("Symbols") bFound = False For Each ws In Worksheets If ws.Name = c.Value Then bFound = True Exit For End If Next ws If bFound = False Then Worksheets.Add.Name = c.Value End If '---------------------------------------------------------- Sheets(c.Value).Select Cells.Select Range("A1:IV65536").ClearContents str1 = "URL;http://finance.yahoo.com/q/ks?s=" & _ c.Value With ActiveSheet.QueryTables.Add(Connection:=str1 _ , Destination:=Range("A1")) .Name = str1 .Name = "ks?s=c.Value" .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = True On Error GoTo Errorhandler: .Refresh BackgroundQuery:=False End With Range("B:D").Select Selection.Delete Shift:=xlToLeft €˜etc€¦€¦€¦€¦. Code sometimes fails on this line: .Refresh BackgroundQuery:=False (just a few lines above) Im baffled; Im not sure why this happens. Sometimes the code runs fine, and completes all import request (stock symbols listed in a column) and sometimes it fails for no apparent reason. I added a few simple error handlers (On Error Resume Next) and then the code ceases to do anything at all. Does anyone know what may cause this behavior? TIA, Ryan--- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
.Refresh BackgroundQuery:=False | Excel Programming | |||
.Refresh BackgroundQuery:=False | Excel Programming | |||
Problems with .Refresh BackgroundQuery:=False | Excel Worksheet Functions | |||
Refresh BackgroundQuery fails | Excel Programming | |||
Excel does not close from VB!! (when i refresh Refresh query with BackgroundQuery:=False) | Excel Programming |