- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¦^´_ 1# cji3cj6xu6
קï¦p¤U- Option Explicit
- Sub GGetPrice()
- Dim StartYear, StockNumber As Integer, URL As String, xlMonth As String, R As Integer, R1 As Integer
- StartYear = 2013
- StartYear = DateSerial(StartYear, 1, 0) '°_©l¤é=>¥h¦~³Ì«á¤@¤Ñ
- StockNumber = 1101 'ªÑ²¼¥N¸¹
- Sheets(1).Cells.Clear '²M°£±ý¦s©ñªº¶±
- On Error Resume Next '¥~³¡¬d¸ßªººô§}¦³»~·|¦³¿ù»~(¤é´Á¶W¹L)
- Do While Err.Number = 0
- StartYear = DateAdd("M", 1, StartYear) '°_©l¤éªº¤U¤@Ó¤ë¤é´Á
- xlMonth = Format(StartYear, "YYYYMM") & "/" & Format(StartYear, "YYYYMM")
- URL = "URL;http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/genpage/Report" & xlMonth & "_F3_1_8_" & StockNumber & ".php?STK_NO=" & StockNumber & "&myear=" & Format(StartYear, "YYYY") & "&mmon=" & Format(StartYear, "MM")
- With Sheets(2) '²M°£§ì¨ú¸ê®Æ¦s©ñªº¶±
- If .QueryTables.Count = 0 Then
- With .QueryTables.Add(URL, .[A1])
- .Refresh BackgroundQuery:=False
- End With
- End If
- With .QueryTables(1)
- .Connection = URL
- .WebSelectionType = xlSpecifiedTables
- .WebFormatting = xlWebFormattingNone
- .WebTables = "8"
- .WebPreFormattedTextToColumns = False
- .WebConsecutiveDelimitersAsOne = False
- .WebSingleBlockTextImport = False
- .WebDisableDateRecognition = True
- .WebDisableRedirections = True
- .Refresh BackgroundQuery:=False
- With .ResultRange
- R = Application.CountA(Sheets(1).[A:A])
- R1 = IIf(R = 0, 3, 4)
- .Rows(R1).Resize(.Rows.Count - R1 + 1).Copy Sheets(1).Cells(R + 1, 1)
- End With
- End With
- End With
- Loop
- Sheets(1).Columns.AutoFit
- MsgBox "OK!"
- End Sub
½Æ»s¥N½X |
|