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

½Ð°Ý¦p¦óºI¨ú«ü©w°Ñ¼Æºô¸ô¤º®e¤§«ü©wÄæ¦ì¸ê®Æ?

½Ð°Ý¦p¦óºI¨ú«ü©w°Ñ¼Æºô¸ô¤º®e¤§«ü©wÄæ¦ì¸ê®Æ?

¥»©«³Ì«á¥Ñ peter460191 ©ó 2012-1-4 13:44 ½s¿è

¹ï·Ó¤£¦P°Ñ¼ÆºI¨ú«ü©wÄæ¦ì¸ê®Æ.rar (3.63 KB)

½Ð°Ý¦p¦ó«ö AÄ檺°Ñ¼Æ¡AºI¨úºô¦û¤º®e¤§J Äæ¤ÎOÄæ©Ò¹ïÀ³¦~«×ªÑ§Q¤§¸ê®Æ
¬d¸ßºô§}¡÷http://justdata.yuanta.com.tw/z/zc/zcc/zcc_1101.asp.htm¡÷¥N¸¹°Ñ¼Æ¥iÅÜ
peter460191

¦^´_ 1# peter460191
  1. Sub Ex()
  2.     Dim E As Range, Ar()
  3.     With Sheets("Sheet1")
  4.         For Each E In .Range(.[A6], .[A6].End(xlDown))   'ªÑ²¼¥N¸¹°Ï°ì
  5.             With Sheets("Sheet2").[A1].QueryTable        'Sheet2").[A1] ½Ð³]¦³¥~³¡¬d¸ß
  6.                 .Connection = "URL;http://justdata.yuanta.com.tw/z/zc/zcc/zcc_" & E & ".asp.htm"
  7.                 .WebSelectionType = xlSpecifiedTables
  8.                 .WebFormatting = xlWebFormattingNone
  9.                 .WebTables = "4"
  10.                 .WebPreFormattedTextToColumns = True
  11.                 .WebConsecutiveDelimitersAsOne = True
  12.                 .WebSingleBlockTextImport = False
  13.                 .WebDisableDateRecognition = False
  14.                 .WebDisableRedirections = False
  15.                 .Refresh BackgroundQuery:=False
  16.                 With .ResultRange
  17.                     Ar = .Range(.Cells(2, 6), .Cells(6, 6)).Value   '¦~«×ªÑ§Q¦X­p½d³ò
  18.                 End With
  19.             End With
  20.             E.Offset(, 2).Resize(1, 5) = Application.Transpose(Ar)  '¦~«×ªÑ§Q¦X­p ½Æ»s©ó ªÑ²¼¥N¸¹°Ï°ì¦V¥k2Äæ(1¦C,5Äæ)
  21.         Next
  22.     End With
  23. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# GBKEE

·PÁ GBKEE ª©¤j

¸Õ¹L«á¤w¥i¦Û°Ê§ì¨ú¸ê®Æ¡AÁÂÁ±z!
¥t¥~¡A­Y­n¦b¡y¥N¸¹¡z°Ñ¼Æ§ïÅÜ«á¡A¦Û°ÊÅܧó¸ê®Æ¡AÁÙ­n¥[¨º¨Çµ{¦¡¡A½ÐÀ°¦£!
¦A¤@¦¸ÁÂÁ±z!
peter460191

TOP

¦^´_ 3# peter460191
¶·½Æ»s¨ì Sheet1
  1. Option Explicit
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     Application.EnableEvents = False          'False  «ü©wª«¥ó¤£¯àIJµo¨Æ¥ó
  4.     'EnableEvents ÄÝ©Ê ¦pªG«ü©wª«¥ó¯àIJµo¨Æ¥ó¡A«h¥»Äݩʬ° True
  5.     If Not Intersect(Target(1), Range([A6], [A6].End(xlDown))) Is Nothing Then Ex Target(1)
  6.       'Intersect ¤èªk ¶Ç¦^ Range ª«¥ó¡A¦¹ª«¥ó¥Nªí¨â­Ó©Î¦h­Ó½d³ò­«Å|ªº¯x§Î½d³ò¡C
  7.     Application.EnableEvents = True           'True  «ü©wª«¥ó¯àIJµo¨Æ¥ó
  8. End Sub
  9. Private Sub Ex(E As Range)  '±µ¨ü°Ñ¼Æ E As Range
  10.     Dim Ar()
  11.     With Sheets("Sheet2").[A1].QueryTable        ' ***Sheet2").[A1] ½Ð³]¦³¥~³¡¬d¸ß***
  12.         .Connection = "URL;http://justdata.yuanta.com.tw/z/zc/zcc/zcc_" & E & ".asp.htm"
  13.         .WebSelectionType = xlSpecifiedTables
  14.         .WebFormatting = xlWebFormattingNone
  15.         .WebTables = "4"
  16.         .WebPreFormattedTextToColumns = True
  17.         .WebConsecutiveDelimitersAsOne = True
  18.         .WebSingleBlockTextImport = False
  19.         .WebDisableDateRecognition = False
  20.         .WebDisableRedirections = False
  21.         .Refresh BackgroundQuery:=False
  22.         With .ResultRange
  23.             Ar = .Range(.Cells(2, 6), .Cells(6, 6)).Value   '¦~«×ªÑ§Q¦X­p½d³ò
  24.         End With
  25.     End With
  26.     E.Offset(, 2).Resize(1, 5) = Application.Transpose(Ar)  '¦~«×ªÑ§Q¦X­p ½Æ»s©ó ªÑ²¼¥N¸¹°Ï°ì¦V¥k2Äæ(1¦C,5Äæ)
  27.     '³o¸Ì Sheets("Sheet1")¦³¿é¤J: ·|IJ°ÊSheets("Sheet1")ªº  Private Sub Worksheet_Change
  28.     'Application.EnableEvents = False ->  ¤£·|IJµoSheets("Sheet1")ªº  Private Sub Worksheet_Change
  29. End Sub
½Æ»s¥N½X

TOP

¦U¦ì¼Ó¥D¤j¤j±z¦n¡G

   ¤p§Ì³o´X¤é¤@ª½Æp¬ã¦¹½g´T
¤´µMµLªk»â·|¡AÁٽЦU¦ì¤j¤j«üÂI
°g¬z¡C
¤@¡B²Ä¤G½gª©¥D´£¨ì­n¦bsheet2¸Ì
'Sheet2").[A1] ½Ð³]¦³¥~³¡¬d¸ß
À³¦p¦ó¼W³]©O¡H
¤p§Ì¤w¼W¦C:d:\temp\zcc_1101.iqy
WEB
1
http://justdata.yuanta.com.tw/z/zc/zcc/zcc_1101.asp.htm

Selection=4
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

¤G¡B½Ð°Ý¦¹ºô­¶¬°¦ó¨Ã¥¼¦³­º­¶
¥iµn¤J©O¡H

·P®¦¦U¦ì¤j¤j¡H

TOP

¦^´_ 5# dechiuan999
Sheets("Sheet2").[A1] ½Ð³]¦³¥~³¡¬d¸ß  À³¦p¦ó¼W³]©O¡H  ¤p§Ì¤w¼W¦C:d:\temp\zcc_1101.iqy
¨ìSheet2 ¿ï¾ÜA1  
«ü¥O ¸ê®Æ->¶×¤J¥~³¡¸ê®Æ->¶×¤J¸ê®Æ  :  [¿ï¨ú¸ê®Æ¨Ó·½] ¶}±Ò   d:\temp\zcc_1101.iqy

