Macro won't run from CommandButton
 

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:D").Select
Selection.Columns.AutoFit
Range("E1").Select
Application.ScreenUpdating = True
Call HangUp
Exit Sub
errRunQuery:


End Sub

Macro won't run from CommandButton
 

UpdateQuotes should be in a general module, not a sheet module or the
Thisworkbook.module.


"Nathan Gutman" wrote in message
...
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:D").Select
Selection.Columns.AutoFit
Range("E1").Select
Application.ScreenUpdating = True
Call HangUp
Exit Sub
errRunQuery:


End Sub

Macro won't run from CommandButton
 

Thanks, problem solved. Needed to set TakeFocusOnClick = False.
But would like to understand why.
Nathan
On Wed, 24 Dec 2003 12:36:25 -0500, "Tom Ogilvy"
wrote:

UpdateQuotes should be in a general module, not a sheet module or the
Thisworkbook.module.


"Nathan Gutman" wrote in message
.. .
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:D").Select
Selection.Columns.AutoFit
Range("E1").Select
Application.ScreenUpdating = True
Call HangUp
Exit Sub
errRunQuery:


End Sub

Macro won't run from CommandButton
 

While always a good practice, especially in xl97, there is nothing in your
code and description that would indicate setting TakeFocusOnClick to false
would clear up your problem.

--
Regards,
Tom Ogilvy

Nathan Gutman wrote in message
...
Thanks, problem solved. Needed to set TakeFocusOnClick = False.
But would like to understand why.
Nathan
On Wed, 24 Dec 2003 12:36:25 -0500, "Tom Ogilvy"
wrote:

UpdateQuotes should be in a general module, not a sheet module or the
Thisworkbook.module.


"Nathan Gutman" wrote in message
.. .
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:D").Select
Selection.Columns.AutoFit
Range("E1").Select
Application.ScreenUpdating = True
Call HangUp
Exit Sub
errRunQuery:


End Sub