- ©«¤l
- 90
- ¥DÃD
- 16
- ºëµØ
- 0
- ¿n¤À
- 114
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- sp2
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-7-9
- ³Ì«áµn¿ý
- 2018-10-7
|
¥»©«³Ì«á¥Ñ white5168 ©ó 2012-5-27 00:48 ½s¿è
À³Æ[²³n¨D,¶K¥Xµ{¦¡½X,Åwªï¦U¦ì¦h¦h«ü±Ð
¦bSheet1¶Kªº¤º®e- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Address = "$B$1" Then '§PÂ_¥Ø«e¨Ï¥ÎªÌ©Ò¿é¤Jªº¦ì¸m¬O§_¦b$B$1,¦pªG¬O,«h±NB1¿é¤Jªº¥N½X¶Ç¦ÜGetStockPrice¼Ò²Õ¤¤,¤Ï¤§¤£§@°Ê§@
- Call GetStockPrice(Range("B1")) '¶Ç¤JªÑ²¼¥N½X,©I¥sGetStockPrice¼Ò²Õ
- End If
- End Sub
½Æ»s¥N½X ¦bModule¿é¤Jªº¤º®e- Sub GetStockPrice(ByVal stockid As String)
- '
- ' GetStockPrice Macro
- ' Amin ¦b 2012/5/6 ¿ý»sªº¥¨¶°
- Call ClearQueryTablesData
-
- '³z¹L "¥~³¡¶×¤J¸ê®Æªº¤èªk" (´N¬O¨Ï¥ÎQueryTablsªº¤èªk) ±N¾ú¥vªÑ»ù±q2000/1/1¨ì¥Ø«e¬°¤îªº¤é´Á,¸g¥Ñ Yahoo finance §ì¦^¨Ó¦s¶iexcel¬¡¶Ã¯¤¤,¦pªGºô¯¸¸ê®Æ¦³°ÝÃD,§ì¦^¨Óªº¤]·|¬O¿ù»~ªº¸ê®Æ
- With ActiveSheet.QueryTables.Add(Connection:= _
- "TEXT;http://ichart.finance.yahoo.com/table.csv?s=" & stockid & "&a=00&b=4&c=2000&d=" & Month(Date) & "&e=" & Day(Date) & "&f=" & Year(Date) & "&g=d&ignore=.csv" _
- , Destination:=Range("A3"))
- .Name = "stockprice"
- .FieldNames = True
- .RowNumbers = False
- .FillAdjacentFormulas = False
- .PreserveFormatting = True
- .RefreshOnFileOpen = False
- .RefreshStyle = xlOverwriteCells
- .SavePassword = False
- .SaveData = True
- .AdjustColumnWidth = True
- .RefreshPeriod = 0
- .TextFilePromptOnRefresh = False
- .TextFilePlatform = 950
- .TextFileStartRow = 1
- .TextFileParseType = xlDelimited
- .TextFileTextQualifier = xlTextQualifierDoubleQuote
- .TextFileConsecutiveDelimiter = False
- .TextFileTabDelimiter = True
- .TextFileSemicolonDelimiter = False
- .TextFileCommaDelimiter = True
- .TextFileSpaceDelimiter = False
- .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
- .TextFileTrailingMinusNumbers = True
- .Refresh BackgroundQuery:=False
- End With
- End Sub
- Sub ClearQueryTablesData() '§PÂ_·í«e¬¡¶Ã¯¤¤¬O§_¦³«e¤@µ§ªº¸ê®Æ,Y¦³«h²M°£,¤Ï¤§ª½±µ¤U¸üªÑ»ù
- Dim n As Integer
- If Range("A4") <> "" Then '§PÂ_¥Ø«eªº¬¡¶Ã¯¬O§_¦³¸ê®Æ¦s¦b, ³o¦æ¥i¥H¦A¼gªº§óÂÔ·V,Åwªï¦U¦ì¦Û¦æקï
- n = ActiveSheet.Range("A4").End(xlDown).Row '¿ï¨ú¥Ø«e¬¡¶Ã¯±qA4¦ì¸m¨ì³Ì«á¤@¦æªº½d³ò
- For Each gt In ActiveSheet.QueryTables '¿ï¨ú¥ÎQueryTables§ì¨úªº¨C¤@¦æ
- gt.Delete '±N¨Ï¥ÎQueryTables¤èªk©Ò²£¥Íªº¦æ¶i¦æ§R°£,ÁקKQueryTables¥Î¤[¤F,³y¦¨¨t²Î°ï¿n¤@°ïQueryTablesªº©U§£,¦p¦¹¨t²Î¤~¤£·|ÅܺC(¦U¦ìÀ³¸Ó³£¤]·Pı)
- Next
- ActiveSheet.Range("A4:G" & n).Clear '²M°£©Ò¿ï¨úªº¸ê®Æ
- End If
- End Sub
½Æ»s¥N½X |
|