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

Web ¸ê®Æ ¶×¤J °ÝÃD

Web ¸ê®Æ ¶×¤J °ÝÃD

qq.rar (24.03 KB)
³o¬O§Ú§ï§O¤H¼gªº
¶]¤@¥b·|¶i¤JµL­­°j°é ½Ð°Ý«ç»ò¼g§PÂ_°±¤î±ø¥ó?
²Ä¤G­¶..¤§«á  «ç»ò§R°£«e¨â¦C(­ì¥»¥u§R°£¤@¦C)?
½Ð«e½ú½ç±Ð

.ResultRange(2).EntireRow.Delete   ­ì¥»¬O(1)
§ï¦¨2¤§«áÁÙ¬O§R°£¤@¦C

TOP

¦^´_ 2# seemee
  1. Sub ­ÓªÑ¥æ©ö©ú²Ó¤U¸ü()
  2.     Dim ªÑ²¼¥N¸¹ As String, ¦~ As String, ¤ë As String, N As Name, i As Integer, T As Integer, A
  3.     ¦~ = 2013
  4.     ¤ë = 1
  5.     ¤ë = Format(¤ë, "00")
  6.     ªÑ²¼¥N¸¹ = 2330
  7.    
  8.     T = Time
  9.     With ActiveSheet
  10.         .Cells.Clear
  11.         DoEvents
  12.         'Application.ScreenUpdating = False
  13.         'Application.StatusBar = False
  14.         With .QueryTables.Add(Connection:="URL;http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/genpage/Report" & ¦~ & ¤ë & "/" & ¦~ & ¤ë & "_F3_1_8_" & ªÑ²¼¥N¸¹ & ".php?STK_NO=" & ªÑ²¼¥N¸¹ & "&myear=" & ¦~ & "&mmon=" & ¤ë, Destination:=Range("A1"))
  15.             .BackgroundQuery = True
  16.             .WebTables = "8"
  17.             .Refresh BackgroundQuery:=False
  18.             ActiveSheet.Names(.Name).Delete
  19.         End With
  20.         ¤ë = 2
  21.         
  22.         Do
  23.              ¤ë = Format(¤ë, "00")
  24.             .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Select
  25.             With .QueryTables.Add(Connection:="URL;http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/genpage/Report" & ¦~ & ¤ë & "/" & ¦~ & ¤ë & "_F3_1_8_" & ªÑ²¼¥N¸¹ & ".php?STK_NO=" & ªÑ²¼¥N¸¹ & "&myear=" & ¦~ & "&mmon=" & ¤ë, Destination:=Selection)
  26.                 .BackgroundQuery = True
  27.                 .WebTables = "8"
  28.                 On Error Resume Next
  29.                 Do
  30.                      Err.Clear
  31.                 .Refresh BackgroundQuery:=False
  32.                 If Err.Number = 1004 Then GoTo 10 'µLªk¶}±ÒÀÉ®×´N¸õ¨ì¤U¤@¤ë
  33.                 Loop Until Err.Number = 0
  34.                 On Error GoTo 0
  35.                 If Application.CountA(.ResultRange) = 0 Then GoTo OUT
  36.                 .ResultRange.Rows("1:2").Delete '§R°£1:2¦C
  37.                 ActiveSheet.Names(.Name).Delete
  38. 10
  39.                 ¤ë = ¤ë + 1
  40.             End With
  41.         Loop Until ¤ë > 12
  42. OUT:
  43.         .[A1].Select
  44.         Application.ScreenUpdating = True
  45.         With .UsedRange
  46.             .WrapText = False
  47.             .Interior.ColorIndex = xlNone
  48.             .Font.Size = 12
  49.             .Columns.AutoFit
  50.             A = CreateObject("WScript.Shell").popup("¦@¤U¸ü " & i & " ­¶¶O®É  " & Format(Time - T, "hh:mm¤ÀSS¬í"), 5, ¦~ & "_" & ªÑ²¼¥N¸¹, 48 + 0)
  51.             Application.StatusBar = ¦~ & " _ " & ªÑ²¼¥N¸¹ & " ¦@¤U¸ü " & i & "­¶ ¶O®É " & Format(Time - T, "HH:MM:SS")
  52.         End With
  53.         For Each N In .Names
  54.             N.Delete
  55.         Next
  56.      End With
  57. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁ¤j¤j ¸Ñµª¤F §Ú³o¨â¤Ñªº§xÂZ:D
