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

[µo°Ý] ¶°«O¤áªÑÅv¤À´²ªí¬d¸ß §ì¨C¶g¸ê®Æ

[µo°Ý] ¶°«O¤áªÑÅv¤À´²ªí¬d¸ß §ì¨C¶g¸ê®Æ

¦U¦ì°ª¤â§A­Ì¦n ¤p§Ì¦bª¦§¹«Ü¦h­¶­±
¤]google ¬Ý¤F´X­Ó°ª¤âªº»yªk¡A·Q­n¹Á¸Õ¥Î VBA ¥h ¶°«O¤áªÑÅv¤À´²ªí¬d¸ß ¡A¿é¤J¤@­Ó¥N¸¹¡A§ì¨C¶gªºªÑ²¼ªÑÅv¤À´²ªí¸ê®Ætable¡A§ì¨ìExcel¥h¤ÀªR
ºô§}¡Ghttp://www.tdcc.com.tw/smWeb/QryStock.jsp

¦ý¥u·|¼g¨ì«ç»ò§âªÑ²¼¥N¸¹¿é¤J¡A¦ý¬O¤é´Áªº¿ï³æ¹ê¦b¤£ª¾­n«ç»ò¾Þ§@¡Aºô­¶ "¸ê®Æ¤é´Á" select §ä¤£¨ì  .value¡A´N°±¦b³oÃä·Q¦n´X¤Ñ¡AÁÙ¬O¤£ª¾«ç»ò¼g¤U¥h¤F
Áٽаª¤â«ü±Ð»yªk
·P®¦

==========================================
Sub ¶°«O§ì¨ú()

Dim A
With CreateObject("InternetExplorer.Application")
.Visible = True
.Navigate "http://www.tdcc.com.tw/smWeb/QryStock.jsp"
Do While .Busy Or .ReadyState <> 4: DoEvents: Loop

With .document
'-----¿é¤J­n¬d¸ßªºªÑ²¼¥N½X
   For Each A In .getelementsbytagname("INPUT") '§ä­ì©l½X¦³input°Ñ¼Æ
   If A.Name = "StockNo" Then A.Value = Range("B1").Value '§ä­ì©l½X¦³input°Ñ¼Æ
   Next
   
End With

End With
End Sub
==========================================

¦U¦ì°ª¤â
§Ú¸É¥R¤@¤U°ÝÃD¡A¸ô®|¬O¤U­±³o­Ó¹Ï¤ù­¶­±¡A¥u¦³2­ÓÄæ¦ì¿é¤J¸ê®Æ¡AµM«áÂIÀ»¬d¸ß«ö¶s¡A·|¶]¥X¤@­Ótable¡A§â¸ê®Æ½Æ»s¨ìExcel
1. ¸ê®Æ¤é´Á (¬õ®Øªº¦a¤è) ¥Ø«e¥d¦í¡A¤£ª¾¹D¸Ó¦p¦ó¥ÎVBA¾Þ§@
2. ÃÒ¨é¥N¸¹
¤£ª¾¬O§_¦³°ª¤â¥i¥H¨ó§U«ü±Ð¡AÁÂÁÂ

TOP

¦^´_ 2# espionage

¸Õ¸Õ¬Ý
  1. Sub Ex() '¶°«O¤áªÑÅv¤À´²ªí¬d¸ß
  2.     Dim element As Object, i As Integer, k As Integer, J As Integer, jj As Integer, s As Integer
  3.     With CreateObject("InternetExplorer.Application")
  4.         .Visible = True
  5.         .Navigate "http://www.tdcc.com.tw/smWeb/QryStock.jsp"
  6.         Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
  7.         With .Document
  8.             '.ALL("SqlMethod")(0).Checked = True    '¤Ä¿ï:ÃÒ¨é¥N¸¹
  9.            ' .All("StockNo").Value = "1101"
  10.             .ALL("SqlMethod")(1).Checked = True     '¤Ä¿ï:ÃÒ¨é¦WºÙ
  11.             .ALL("StockName").Value = "Áp¹q"
  12.             '.ALL("SCA_DATE").SELECTEDINDEX = 0     '²Ä1­Ó¤é´Á
  13.             .ALL("SCA_DATE").SELECTEDINDEX = 2      '²Ä3­Ó¤é´Á
  14.             .ALL("sub").Click                       '«ö¤U¬d¸ßÁä
  15.         End With
  16.         Do While .Busy Or .ReadyState <> 4          'µ¥­Ôºô­¶¤U¸ü§¹²¦
  17.             DoEvents
  18.             Application.SendKeys "~", True          '«ö ENTER «öÁä ,¹w¨¾ "ÃÒ¨é¥N¸¹"¦³¿ù»~
  19.          Loop
  20.         Set element = .Document.getelementsbytagname("table")  '¨ú±oºô­¶¸ê®Æ°Ï¶ô
  21.         If element.Length < 7 Then
  22.             MsgBox "ÃÒ¨é¥N¸¹  ??": Exit Sub
  23.         End If
  24.         With Sheets(1)
  25.             .Cells.Clear
  26.             k = k + 1
  27.             For s = 5 To 7                   '¤w§ä¥Xºô­¶ªºtable¤º®e¦b 5-7 ¤¤
  28.                 For i = 0 To element(s).Rows.Length - 1                 '¸ê®Æªº¦C¦ì
  29.                     For jj = 0 To element(s).Rows(i).Cells.Length - 1   '¸ê®ÆªºÄæ¦ì
  30.                         .Cells(k, jj + 1) = element(s).Rows(i).Cells(jj).INNERTEXT
  31.                     Next
  32.                     k = k + 1
  33.                 Next
  34.             Next
  35.         End With
  36.       '  .Quit  'Ãö³¬IE
  37.     End With
  38. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

ÁÂÁ GBKEE ¤j¤j À°¦£
.ALL("SCA_DATE").SELECTEDINDEX = 2      '²Ä3­Ó¤é´Á
1.  ­ì¨Ó³o­ÓSELECTEDINDEX »yªk¥i¥H§ïÅÜ ¤é´Á ¿ï¶µ¡A¦]¬°ºô­¶­ì©lÀɪº»yªk§Ú§ä¤£¨ì option .value¡A¯u¬O¨ü±Ð¤F¡A¦ý¦pªG§Ú·Q­n±q20150529¶]¨ì³Ìªñ20150904¡A¬O¤£¬O­n³]©w°j°é i=0 to 14¨Ó§ïÅÜSELECTEDINDEX ©O?
2.  ©Ó¤W¡A¦]¬°¬Ý¹Lºô­¶¿ï³æ¡Aª¾¹D²Ä¤@­Ó¿ï¶µ¬O20150904¡A²Ä¤G­Ó¿ï¶µ¬O20150828¡A¥H¦¹Ãþ±À...°£¤F¤H¤u¶ñ¨ìExcel ¤§¥~¡A°²¨Ï§Ú­Ì­n§â¿ï³æ¤ºªº¤å¦r¤]¤@¨Ö§ì¥X¨Ó¨ìExcel¡A¤ñ¤èSELECTEDINDEX = 2 ¬°20150821¡A¥i§_¥[¨ì»yªk¤º?
3. F or s = 5 To 7   '¤w§ä¥Xºô­¶ªºtable¤º®e¦b 5-7 ¤¤¡A·Q½Ð±ÐGBKEE «ç»ò¬Ý¥X¨Óªº? ¦]¬°§Ú¬Ý¤£À´³o¤@¥y¡A°õ¦æVBA¡AExcel·|¸õ¥XMsgBox "ÃÒ¨é¥N¸¹  ??"´Nµ²§ô¤F
4. °²¨Ï§Ú§â³o¤@¬q If element.Length < 7 Then MsgBox "ÃÒ¨é¥N¸¹  ??": Exit Sub ¡A¥ý²¾°£¡A«hVBA¶]¨ì ³o¤@¥yFor i = 0 To element(s).Rows.Length - 1  '¸ê®Æªº¦C¦ì¡A·|³ø¿ù¡A"¨S¦³³]©wª«¥óÅܼƩÎwith°Ï¶ôÅÜ¼Æ "


==================================================
                 ¸ê®Æ¤é´Á  
                 <select size="1" name="SCA_DATE">
                       <option >20150904</option><option >20150828</option><option >20150821</option><option >20150814</option><option >20150807</option><option >20150731</option><option >20150724</option><option >20150717</option><option >20150709</option><option >20150703</option><option >20150626</option><option >20150618</option><option >20150612</option><option >20150605</option><option >20150529</option><option >20150522</option><option >20150515</option><option >20150508</option><option >20150430</option><option >20150401</option><option >20150302</option><option >20150202</option><option >20150105</option><option >20141201</option><option >20141103</option><option >20141001</option><option >20140901</option>               </select>
==================================================

TOP

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

TOP

GBKEE ¤j¤j
·Q½Ð±Ð¤@¤U
1. §Ú¥ý°õ¦æ±zªºVBA»yªk¡A¦ý¶]¨ìFor i = 0 To element(s).Rows.Length - 1 '¸ê®Æªº¦C¦ì¡AÅã¥Ü³ø¿ù¡A°õ¦æ¶¥¬q¿ù»~ '91': ¨S¦³³]©wª«¥óÅܼƩΠWith°Ï¶ôÅܼơA½Ð°Ý§Ú¾Þ§@¤W¬O§_¦³¿òº|¤°»ò?
2. ¦pªG§Ú¥[¤J¤@¥y¡AMsgBox element.Length¡Aµøµ¡·|¼u¥X 5¡AµM«á§Ú¥ý§â°j°é¨ú®ø¡A¥u³æ¯Â¥[¤F³o¥y¡A¦p¤UVBA¡ACells(2, 6) = element(5).Rows(1).Cells(1).innertext¡A¦ýÁÙ¬O³ø¿ù¤@¼Ëªº°T®§¡A°õ¦æ¶¥¬q¿ù»~ '91': ¨S¦³³]©wª«¥óÅܼƩΠWith°Ï¶ôÅܼơA¥i¥H½Ð±Ð³o¤@¥yªº»yªk§Ú¬O¤£¬O¿ù¤F­þ­Ó¦a¤è?
·PÁÂ
=================================
Do While .Busy Or .ReadyState <> 4 'µ¥­Ôºô­¶¤U¸ü§¹²¦
DoEvents
Application.SendKeys "~", True '«ö ENTER «öÁä ,¹w¨¾ "ÃÒ¨é¥N¸¹"¦³¿ù»~
Loop
Set element = .Document.getelementsbytagname("table") '¨ú±oºô­¶¸ê®Æ°Ï¶ô
MsgBox element.Length

'If element.Length < 7 Then
'MsgBox "ÃÒ¨é¥N¸¹?": Exit Sub
'End If
With Sheets(1)
.Cells.Clear
.Cells(2, 6) = element(5).Rows(1).Cells(1).innertext
=================================

TOP

¦^´_ 6# espionage
­×§ï¤@¤U¬Ý¬Ý
  1. Do While .Busy Or .ReadyState <> 4          'µ¥­Ôºô­¶¤U¸ü§¹²¦
  2.             DoEvents
  3.             Application.SendKeys "~", True          '«ö ENTER «öÁä ,¹w¨¾ "ÃÒ¨é¥N¸¹"¦³¿ù»~
  4.         Loop
  5.         Do
  6.             Set element = .Document.getelementsbytagname("table")  '¨ú±oºô­¶¸ê®Æ°Ï¶ô
  7.         Loop Until Not element Is Nothing
  8.         MsgBox element.Length
  9.         Stop
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

Hi GBKEE ¤j¤j
ÁÙ¬O¤£¦æ­C¡A©³¤U¬OVBA½X
1. ¶]¨ì Stop·|°±¤U¨Ó
2. ¦pªG§âStop®³±¼¡AIf element.Length < 7 Then ³o¤@¬q¤]¥ý®³±¼¡Aµ{¦¡ÁÙ¬O¶]¨ì For i = 0 To element(s).Rows.Length - 1 '¸ê®Æªº¦C¦ì¡A³o¤@¥y³ø¿ù¡A°õ¦æ¶¥¬q¿ù»~ '91': ¨S¦³³]©wª«¥óÅܼƩΠWith°Ï¶ôÅܼÆ

============================================
Sub ¶°«O§ì¨ú()

Dim A, element As Object, i As Integer, k As Integer, J As Integer, jj As Integer, s As Integer

With CreateObject("InternetExplorer.Application")
.Visible = True
.Navigate "http://www.tdcc.com.tw/smWeb/QryStock.jsp"
Do While .Busy Or .readyState <> 4: DoEvents: Loop

With .document
'-----¿é¤J­n¬d¸ßªºªÑ²¼¥N½X
   For Each A In .getelementsbytagname("INPUT") '§ä­ì©l½X¦³input°Ñ¼Æ
   If A.Name = "StockNo" Then A.Value = Range("B1").Value '§ä­ì©l½X¦³input°Ñ¼Æ¡A«á­±ªºname =StockNo
   Next
   
   .ALL("SCA_DATE").SELECTEDINDEX = 2
   .ALL("sub").Click
End With
   
Do While .Busy Or .readyState <> 4: DoEvents

Application.SendKeys "~", True '«ö ENTER «öÁä ,¹w¨¾ "ÃÒ¨é¥N¸¹"¦³¿ù»~

Loop

Do
Set element = .document.getelementsbytagname("table")
Loop Until Not element Is Nothing
MsgBox element.Length
'Stop

'If element.Length < 7 Then
'MsgBox "ÃÒ¨é¥N¸¹  ??": Exit Sub
'End If

With Sheets(1)

k = k + 1
  For s = 5 To 7 '¤w§ä¥Xºô­¶ªºtable¤º®e¦b 5-7 ¤¤
    For i = 0 To element(s).Rows.Length - 1 '¸ê®Æªº¦C¦ì
      For jj = 0 To element(s).Rows(i).Cells.Length - 1   '¸ê®ÆªºÄæ¦ì
         .Cells(k, jj + 1) = element(s).Rows(i).Cells(jj).INNERTEXT
      Next
      k = k + 1
    Next
  Next
End With

End With

End Sub

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2015-9-15 05:56 ½s¿è

¦^´_ 8# espionage
§Ú¥u¦³Ie8¨S³o°ÝÃD, Ie8 ¤¤element ªº Length =9
½Ð¦³¤ñIe8·sª©ªº·|­û,¬Ý¬Ý°ÝÃD¦b­þ¸Ì.
  1. Application.VBE.Windows("°Ï°ìÅܼÆ").Visible = True '½Ð¦A¥[¤W
  2.     Stop  'µ{¦¡°±¤U¨Ó
  3.     '¦p7#ªº¹Ï¥i¥H¬Ý¬Ý§Aªº "°Ï°ìÅܼÆ"µøµ¡ ¤¤  element ªº Length ¬O¦h¤Ö
½Æ»s¥N½X
©Î¬O
  1. Application.Wait #12:00:05 AM#    '¦bµ{¦¡¤¤'µ¥­Ô5¬í
  2.         Set element = .Document.getelementsbytagname("table")  '¨ú±oºô­¶¸ê®Æ°Ï¶ô
  3.         Stop  'µ{¦¡°±¤U¨Ó,¬Ý "°Ï°ìÅܼÆ"µøµ¡ ¤¤  element ªº Length ¬O¦h¤Ö
  4.         With Sheets(1)
  5.             .Cells.Clear
  6.         
