ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð°Ý¦p¦ó¥HVBAÂ^¨úºô¯¸¤Wªº¸ê®Æ¡H

[µo°Ý] ½Ð°Ý¦p¦ó¥HVBAÂ^¨úºô¯¸¤Wªº¸ê®Æ¡H

¥»©«³Ì«á¥Ñ mnsmrtl ©ó 2011-4-23 12:11 ½s¿è

¦pÃD¡A§Ú§Æ±æ¦bEXCEL¿é¤JªÑ²¼¥N½X¡AµM«á¥i¥H±qÃÒ¥æ©Ò©Î¬O©_¼¯µ¥ºô¯¸§ì¨ú·í¤é¦¬½Lµ¥¼Æ¾Ú
¤£¹L§¹¥þ¤£ª¾¹D±q¦ó¤U¤â¡A½Ð¦U¦ì¥ý¶i¤£§[«ü±Ð¡AÁÂÁÂ
¤£¶i«h°h

©_¼¯ºô¯¸ªººô¸ô¤W¦³¤H´£¨Ñ
¦ý¬O§Ú¸òÂd¶R¤¤¤ßªº¤ñ¹ï¦³ÂI®t²§
¤£ª¾¹D­ì¦]¬O¤°»ò
¤£µM¨º¦UÀɯuªº¬OÅý¤H§ìªº¬Û·í¦n¥Î
¤Wºô§ä¤@¤U´N¦³
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 1# mnsmrtl
¤£¤@©w­nVBA
EX1.GIF
2011-4-21 16:56

TOP

·PÁ¨â¦ìªº¦^ÂСA¤£¹L§ÚÁÙ¬O§Æ±æ³z¹LVBA¨C¤éÂ^¨ú
¦Ü©óºô¸ô¤W§ä¨ìªºÀɮסA§Ú¤]¦³§ì¨ì¹L¡A¤£¹LµLªk¶}±Ò
¦ü¥G¯Ê¤F«Ü¦h¤¸¥ó¡A¥Ñ©ó§ÚVBAªº¥\¤O¤£¨¬¡A­n­×§ï¤]µL±q¤U¤â
©Ò¥H·Q¦Û¤v¾ÇµÛ¼g¼g¬Ý¡AÁٽд£¨Ñ«Øij¡AÁÂÁÂ
¤£¶i«h°h

TOP

¦^´_ 4# mnsmrtl
  1. Sub ·s¼W¬d¸ß()
  2.     Range("A1") = "2610"   'ªÑ²¼¥N¸¹
  3.     Range("A3").Select
  4.     With ActiveSheet.QueryTables.Add(Connection:= _
  5.         "URL;http://tw.stock.yahoo.com/q/q?s=" & Range("a1"), Destination:=Selection)  '·s¼W¬d¸ß
  6.         .FieldNames = True
  7.         .RowNumbers = False
  8.         .FillAdjacentFormulas = False
  9.         .PreserveFormatting = True
  10.         .RefreshOnFileOpen = False
  11.         .BackgroundQuery = True
  12.         .RefreshStyle = xlInsertDeleteCells
  13.         .SavePassword = False
  14.         .SaveData = True
  15.         .AdjustColumnWidth = True
  16.         .RefreshPeriod = 0
  17.         .WebSelectionType = xlSpecifiedTables
  18.         .WebFormatting = xlWebFormattingNone
  19.         .WebTables = "6"
  20.         .WebPreFormattedTextToColumns = True
  21.         .WebConsecutiveDelimitersAsOne = True
  22.         .WebSingleBlockTextImport = False
  23.         .WebDisableDateRecognition = False
  24.         .WebDisableRedirections = False
  25.         .Refresh BackgroundQuery:=False
  26.         .Name = .ResultRange.Cells(3, 1)
  27.     End With
  28.    End Sub
  29. '***** ¤w·s¼W«á¬d¸ß«á ¥i­×§ïA1ªºªÑ²¼¥N¸¹ ¬d¸ß  *****
  30. Sub §ó·s¬d¸ß()
  31.     With Range("A3").QueryTable
  32.         .Connection = "URL;http://tw.stock.yahoo.com/q/q?s=" & Range("a1") 'ªÑ²¼¥N¸¹¦b[A1]
  33.         .Refresh BackgroundQuery:=False
  34.         .Name = .ResultRange.Cells(3, 1)
  35.     End With
  36. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ mnsmrtl ©ó 2011-4-23 12:14 ½s¿è

