#1   Report Post  
jfk
 
Posts: n/a
Default macro ?

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

  #2   Report Post  
Anne Troy
 
Posts: n/a
Default

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 wrote in message ...
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



  #3   Report Post  
jfk
 
Posts: n/a
Default

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

On Tue, 26 Jul 2005 21:10:14 -0400, "Anne Troy"
wrote:

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 wrote in message ...
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



  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default

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

--
HTH

Bob Phillips

<jfk wrote in message ...
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



  #5   Report Post  
Don Guillett
 
Posts: n/a
Default

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

--
Don Guillett
SalesAid Software

<jfk wrote in message ...
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





  #6   Report Post  
Anne Troy
 
Posts: n/a
Default

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


<jfk wrote in message ...
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

On Tue, 26 Jul 2005 21:10:14 -0400, "Anne Troy"
wrote:

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 wrote in message ...
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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Make Alignment options under format cells available as shortcut dforrest Excel Discussion (Misc queries) 1 July 14th 05 10:58 PM
Help with macro looping and color query function kevinm Excel Discussion (Misc queries) 10 May 26th 05 01:25 AM
Playing a macro from another workbook Jim Excel Discussion (Misc queries) 1 February 23rd 05 11:12 PM
Date macro Hiking Excel Discussion (Misc queries) 9 February 3rd 05 01:40 AM
Macro and If Statement SATB Excel Discussion (Misc queries) 2 December 3rd 04 05:46 PM


All times are GMT +1. The time now is 10:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"