ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

½Ð°Ýquerytalbe.add¶×¤J ªÑ²¼¥N½X®æ¦¡°ÝÃD

½Ð°Ýquerytalbe.add¶×¤J ªÑ²¼¥N½X®æ¦¡°ÝÃD

½Ð°Ý¦U¦ì«e½ú:
querytalbe.add¶×¤JªÑ²¼¦¨¥æ»ù®É ,¥xÆW50ªÑ²¼¥N½X0050Åܦ¨50°ÝÃD
½Ð°Ýºô­¶¸ê®Æ¶×¤J®É,¤@¨Ç querytable.add¨äÄݩʳ]©w¤è¦¡
ÁÂÁÂ!

¦¨¥æ»ù.rar (126.57 KB)

ªþ¥ó

¥Î³o¸Õ¬Ý¬Ý .WebSelectionType = xlEntirePage

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-9-21 16:51 ½s¿è

¦^´_ 1# ten999
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim DATE_REQ  As Date, yyyymm As String, yyyymmdd As String, yyymmdd As String
  4.     Dim URL As String, A As Object, E As String, i As Integer, ii As Integer, Sh As Worksheet, t As Date
  5.     On Error GoTo IE_ER
  6.     DATE_REQ = Date
  7.     Do
  8.         If Weekday(DATE_REQ, vbMonday) > 5 Then DATE_REQ = DATE_REQ - 1  '¨ú±oÀç·~¤é
  9.     Loop Until Weekday(DATE_REQ, vbMonday) <= 5
  10.     DATE_REQ = CDate(InputBox("½Ð¿é¤J¥æ©ö¤é´Á, ®æ¦¡ 2011/9/6", , DATE_REQ))
  11.     yyyymm = Year(DATE_REQ) & Format(Month(DATE_REQ), "00")
  12.     yyyymmdd = Year(DATE_REQ) & Format(Month(DATE_REQ), "00") & Format(Day(DATE_REQ), "00")
  13.     yyymmdd = Year(DATE_REQ) - 1911 & "/" & Format(Month(DATE_REQ), "00") & "/" & Format(Day(DATE_REQ), "00")
  14.     URL = "http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/genpage/Report" & yyyymm & "/A112" & yyyymmdd & "ALLBUT0999_1.php?select2=ALLBUT0999&chk_date=" & yyymmdd
  15.     Set Sh = ActiveSheet
  16.     Sh.Cells.Clear
  17.     Application.StatusBar = " µ¥­Ôºô­¶...."
  18.     t = Time
  19.     With CreateObject("InternetExplorer.Application")
  20.         .Navigate URL
  21.     '    .Visible = True
  22.         Do While .Busy Or .ReadyState <> 4
  23.             DoEvents
  24.         Loop
  25.         If .document.Title = "HTTP 404 §ä¤£¨ì" Then 'IE8 ÂsÄý¾¹
  26.             .Quit
  27.             GoTo IE_ER
  28.         End If
  29.         Do
  30.             Set A = .document.getElementsByTAGName("TABLE")(9)
  31.         Loop While A Is Nothing
  32.         With Sh
  33.             For i = 0 To A.Rows.Length - 1
  34.                 For ii = 0 To A.Rows(i).Cells.Length - 1
  35.                    '.Cells(i + 1, "a").Select
  36.                     E = Trim(A.Rows(i).Cells(ii).innerText)    'ºô­¶ªº¦r¦ê
  37.                     .Cells(i + 1, ii + 1) = E '   A.Rows(i).Cells(II).innerText
  38.                     'ºô­¶ªº¦r¦êÂà¦s¨ìÀx¦s®æ,"0050"µø¬°¼Æ¦r¦Û°Ê¥h°£"00"
  39.                     If ii = 0 And Cells(i + 1, ii + 1).Text <> E Then
  40.                         .Cells(i + 1, ii + 1).NumberFormatLocal = "@"  'Àx¦s®æ®æ¦¡§ï¬°¤å¦r
  41.                         .Cells(i + 1, ii + 1) = E                      '­«·sµ¹¤Wºô­¶ªº¦r¦ê
  42.                     End If
  43.                     Application.StatusBar = Application.Text(Time - t, "[s]") & "¬í ¸ê®Æ¤U¸ü...." & .Cells(i + 1, ii + 1)
  44.                 Next
  45.             Next
  46.         End With
  47.         .Quit
  48.     End With
  49.     Application.StatusBar = Application.Text(Time - t, "[s]") & "¬í ¸ê®Æ¤U¸ü§¹²¦"
  50.     Exit Sub
  51. IE_ER:  '¤é´Á¿ù»~
  52.     MsgBox IIf(Err = 13, "¤é´Á ¨ú®ø¿é¤J", DATE_REQ & " ¤é´Á ¦³»~")
  53.     Application.StatusBar = False
  54. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD