Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a little help in my macro please
This is the macro code i'm using to import data from YAHOO into excel
The only thing I need is to be able to run the macro everytime I pres ENTER in any of these 5 boxes (C3, F3, I3, L3, O3) How do I do that? Thanks and any help is appreciated. Sub Stock1() ' ' macro1 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("C3").Value With Sheets("Stock 1").QueryTables(1) .Connection = _ "URL;http://table.finance.yahoo.com/d?a=1...&f=2004&g=d&s= & CoSym .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With End Sub Sub Stock2() ' ' macro1 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("F3").Value With Sheets("Stock 2").QueryTables(1) .Connection = _ "URL;http://table.finance.yahoo.com/d?a=1...&f=2004&g=d&s= & CoSym .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With End Sub Sub Stock3() ' ' Stock3 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("I3").Value With Sheets("Stock 3").QueryTables(1) .Connection = _ "URL;http://table.finance.yahoo.com/d?a=1...&f=2004&g=d&s= & CoSym .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With End Sub Sub Stock4() ' ' macro1 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("L3").Value With Sheets("Stock 4").QueryTables(1) .Connection = _ "URL;http://table.finance.yahoo.com/d?a=1...&f=2004&g=d&s= & CoSym .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With End Sub Sub Stock5() ' ' macro1 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("O3").Value With Sheets("Stock 5").QueryTables(1) .Connection = _ "URL;http://table.finance.yahoo.com/d?a=1...&f=2004&g=d&s= & CoSym .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With End Su -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a little help in my macro please
Rather than pressing enter, this one adds a "Update
Stocks" menu item to call your macros. So all you have to do is right click the mouse while one of these cells is selected. Add this code in the sheet where these cells are located: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) Dim icbc As Object For Each icbc In Application.CommandBars ("cell").Controls If icbc.Tag = "brccm" Then icbc.Delete Next icbc If Not Application.Intersect(Target, Range("k3:v22")) _ Is Nothing Then With Application.CommandBars("cell").Controls _ .Add(Type:=msoControlButton, befo=1, _ temporary:=True) .Caption = "Update Stocks" .OnAction = "GetStock" .Tag = "brccm" End With End If End Sub Then Add this code in a Module: Public Sub GetStock() If ActiveCell.Row < 3 Then Exit Sub c = ActiveCell.Column Select Case c Case 3 Stock1 Case 6 Stock2 Case 9 Stock3 Case12 Stock4 Case15 Stock5 Case Else Exit Sub End Select End Sub Good Luck! -----Original Message----- This is the macro code i'm using to import data from YAHOO into excel. The only thing I need is to be able to run the macro everytime I press ENTER in any of these 5 boxes (C3, F3, I3, L3, O3) How do I do that? Thanks and any help is appreciated. Sub Stock1() ' ' macro1 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("C3").Value With Sheets("Stock 1").QueryTables(1) .Connection = _ "URL;http://table.finance.yahoo.com/d? a=10&b=23&c=2003&d=1&e=24&f=2004&g=d&s=" & CoSym .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With End Sub Sub Stock2() ' ' macro1 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("F3").Value With Sheets("Stock 2").QueryTables(1) .Connection = _ "URL;http://table.finance.yahoo.com/d? a=10&b=23&c=2003&d=1&e=24&f=2004&g=d&s=" & CoSym .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With End Sub Sub Stock3() ' ' Stock3 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("I3").Value With Sheets("Stock 3").QueryTables(1) .Connection = _ "URL;http://table.finance.yahoo.com/d? a=10&b=23&c=2003&d=1&e=24&f=2004&g=d&s=" & CoSym .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With End Sub Sub Stock4() ' ' macro1 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("L3").Value With Sheets("Stock 4").QueryTables(1) .Connection = _ "URL;http://table.finance.yahoo.com/d? a=10&b=23&c=2003&d=1&e=24&f=2004&g=d&s=" & CoSym .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With End Sub Sub Stock5() ' ' macro1 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("O3").Value With Sheets("Stock 5").QueryTables(1) .Connection = _ "URL;http://table.finance.yahoo.com/d? a=10&b=23&c=2003&d=1&e=24&f=2004&g=d&s=" & CoSym .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With End Sub --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a little help in my macro please
try a double click event instead. Right click sheet tabview codeinsert
thisSAVE I don't know the layout and ultimate purpose of this (send me a workbook if you like) but maybe a data validation drop down would work better. If you are trying to get many at once, there are much easier ways than a separate sheet for each symbol. But, it looks like you want one at a time.....???? I work with these projects a lot. I have available an excel historicals program where you just enter the symbols, start date, stop date, and choice of daily, weekly, etc. from a drop down list. Then one mouse click gets all symbols requested at once. It could even chart the different symbols if desired from a double click on the symbol. You can even copy the chart as a .jpg with one mouse click. 90 days is Start Date Stop Date 10/31/2003 1/1/2003 1/29/2004 Date ^TYX ^TnX ^FvX 6/20/2003 4.46 3.40 2.30 6/19/2003 4.42 3.34 2.24 6/18/2003 4.39 3.36 2.30 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Row < 3 Then Exit Sub If Target.Column < 3 And Target.Column < 6 And Target.Column < 9 _ And Target.Column < 12 And Target.Column < 15 Then Exit Sub 'MsgBox ActiveCell cosym = ActiveCell.Value With Sheets(cosym).QueryTables(1) Connection = _ "URL;http://table.finance.yahoo.com/d?a=1...4&f=2004&g=d&s =" & cosym ..WebSelectionType = xlEntirePage ..WebFormatting = xlWebFormattingAll ..WebPreFormattedTextToColumns = True ..WebConsecutiveDelimitersAsOne = True ..WebSingleBlockTextImport = True ..WebDisableDateRecognition = False ..WebDisableRedirections = True ..Refresh BackgroundQuery:=False End With End Sub -- Don Guillett SalesAid Software "qitrader888 " wrote in message ... This is the macro code i'm using to import data from YAHOO into excel. The only thing I need is to be able to run the macro everytime I press ENTER in any of these 5 boxes (C3, F3, I3, L3, O3) How do I do that? Thanks and any help is appreciated. Sub Stock1() ' ' macro1 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("C3").Value With Sheets("Stock 1").QueryTables(1) Connection = _ "URL;http://table.finance.yahoo.com/d?a=1...4&f=2004&g=d&s =" & CoSym WebSelectionType = xlEntirePage WebFormatting = xlWebFormattingAll WebPreFormattedTextToColumns = True WebConsecutiveDelimitersAsOne = True WebSingleBlockTextImport = True WebDisableDateRecognition = False WebDisableRedirections = True Refresh BackgroundQuery:=False End With End Sub Sub Stock2() ' ' macro1 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("F3").Value With Sheets("Stock 2").QueryTables(1) Connection = _ "URL;http://table.finance.yahoo.com/d?a=1...4&f=2004&g=d&s =" & CoSym WebSelectionType = xlEntirePage WebFormatting = xlWebFormattingAll WebPreFormattedTextToColumns = True WebConsecutiveDelimitersAsOne = True WebSingleBlockTextImport = True WebDisableDateRecognition = False WebDisableRedirections = True Refresh BackgroundQuery:=False End With End Sub Sub Stock3() ' ' Stock3 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("I3").Value With Sheets("Stock 3").QueryTables(1) Connection = _ "URL;http://table.finance.yahoo.com/d?a=1...4&f=2004&g=d&s =" & CoSym WebSelectionType = xlEntirePage WebFormatting = xlWebFormattingAll WebPreFormattedTextToColumns = True WebConsecutiveDelimitersAsOne = True WebSingleBlockTextImport = True WebDisableDateRecognition = False WebDisableRedirections = True Refresh BackgroundQuery:=False End With End Sub Sub Stock4() ' ' macro1 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("L3").Value With Sheets("Stock 4").QueryTables(1) Connection = _ "URL;http://table.finance.yahoo.com/d?a=1...4&f=2004&g=d&s =" & CoSym WebSelectionType = xlEntirePage WebFormatting = xlWebFormattingAll WebPreFormattedTextToColumns = True WebConsecutiveDelimitersAsOne = True WebSingleBlockTextImport = True WebDisableDateRecognition = False WebDisableRedirections = True Refresh BackgroundQuery:=False End With End Sub Sub Stock5() ' ' macro1 Macro ' Macro recorded 2/26/2004 by Fongsaiyuk ' Dim CoSym As String CoSym = Worksheets("Main").Range("O3").Value With Sheets("Stock 5").QueryTables(1) Connection = _ "URL;http://table.finance.yahoo.com/d?a=1...4&f=2004&g=d&s =" & CoSym WebSelectionType = xlEntirePage WebFormatting = xlWebFormattingAll WebPreFormattedTextToColumns = True WebConsecutiveDelimitersAsOne = True WebSingleBlockTextImport = True WebDisableDateRecognition = False WebDisableRedirections = True Refresh BackgroundQuery:=False End With End Sub --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a little help in my macro please
For some reason i'm getting a compilation error on both written codes
Where can I send you my worksheet? Can you send me a PM? thank -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a little help in my macro please
Send to the address shown here...........I will be out for a few hours.
-- Don Guillett SalesAid Software "qitrader888 " wrote in message ... For some reason i'm getting a compilation error on both written codes. Where can I send you my worksheet? Can you send me a PM? thanks --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a little help in my macro please
I try refreshing my data and I have found out that my dates are no
updated. How can fix this in my macro? Thank -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |