- ©«¤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
|
¥»©«³Ì«á¥Ñ GBKEE ©ó 2017-12-21 17:37 ½s¿è
¦^´_ 1# paul3063
¸Õ¸Õ¬Ý- Option Explicit
- Sub Ex_¤é¦¬½L»ù¤Î¤ë¥§¡¦¬½L»ù()
- Dim oXmlhttp As Object, oHtmldoc As Object, surl, i, E, r As Double, c As Double
- Dim StockNo As String, xday As String, xRow As Integer, Day1 As Date, Day2 As Date, xTime As Date
- StockNo = [A2]
- Day1 = ActiveSheet.[B2]
- Day2 = ActiveSheet.[C2]
- For i = 0 To DateDiff("m", Day1, Day2)
- xday = Format(DateAdd("m", i, Day1), "yyyymmdd")
- Set oXmlhttp = CreateObject("msxml2.xmlhttp")
- Set oHtmldoc = CreateObject("htmlfile")
- surl = "http://www.twse.com.tw/exchangeReport/STOCK_DAY_AVG?response=html&date=" & xday & "&stockNo=" & StockNo
- With oXmlhttp
- .Open "Get", surl, False
- .Send
- If InStr(.responseText, "«Ü©êºp¡A¨S¦³²Å¦X±ø¥óªº¸ê®Æ!") Then
- MsgBox "«Ü©êºp¡A¨S¦³²Å¦X±ø¥óªº¸ê®Æ!" & vbLf & "½ÐÀˬd ªÑ²¼¥N¸¹"
- Exit Sub
- ElseIf InStr(.responseText, "¬d¸ß¤é´Á¤p©ó88¦~1¤ë5¤é¡A½Ð«·s¬d¸ß") Then
- MsgBox "¬d¸ß¤é´Á¤p©ó88¦~1¤ë5¤é!" & vbLf & "½ÐÀˬd °_©l¤é´Á"
- Exit Sub
- ElseIf InStr(.responseText, "¬d¸ß¤é´Á¤j©ó¤µ¤é¡A½Ð«·s¬d¸ß") Then
- MsgBox "¬d¸ß¤é´Á¤j©ó¤µ¤é" & vbLf & "½ÐÀˬd ²×¤î¤é´Á"
- Exit Sub
- End If
- oHtmldoc.write .responseText
- End With
- With oHtmldoc
- Set E = .all.tags("table")(0)
- With ActiveSheet
- If i = 0 Then .UsedRange.Offset(2).Clear
- xRow = .Cells(Rows.Count, "a").End(xlUp).Row + IIf(i = 0, 1, 0)
-
- For r = IIf(i = 0, 0, 2) To E.Rows.Length - 2 '-1 ¥iÅã¥Ü¤ë¥§¡¦¬½L»ù
- For c = 0 To E.Rows(r).Cells.Length - 1
- .Cells(xRow + r + IIf(i > 0, -1, 0), c + 1) = E.Rows(r).Cells(c).innertext
- Next
- Next
- End With
- End With
- Set oXmlhttp = Nothing
- Set oHtmldoc = Nothing
- Application.StatusBar = "**** " & Format(DateAdd("m", i, Day1), "ee/mm") & " ¸ü§¹²¦ *****"
- '**** ªÑ¥«Àç·~®É¶¡¦³¬y¶qºÞ¨î **
- 'xTime = Time + #12:00:09 AM# '¶¡¹j 10¬í
- 'Do : DoEvents: Loop Until Time > xTime
- '**********©Î¬O¤U¦¡**********************
- 'Application.Wait Now + #12:00:09 AM#
- '********************************
- Next
- MsgBox "ok"
- End Sub
½Æ»s¥N½X |
|