½Æ»s¥N½X
©Î¬O¥ÎWEB¬d¸ß
  1. Sub Ex() '¶°«O¤áªÑÅv¤À´²ªí_WEB¬d¸ß
  2.     Dim Ar(), a, i As Integer, strDate As String, stkno As String, Qur As String
  3.     With CreateObject("InternetExplorer.Application")
  4.         .Navigate "http://www.tdcc.com.tw/smWeb/QryStock.jsp"
  5.         Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
  6.         Set a = .Document.ALL.tags("option") '¸ê®Æ¤é´Áªº¤º®e
  7.         ReDim Ar(a.Length - 1)
  8.         For i = 0 To a.Length - 1
  9.             Ar(i) = a(i).innerHTML
  10.         Next
  11.         .Quit
  12.     End With
  13.     strDate = Ar(0) '¾É¤J·í¤ë¤é´Á
  14.     Do
  15.         strDate = InputBox(Join(Ar, vbTab), "¶°«O¤áªÑÅv¤À´²ªí¬d¸ß ¤§ ¦³®Ä¤é´Á", strDate)
  16.         If strDate = "" Then Exit Sub
  17.      
  18.     Loop Until IsNumeric(Application.Match(strDate, Ar, 0))
  19.     stkno = InputBox("¿é¤JªÑ²¼¥N¸¹", "ªÑ²¼¥N¸¹", 2317)    '
  20.     If stkno = "" Then Exit Sub
  21.     Qur = "http://www.tdcc.com.tw/smWeb/QryStock.jsp?SCA_DATE=" & strDate & "&SqlMethod=StockNo&StockNo=" & stkno & "&StockName=&sub=%ACd%B8%DF"
  22.     With ActiveSheet
  23.         If .QueryTables.Count = 0 Then
  24.             .QueryTables.Add "URL;" & Qur, .[A1]
  25.         Else
  26.             .QueryTables(1).Connection = "URL;" & Qur
  27.         End If
  28.         With .QueryTables(1)
  29.             .WebSelectionType = xlSpecifiedTables
  30.             .WebFormatting = xlWebFormattingNone
  31.             .WebTables = "6,7,8"
  32.             .WebPreFormattedTextToColumns = True
  33.             .WebConsecutiveDelimitersAsOne = True
  34.             .WebSingleBlockTextImport = False
  35.             .WebDisableDateRecognition = False
  36.             .WebDisableRedirections = False
  37.             .Refresh BackgroundQuery:=False
  38.         End With
  39.     End With
  40. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

GBKEE ¤j¤j
1. ±z©Ò´£ªºWEB¬d¸ß¡A¥i¥H°õ¦æ
2. ¦Ü©ó­ì¥»»yªkªº­×§ïÁÙ¬O¤£¦æ­C¡A§Ú¦³°Ñ¦Ò¥t¥~¤@«Ê»yªk¡A¤]¬O±z¦^ÂЪº¡Ahttp://forum.twbts.com/viewthread.php?tid=9511¡A¨âªÌ¥Øªº«Ü¹³¡A¤@¼Ë¬O¿é¤J¥N¸¹»P¤é´Á¡A¶]¸ê®Æ¡Aµo²{³o­ÓVBA¬O¦³®Ä¡A¥i¥H¶]¥Xµ²ªG¡A©Ò¥H®¼¯Ç´eªº
3.  §Ú¦³µo²{¤@­Ó°ÝÃD¡A­ì¥»§Ú¸ß°Ýªººô­¶¡Ahttp://www.tdcc.com.tw/smWeb/QryStock.jsp¡A·í¿é¤J§¹ªÑ²¼¥N¸¹¡A¶]¥Xµ²ªG¤§«á¡Aºô­¶¤WªºªÑ²¼¥N¸¹´N·|®ø¥¢¤£¨£¤F¡A¦Óºô­¶­ì©lÀɪº¦æ¼Æ¥Ñ167¦æ¼W¥[¬°393¦æ¡A¦p¤U¹Ï¬õ®Ø¡A
4.  ¦Ó¥t¥~¤@½gºô­¶ http://mops.twse.com.tw/mops/web/t164sb04¡A·í¿é¤J§¹ªÑ²¼¥N¸¹¡A¶]¥Xµ²ªG¤§«á¡Aºô­¶¤WªºªÑ²¼¥N¸¹¤£·|®ø¥¢¡Aºô­¶­ì©lÀɪº¦æ¼Æ¥Ñ704¦æºû«ù¬°704¦æ¡A¦p¤U¹Ï¬õ®Ø¡A
5. ¤£ª¾¹D³o¦³¨S¦³Ãö«Y¡A¬G½Ð±Ð¤@¤U¡A§Ú¦b§äªB¤Í¦³IE8ªº¸Õ¸Õ¬Ý­ì¥»ªºcode
·PÁÂ

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD