macro ?

[jfk]

Used MS Office 97 since 97.
Never tried to create and complicated macros.
I found the one below in a public domain spreadsheet.
The following error open when the macro runs:

Ambiguous name detected: GetData.

The second Sub GetData() is higlighted in blue.

Any help appreciated.
I can upload the ss to my website if need be.

dlw

Sub Module1()
Sub GetData()

Sub GetData()
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Set DataSheet = ActiveSheet

Range ("C8")
i = 8
qurl = "http://finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + Range("C2")
Range("C1") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" &
qurl, Destination:=DataSheet.Range("C7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Range("C7").CurrentRegion.TextToColumns
Destination:=Range("C7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False,
other:=False


'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
' Range("C7:H2000").Select
' Selection.Sort Key1:=Range("C8"), Order1:=xlAscending,
Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("C:C").ColumnWidth = 10
Range("h2").Select

End Sub

 

[Anne Troy]

Delete the very first 2 lines:
Sub Module1()
Sub GetData()

"Sub" is the name and you only need it once until after you have an End Sub.
For each Sub, you need an End Sub at the end of the procedure. You've just
got 2 too many Subs, and the ambiguous name means you have two with the same
name. Just delete those two lines at the beginning.

*******************
~Anne Troy

www.OfficeArticles.com

 

[jfk]

That worked, thank you.
Now there is an error 'Invalid use of property'
Range ("C8") in the macro is marked in blue.
Okay, originally it was Range ("C7")
And that is where you started entering data.
Now it starts at C8.
I tried to name C8, C8 but Excel will not accept the name C*

Any ideas?
I will post the ss at my website if need be.

Thanks again,
dlw

 

[Bob Phillips]

Not tried it myself, but try this


Sub GetData()
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Set DataSheet = ActiveSheet

Range("C8").Select
i = 8
qurl = "http://finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + Range("C2")
Range("C1") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & _
qurl, Destination:=DataSheet.Range("C7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Range("C7").CurrentRegion.TextToColumns _
Destination:=Range("C7"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
other:=False

'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
' Range("C7:H2000").Select
' Selection.Sort Key1:=Range("C8"), _
' Order1:=xlAscending, _
' Header:=xlGuess, _
' OrderCustom:=1, _
' MatchCase:=False, _
' Orientation:=xlTopToBottom
Columns("C:C").ColumnWidth = 10
Range("h2").Select

End Sub

 

[Don Guillett]

Just goto the files section in http://groups.yahoo.com/group/xltraders/
and look for donald36 as author and the free file YahooMultipleQuotes

 

[Anne Troy]

C8 is a cell reference, not a named range. If you want it to go to a named
range, create a named range (that isn't a cell reference for its name) and
change C8 to the name of the range. Or just change the C8 to the cell you
want to use.
*******************
~Anne Troy

www.OfficeArticles.com