[µo°Ý] ºô­¶¤U¸ü«á¨S¦³ªF¦è

pupai

¦U¦ì¦n
µ{¦¡½X¦p¤U¡A¤U¸ü¸ê®Æ«á¡A¬OªÅªÅªº¡A¤£ª¾¸Ó§ï­þ
©Ò¥H·Q½Ð¦U¦ìÀ°À°¦£
ÁÂÁÂ!! ·P®¦

1. Sub abc()
   
2.     Dim Theurl As String, Sdate
   
3.     Dim URL As String, xDate As String, A As Date
   
4.     A = Range("A2").Value
   
5.     xDate = Format(A, "E/MM/DD")
   
6.     Theurl = "http://www.otc.org.tw/ch/stock/3insti/DAILY_TradE/3itrade_download.php?t=D&d=" & xDate & "&s=0"
   
7. 
   
8. 
   
9.     With ActiveSheet.QueryTables.Add(Connection:="URL;" & Theurl, Destination:=Range("B3"))
   
10.         .BackgroundQuery = True
    
11.         .RefreshStyle = xlOverwriteCells
    
12.         .RefreshPeriod = 0
    
13.         .AdjustColumnWidth = False
    
14.         .WebSelectionType = xlSpecifiedTables
    
15.         .WebFormatting = xlWebFormattingNone
    
16.         '.WebTables = "1,2,3,4"
    
17.         .WebDisableDateRecognition = True         'Ãö³¬¤é´Á¿ëÃÑ
    
18.         .Refresh BackgroundQuery:=False
    
19.         .ResultRange.Columns(1).TextToColumns Destination:=.ResultRange.Cells(1), DataType:=xlDelimited, _
    
20.                 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    
21.                 Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    
22.                 :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
    
23.                 Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
    
24.                 ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
    
25.                 (20, 1)), TrailingMinusNumbers:=True
    
26.             'TextToColumns ¤èªk (¸ê®Æ­åªR)ªº°Ñ¼Æ¥i¿ý»s±o¨ì
    
27.             'ResultRange.Columns(1):weby¸ê®Æªº²Ä¤@Äæ
    
28.             'ResultRange.Cells(1)  :weby¸ê®Æªº²Ä¤@­ÓCells(Àx¦s®æ)
    
29.             '.Delete
    
30.             
    
31.      
    
32. 
    
33. 
    
34.     End With
    
35. End Sub

½Æ»s¥N½X

joey0415

¦^´_ 1# pupai
ºô§}¤£¹ï

1. Sub ¥¨¶°1()
   
2.     With ActiveSheet.QueryTables.Add(Connection:= _
   
3.         "URL;http://www.gretai.org.tw/web/stock/3insti/daily_trade/3itrade_print.php?l=zh-tw&t=D&d=103/11/28&s=0,asc,0" _
   
4.         , Destination:=Range("$A$1"))
   
5.         .WebFormatting = xlWebFormattingNone
   
6.         .WebPreFormattedTextToColumns = True
   
7.         .Refresh BackgroundQuery:=False
   
8.         .Delete
   
9.     End With
   
10. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 2# joey0415

¤U¸ü (40 KB)

2014-12-5 15:27

ºô§}¤£¹ï
Workbooks.Open ("http://www.gretai.org.tw/web/stock/3insti/daily_trade/3itrade_print.php?l=zh-tw&t=D&d=103/11/28&s=0,asc,0")
    Workbooks.Open ("http://www.gretai.org.tw/web/stock/3insti/daily_trade/3itrade_hedge_download.php?l=zh-tw&t=D&d=103/12/03&s=0,asc,0")

pupai

¦^´_ 3# GBKEE


    ½Ð°ÝG¤j
±zªº¤è¦¡­n¦p¦ó­×§ï
¥i§_¦A¸É¥R»¡©ú
ÁÂÁÂ!!

pupai

¦^´_ 2# joey0415


    ÁÂÁ±zªº¦^ÂÐ

GBKEE

¦^´_ 4# pupai
¥i¥Î¶}±ÒÀɮתº¤è¦¡

1. Option Explicit
   
2. Sub abc()
   
3.     Dim Theurl As String, xDate As String, AR()
   
4.     Range("A2") = "2014/12/4"  
   
5.     xDate = Format(Range("A2"), "E/MM/DD")
   
6.     Theurl = "http://www.gretai.org.tw/web/stock/3insti/daily_trade/3itrade_hedge_download.php?l=zh-tw&t=D&d=" & xDate '& "&s=0"
   
7.             
   
8.     Application.ScreenUpdating = False
   
9.     With Workbooks.Open(Theurl)
   
10.         AR = .Sheets(1).UsedRange.Value
    
11.         .Close False
    
12.     End With
    
13.     With ActiveSheet
    
14.         .UsedRange.Clear
    
15.         .[A1].Resize(UBound(AR, 1), UBound(AR, 2)) = AR
    
16.     End With
    
17.     Application.ScreenUpdating = True
    
18. End Sub

½Æ»s¥N½X

pupai

¦^´_ 6# GBKEE


   G ¤j
¤F¸Ñ   ·P®¦!!