¤£¤p¤ßª¦¤å§ä¨ì¤U­±ªºµ{¦¡½X
¦ý¬O·sªº°ÝÃD¤S¨ÓÅo ¦A¦¸³Â·Ð¤j¤j¤F
°ÝÃD
1. ?myear=" & Myear & "&mmon=" & Mmon  ³o¬qµ{¦¡½X­n«ç»ò¨ú±o, ¦bºô­¶ªººô§}¨SÅã¥Ü³o¬q
    §Ú¦³½Ð±Ð§O¤H PHP POST ¨S¿ìªkª¾¹D ¦ý¬O ³o¬qµ{¦¡½X¬O«ç»ò§Ë¨Óªº,¦n©_?
2. ¶×¤Jªº¸ê®Æ¼È¦s«ç»ò«ü©w¨ìUÄæ,ABÄæ¶}©l §Ú¬Ý¤£¥X¸Ì­±¦³»yªk«ü©w³o­Ó¦ì¸m?
  1.   Sub ¤j½L¾ú¥v¦¨¥æ¸ê°T_³sÄò¤ë_±Æ§Ç()
  2.     Dim Q(1 To 2) As QueryTable, xlData As Date, Myear, Mmon, i As Integer
  3.     With Sheets("¥[Åv¾ú¥v¦æ±¡")
  4.         .Range("E1").CurrentRegion.Offset(1).Clear
  5.         .Range("E1").CurrentRegion.Select
  6.         Application.ScreenUpdating = False
  7.         Set Q(1) = .QueryTables(1)
  8.         Set Q(2) = .QueryTables(2)
  9.         For i = .[b4] - 1 To 0 Step -1
  10.             xlData = DateAdd("m", -i, DateSerial(.[b1] + 1911, .[b2], 1))
  11.             Myear = Year(xlData) - 1911 '¥Á°ê¦~
  12.             Mmon = Format(Month(xlData), "00")
  13.             With Q(1)
  14.                 .Connection = "URL;http://www.twse.com.tw/ch/trading/indices/MI_5MINS_HIST/MI_5MINS_HIST.php?myear=" & Myear & "&mmon=" & Mmon ' °ÝÃD1
  15.                 .WebTables = "8"
  16.                 .Refresh BackgroundQuery:=False
  17.                 .ResultRange.Offset(2).Copy .Parent.Cells(Rows.Count, "E").End(xlUp).Offset(1)
  18.             End With
  19.             With Q(2)
  20.                 .Connection = "URL;http://www.twse.com.tw/ch/trading/exchange/FMTQIK/genpage/Report" & Year(xlData) & Mmon & "/" & Year(xlData) & Mmon & "_F3_1_2.php?STK_NO=&myear=" & Year(xlData) & "&mmon=" & Mmon
  21.                 Q(2).WebTables = "8"
  22.                 Q(2).Refresh BackgroundQuery:=False
  23.                 .ResultRange.Columns(2).Offset(2).Copy .Parent.Cells(Rows.Count, "J").End(xlUp).Offset(1)
  24.                 .ResultRange.Columns(6).Offset(2).Copy .Parent.Cells(Rows.Count, "K").End(xlUp).Offset(1)
  25.             End With
  26.         Next
  27.         .Range("E1").CurrentRegion.EntireColumn.AutoFit
  28.     End With
  29.     Application.ScreenUpdating = True
  30.     MsgBox "¶×¤J§¹¦¨  !!! "
  31. End Sub
½Æ»s¥N½X
¥[Åv¾ú¥v¦æ±¡.rar (15.27 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-5-18 08:48 ½s¿è

¦^´_ 4# seemee
°ÝÃD1
Myear = Year(xlData) - 1911 '¥Á°ê¦~
Mmon = Format(Month(xlData), "00")
With Q(1)
      .Connection = "URL;http://www.twse.com.tw/ch/trading/indices/MI_5MINS_HIST/MI_5MINS_HIST.php?myear=" & Myear & "&mmon=" & Mmon
Åã¥Üºô§}«á¤ñ¹ï¤@¤U:'"URL;http://www.twse.com.tw/ch/trading/indices/MI_5MINS_HIST/MI_5MINS_HIST.php?myear=101&mmon=04"

°ÝÃD2
With Q(1)
       .ResultRange.Offset(2).Copy .Parent.Cells(Rows.Count, "E").End(xlUp).Offset(1)
End With
.ResultRange.Offset(2).Copy -> ³o½d³òªº½Æ»s
.ResultRange->¥~³¡¸ê®Æ¨Ó·½¶Ç¦^¸ê®Æªº½d³ò
.Offset(2)     ->¸ê®Æªº½d³ò¤U²¾2¦C
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
.Parent.Cells(Rows.Count, "E").End(xlUp).Offset(1)  ->Q(1)ª«¥ó¤u§@ªí.EÄ橳³¡.©¹¤W¨ì¦³¸ê®Æªº¦C.¤U²¾1¦C
.Parent                                 ->  Q(1)ª«¥óªº¤÷¼h: ©Ò¦bªº¤u§@ªí   
Rows.Count                           ->¤u§@ªí¦CªºÁ`¼Æ
.Cells(Rows.Count, "E")           ->EÄæ³Ì«á¤@¦C
.End(xlUp)                             ->©¹¤W: xlUp ¨ì¦³¸ê®Æªº¦C
.Offset(1)                             ->¸ê®Æªº½d³ò¤U²¾1¦C
'**********************************************************************
.ResultRange.Offset(2).Copy .Parent.Cells(Rows.Count, "E").End(xlUp).Offset(1)
¥~³¡¸ê®Æ¨Ó·½¶Ç¦^¸ê®Æªº½d³ò¤U²¾2¦C.½Æ»s ¶K¤W¦ì¸m(ª«¥ó¤u§@ªí.EÄæ³Ì«á¤@¦C.©¹¤W¨ì¦³¸ê®Æªº¦C.¤U²¾1¦C)



·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥ýÁÂÁ¤j¤j¸Ô²Óªº¦^µª
§Úµoı§Úªº°ÝÃD°Ýªº¤£¦n

°ÝÃD
1. ¦bºô­¶¤W¥u¬Ý¨ìhttp://www.twse.com.tw/ch/trading/indices/MI_5MINS_HIST/MI_5MINS_HIST.php
    ´«¤é´Á¬d¸ß¨ÌµM¥u¬Ý¨ìhttp://www.twse.com.tw/ch/trading/indices/MI_5MINS_HIST/MI_5MINS_HIST.php
    ¨º«á­±³o¬q?myear=101&mmon=04" ¬O±q¦ó±oª¾?
2.
.QueryTables.Add(Connection:="URL;http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/genpage/Report" & ¦~ & ¤ë & "/" & ¦~ & ¤ë & "_F3_1_8_" & ªÑ²¼¥N¸¹ & ".php?STK_NO=" & ªÑ²¼¥N¸¹ & "&myear=" & ¦~ & "&mmon=" & ¤ë, Destination:=Range("A1"))

Destination:=Range("A1")³o¬O«ü©w³Ì¥ª¤WªºÀx¦s®æ

"URL;http://www.twse.com.tw/ch/trading/exchange/FMTQIK/genpage/Report" & Year(xlData) & Mmon & "/" & Year(xlData) & Mmon & "_F3_1_2.php?STK_NO=&myear=" & Year(xlData) & "&mmon=" & Mmon

¨S¦³Destination  ¨º«ç»òª¾¹D ResultRange ­n©ñ­þ¸Ì?

TOP

¦A¸É¥R¤@­Ó°ÝÃD
¤é´Á³¡¥÷ 1999/12/31
                  100/01/03
¨ì100¦~·|Åܦ¨¥Á°êÅã¥Ü
½Ð°Ý«ç»ò¶g¾ã


­ÓªÑ¾ú¥v¦æ±¡.rar (31.57 KB)

TOP

¦^´_ 6# seemee
Q1: ±q4#³o¬qµ{¦¡½X¸Ì±oª¾
  1. 10.           xlData = DateAdd("m", -i, DateSerial(.[b1] + 1911, .[b2], 1))
  2. 11.            Myear = Year(xlData) - 1911 '¥Á°ê¦~
  3. 12.            Mmon = Format(Month(xlData), "00")
½Æ»s¥N½X
Q2 :¨S¦³Destination  ¨º«ç»òª¾¹D ResultRange ­n©ñ­þ¸Ì?
±q4#³o¬qµ{¦¡½X¸Ì±oª¾ ¬O¤w¦s¦b¥~³¡¬d¸ß ,¤£¶·«ü©wDestination¡@
  1. 07.        Set Q(1) = .QueryTables(1)

  2. 08.        Set Q(2) = .QueryTables(2)
½Æ»s¥N½X
¦^´_ 7# seemee
³o¸Ì¦³µª®×
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ seemee ©ó 2013-5-19 23:55 ½s¿è

¦^´_ 8# GBKEE
§ÚÁÙ¬O¤£¤ÓÀ´°ÝÃD
1.  ?myear=101&mmon=04 §Ú«üªº¬O«ç»òª¾¹D­n¥[³o¨Ç ºô­¶¤Wªººô§}¨S¦³Åã¥Ü
2. ¥[Åv¾ú¥v¦æ±¡.rar (15.27 KB) ¨S¦³¥[Destination¡@ ¦ý¬O¥Lªº¥~³¡¸ê®Æ¼È¦s¬O¦bUÄæ¶}©l ¬O¹w³]±qUÄæ¶}©l¶Ü?
    ­ÓªÑ¾ú¥v¦æ±¡TEST1.rar (37.03 KB)    ¦pªG¸ê®Æ½d³ò¶W¹LUÄæ ·|³y¦¨¸ê®Æ­«Å| ¦Ó¿ù»~


ÁÂÁª©¥Dªº­@¤ß¦^µª

TOP

¦^´_ 9# seemee
Q1:  Google·j´M WEB¬d¸ß     
Q2:  ½Ð¸Ô¬Ý ­ÓªÑ¾ú¥v¦æ±¡TEST1 ³o¤@¦æµ{¦¡½Xªº¤º®e
     .ResultRange.Offset(2).Copy .Parent.Cells(Rows.Count, "S").End(xlUp).Offset(1)
    ³o¦ì¸m"S"¬OSÄæ, ½Ð¤ñ¹ï [¥[Åv¾ú¥v¦æ±¡] ¬Û¹ïªºµ{¦¡½X ½Æ»s¨ìªº¦ì¸m¦b­þ¸Ì?
  1. With Q(2)  '­ÓªÑ¾ú¥v¦æ±¡TEST1Tµ{¦¡½X
  2.                 .Connection = "URL;http://www.twse.com.tw/ch/trading/exchange/FMTQIK/genpage/Report" & Year(xlData) & Mmon & "/" & Year(xlData) & Mmon & "_F3_1_2.php?STK_NO=&myear=" & Year(xlData) & "&mmon=" & Mmon
  3.                 Q(2).WebTables = "8"
  4.                 Q(2).Refresh BackgroundQuery:=False
  5.                 .ResultRange.Offset(2).Copy .Parent.Cells(Rows.Count, "S").End(xlUp).Offset(1)
  6.             End With
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¯à¥I¥X·R¤ß´N¬OºÖ¡A¯à®ø°£·Ð´o´N¬O¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD