N
Nathan Gutman
When I go to Alt-F8 and select and run the UpdateQuotes macro it runs
fine.
I added CommandButton1 with the following simple code:
Private Sub CommandButton1_Click()
Call UpdateQuotes
End Sub
When I click the button I get an error message:
"TextToColumns method of Range Class failed."
and it hangs up on the rngB.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited.... line.
Why does it run from Alt-F8 but it doesn't run from the command
button?
Thanks for any help.
Nathan
Sub UpdateQuotes()
Dim strSymbols As String
Dim strURLPrefix As String
Dim strURLSuffix As String
Dim strURL As String
Dim rngA As Range
Dim rngB As Range
Dim wsQ As Worksheet
Application.ScreenUpdating = False
Set wsQ = Sheets("Quotes")
Set rngA = Range(Cells(2, 1), Cells(65536, 1).End(xlUp))
wsQ.Activate
wsQ.Range("B2:J200").ClearContents
'On Error GoTo errRunQuery
strURLPrefix = "http://quote.yahoo.com/d/quotes.cvs?s="
strURLSuffix = "&f=sl1d1ohgv&e=.csv"
strSymbols = ConcatSymbols(rngA)
strURL = strURLPrefix & strSymbols & strURLSuffix
With wsQ.QueryTables.Add(Connection:="URL;" & strURL,
Destination:=wsQ.Cells(2, 2))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Set rngB = Range(Cells(2, 2), Cells(65536, 2).End(xlUp))
rngB.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 9),
Array(5, 9), Array(6, 9), _
Array(7, 9))
Columns("B").Select
Selection.Columns.AutoFit
Range("E1").Select
Application.ScreenUpdating = True
Call HangUp
Exit Sub
errRunQuery:
End Sub
fine.
I added CommandButton1 with the following simple code:
Private Sub CommandButton1_Click()
Call UpdateQuotes
End Sub
When I click the button I get an error message:
"TextToColumns method of Range Class failed."
and it hangs up on the rngB.TextToColumns Destination:=Range("B2"),
DataType:=xlDelimited.... line.
Why does it run from Alt-F8 but it doesn't run from the command
button?
Thanks for any help.
Nathan
Sub UpdateQuotes()
Dim strSymbols As String
Dim strURLPrefix As String
Dim strURLSuffix As String
Dim strURL As String
Dim rngA As Range
Dim rngB As Range
Dim wsQ As Worksheet
Application.ScreenUpdating = False
Set wsQ = Sheets("Quotes")
Set rngA = Range(Cells(2, 1), Cells(65536, 1).End(xlUp))
wsQ.Activate
wsQ.Range("B2:J200").ClearContents
'On Error GoTo errRunQuery
strURLPrefix = "http://quote.yahoo.com/d/quotes.cvs?s="
strURLSuffix = "&f=sl1d1ohgv&e=.csv"
strSymbols = ConcatSymbols(rngA)
strURL = strURLPrefix & strSymbols & strURLSuffix
With wsQ.QueryTables.Add(Connection:="URL;" & strURL,
Destination:=wsQ.Cells(2, 2))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Set rngB = Range(Cells(2, 2), Cells(65536, 2).End(xlUp))
rngB.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 9),
Array(5, 9), Array(6, 9), _
Array(7, 9))
Columns("B").Select
Selection.Columns.AutoFit
Range("E1").Select
Application.ScreenUpdating = True
Call HangUp
Exit Sub
errRunQuery:
End Sub