Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) |