·PÁª©¥D¡A¸g´ú¸Õ«á¤w¸gok
¥t¥~·Q¦A½Ð±Ð¡A¦p¦ó¥u¦^¶Ç¨ä¤¤¤@­ÓÄæ¦ì¡H
¦pªG¸Óºô¯¸Åܧó®æ¦¡ªº¸Ü¡A.querytableªº¤l¶µ¥Ø¬O¤£¬O­n­«·s­×§ï¡H
¦Ówebtables="6"¡A­n«ç»ò¨ú±o¡H
¤£¶i«h°h

TOP

¦^´_ 6# mnsmrtl
¥t¥~·Q¦A½Ð±Ð¡A¦p¦ó¥u¦^¶Ç¨ä¤¤¤@­ÓÄæ¦ì¡H
½Ð°Ñ¦Ò http://forum.twbts.com/thread-3064-1-2.html

webtables="6"¡A­n«ç»ò¨ú±o¡H

EX1.GIF
2011-4-23 17:26



EX2.GIF
2011-4-23 17:27

TOP

·PÁª©¥D«ü¾É~
¤£¶i«h°h

TOP

·PÁµo¤å.¯uªº¥i¥H¥Î..¬O§_ÁÙ¦³¦P®É¯à§ì¦hÀɪº¤èªk?

TOP

ª©¥D¤j¤j§A¦n¡G
       
   ¤p§Ì¹ï³z¹LEXCEL¨ú±oºô­¶¸ê®Æ¡A
¤@ª½²`²`·P¨ì¿³½ì¦ý¬OµLªk»â·|¤Î¬ð¯}§x¹Ò¡C
   ²{·Q­É¥Î¦¹ªO¤ÎªO¥D¤j¤j´£¨Ñªº»yªk¡C
¨Ã¨ú±o¦¹ºô­¶ªº«ü©w¦ì¸m¤º®e¦p¤U¡G

¤@¡B
   ºô§}¡Ghttp://tw.stock.yahoo.com/d/s/company_1102.html

¤G¡B
   ¨ú±o¦¹ºô­¶ªº¸ê®Æªí¬°

Àò §Q ¯à ¤O (100²Ä2©u) ³Ì·s¥|©u¨CªÑ¬Õ¾l  ³Ìªñ¥|¦~¨CªÑ¬Õ¾l

¤T¡B
  ¤p§Ì¦³¸ÕµÛª½±µ±N¤U¦C»yªkªººô§}§ó¥¿
¦p¤Wªººô§}¦ý¬OµLªk¦¨¥\¡C
¯à½Ðª©¥D¤j¤j«üÂI¤p§Ì¡C

·P®¦¤j¤j¡G


Sub ·s¼W¬d¸ß()
    Range("A1") = "2610"   'ªÑ²¼¥N¸¹
    Range("A3").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://tw.stock.yahoo.com/q/q?s=" & Range("a1"), Destination:=Selection)  '·s¼W¬d¸ß
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "6"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
        .Name = .ResultRange.Cells(3, 1)
    End With
   End Sub
'***** ¤w·s¼W«á¬d¸ß«á ¥i­×§ïA1ªºªÑ²¼¥N¸¹ ¬d¸ß  *****
Sub §ó·s¬d¸ß()
    With Range("A3").QueryTable
        .Connection = "URL;http://tw.stock.yahoo.com/q/q?s=" & Range("a1") 'ªÑ²¼¥N¸¹¦b[A1]
        .Refresh BackgroundQuery:=False
        .Name = .ResultRange.Cells(3, 1)
    End With
End Sub

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD