- ©«¤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
|
¦^´_ 6# pupai - Option Explicit
- Sub GetData()
- Dim DataSheet As Worksheet, Sh As Worksheet, Msg As Boolean
- Dim EndDate As Date, StartDate As Date, i As Integer, AR, xR As Long
- Dim Symbol As String, Qur As String
- Set DataSheet = Sheets("Sheet1")
- With DataSheet
- StartDate = .[b1]
- EndDate = .[b2]
- Symbol = .[b3]
- .Range("D1").CurrentRegion = ""
- End With
- '¥»¸ê®Æ¦Û¥Á°ê94¦~09¤ë01¤é¶}©l´£¨Ñ *** °£¿ù ***
- If StartDate < #9/1/2005# Or EndDate < #9/1/2005# Or Len(Symbol) <= 3 Or StartDate > EndDate Or EndDate > Date Then
- MsgBox "¼Æ¾Ú¦³»~" & IIf(StartDate < #9/1/2005#, vbLf & "StartDate :¤é´Á ¤p©ó 94¦~09¤ë01¤é ", "") & _
- IIf(EndDate < #9/1/2005#, vbLf & "EndDate :¤é´Á ¤p©ó 94¦~09¤ë01¤é ", "") & _
- IIf(Len(Symbol) <= 3, vbLf & "Symbol : ªÑ²¼¥N¸¹ ", "") & _
- IIf(StartDate > EndDate, vbLf & "StartDate > EndDate", "") & _
- IIf(EndDate > Date, vbLf & " EndDate >" & Date, "")
- Exit Sub
- End If
- '*********************************************
- Set Sh = Sheets.Add(Sheets(1))
- DataSheet.Activate
- Do While DateSerial(Year(StartDate), Month(StartDate), 1) <= EndDate
- Qur = "http://www.twse.com.tw/ch/trading/exchange/BWIBBU/BWIBBU.php?myear=" & Format(StartDate, "yyyy") & "&mmon=" & Format(StartDate, "m") & "&STK_NO=" & Symbol
- With Sh
- If .QueryTables.Count = 0 Then
- .QueryTables.Add "URL;" & Qur, .[A1]
- Else
- .QueryTables(1).Connection = "URL;" & Qur
- Msg = True
- End If
- With .QueryTables(1)
- .WebFormatting = xlWebFormattingNone
- .WebSelectionType = xlSpecifiedTables
- .WebDisableDateRecognition = True
- .WebTables = "8"
- .Refresh BackgroundQuery:=False
- If Application.CountA(.ResultRange) = 0 Then Msg = False
- If Msg Then
- AR = .ResultRange.Offset(2)
- Else
- AR = .ResultRange
- End If
- With DataSheet
- xR = Application.CountA(.[d:d]) + 1
- .Cells(xR, "D").Resize(UBound(AR, 1), UBound(AR, 2)) = AR
- End With
- End With
- End With
- StartDate = DateAdd("m", 1, StartDate)
- Loop
- Application.DisplayAlerts = False
- Sh.Delete
- Application.DisplayAlerts = True
- End Sub
½Æ»s¥N½X |
|