GBKEE

¦^´_ 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

GBKEE

¦^´_ 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

GBKEE

¦^´_ 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

GBKEE

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

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