TOP

ÁÂÁª©¥D¤j¤j¡C
«ü¥O ¸ê®Æ->¶×¤J¥~³¡¸ê®Æ->¶×¤J¸ê®Æ  :  [¿ï¨ú¸ê®Æ¨Ó·½] ¶}±Ò   d:\temp\zcc_1101.iqy
²Ä¤G½g»yªk¤w´ú¦¨¥\¡C

¥t²Ä¥|½gªº»yªk¡A¤À¬°¤G¬q
²Ä¤@¬q¤p§Ì±N¨äÂ\©ñ¦bsheets1¸Ìªº¤u§@ªí¨Æ¥ó¡A
¦Ó²Ä¤G¬q¤p§Ì©ñ¸m¦b¼Ò²Õ¤º¡A¦ýµLªk°õ¦æ¦¹¼Ò²Õ¡C¨Ã¦ba7¿é¤JªÑ²¼¥N¸¹
¤§«á«oµL¤ÏÀ³¡A¦pªG¥þ³¡±N¨äÂ\©ñ¦Üsheet1¤u§@ªí¨Æ¥ó¤º
¤]¬O¤£¦æ¡C½Ð°Ý¨ä­ì¦]¬°¦ó©O¡H

·P®¦¤j¤j¡I

TOP

¦^´_ 7# dechiuan999
¦Ó²Ä¤G¬q¤p§Ì©ñ¸m¦b¼Ò²Õ¤º¡A¦ýµLªk°õ¦æ¦¹¼Ò²Õ

Private Sub Ex(E As Range)  '±µ¨ü°Ñ¼Æ E As Range   §R±¼ Private

TOP

ÁÂÁª©¥D¤j¤j¡C
¤p§Ì¤w¿í·Óª©¥D«ü¥Ü
¤]¬O¨S¦³°Ê§@
¥i¯à¬O¦b®a¸Ì¨Ï¥Î
excel 2002ª©ªºÃö«Y§a¡I
§Ú©ú¤Ñ¨ì¤½¥q§ï¥Î
excel2010ª©¸Õ¸Õ

·P®¦ª©¥D¤j¤j¡I

TOP

¦^´_ 4# GBKEE


    ÁÂÁÂGBKEEª©¤jÀ°¦£!
   ¤w¥i¥H°õ¦æÀH¥N¸¹Åܰʸê®Æ,¤@©w­nªá´X¤Ñ¦n¦n¬ã¨s¤º®e, ¦Û¤v¸ÕµÛ§âªÑ¦W¤]Åã¥Ü¥X¨Ó, ¦A¤@¦¸ÁÂÁª©¤j!
peter460191

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤Hªº§Ö¼Ö¡D¤£¬O¦]¬°¥L¾Ö¦³±o¦h¡A¦Ó¬O¦]¬°¥L­p¸û±o¤Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD