chang0833

¦^´_ 20# chang0833
GBKEEª©¤j¯uªºÁÙ¬O­n§ä§A¨D±Ï~"~
¦Û¤v§ä¤F¤F«Ü¤[¡AÁÙ¬O¤£ª¾¹DQueryTable Äݩʪº¥Îªk
»Ý¥Î­þ¤@­ÓÄݩʳ]©w¤~¯à¤£¤U¸üºô­¶Àx¦s®æ®æ½u???
³Â·Ðª©¤j¤Î°ª¤âÀ°¦£¸Ñµª¡A·P¿E¤£ºÉ!!

chang0833

¤U¸ü (46.28 KB)

2017-7-29 09:10

ª©¤jµ{¦¡½X¬O¥Î§Aªº
¹Ï¥ªÃä¬O¥[¤Jª©¤j«á¨Ó­×¥¿ªº
·Q§â®æ½u¤]¦P®É¨ú®ø
¤@ª½§ä¤£¨ì¤èªk¡A³Â·Ðª©¤jÀ°¦£¡AÁÂÁÂ

chang0833

1. Sub Ex1()
   
2. Cells.Clear
   
3.     Dim Ar(), a As Variant, i As Integer, stkno As String, Qur As String, DateVar As Integer, Sh As Worksheet
   
4.     With CreateObject("InternetExplorer.Application")
   
5.         .Navigate "http://www.tdcc.com.tw/smWeb/QryStock.jsp"
   
6.         Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
   
7.         Set a = .Document.ALL.tags("option") '¸ê®Æ¤é´Áªº¤º®e
   
8.         ReDim Ar(a.Length - 1)
   
9.         For i = 0 To a.Length - 1
   
10.             Ar(i) = a(i).innerHTML
    
11.         Next
    
12.         .Quit
    
13.     End With
    
14.     stkno = Sheets("¥Ø¼ÐªÑ²¼").Range("B2").Value    '
    
15.     If stkno = "" Then Exit Sub
    
16.     Set Sh = ActiveSheet             '«ü©w¤u§@ªí
    
17.     With Sh
    
18.         For DateVar = 0 To UBound(Ar)
    
19.             Qur = "http://www.tdcc.com.tw/smWeb/QryStock.jsp?SCA_DATE=" & Ar(DateVar) & "&SqlMethod=StockNo&StockNo=" & stkno & "&StockName=&sub=%ACd%B8%DF"
    
20.             .QueryTables.Add "URL;" & Qur, .Cells(1 + (DateVar * 32), "A")
    
21.             '.Cells(1 + (DateVar * 32), "A")  AÄ涡¹j 32¦C
    
22.             With .QueryTables(1)
    
23.                 .WebSelectionType = xlSpecifiedTables
    
24.                 .WebFormatting = xlWebFormattingNone
    
25.                 .WebTables = "6,7,8"
    
26.                 .WebPreFormattedTextToColumns = True
    
27.                 .WebConsecutiveDelimitersAsOne = True
    
28.                 .WebSingleBlockTextImport = False
    
29.                 .WebDisableDateRecognition = False
    
30.                 .WebDisableRedirections = False
    
31.                 .PreserveFormatting = False
    
32.                 .Refresh BackgroundQuery:=False
    
33.                 Sh.Names(.Name).Delete '§R±¼¤u§@ªí¤Wªº¦WºÙ
    
34.                 .Delete                '§R±¼³oQueryTable
    
35.             End With
    
36.         Next
    
37.     End With

½Æ»s¥N½X

ª©¤j³o¬O§Ú¥Îªºµ{¦¡½X¡C

GBKEE

¦^´_ 23# chang0833

§Ú±N¤u§@ªí®æ½u§ï¦¨¬õ¦â,¨S¦³¤U¸ü¨ìºô­¶®æ¦¡ÃC¦â
§A¨S¤W¶ÇÀÉ®×,§Ú²ö®_¦Ï£«


   

¤U¸ü (238.23 KB)

2017-7-29 12:50

chang0833

ª©¤jÁÙ¬OÁÂÁ§A¡A§Ú«á¨Ó¥Î¤â°Êªº§â®æ½u®ø°£¤F
·Q»¡¥i¤£¥i¥H¥Ñ¹q¸£¶]µ{¦¡¡A¶¶«K¾Ç²ß¤@¤U¡A·P®¦^^

oligo

¦^´_ 9# GBKEE
GBKEE ¤j¤jªº Web ª©«Ü¦n¥Î¡A§Ú±`±`§Q¥Î³o­Óµ{¦¡¬d¸ß¡A¦ý«e´X¤Ñ¸Ó¬ÛÃöºô­¶¦ü¥G¦³­×§ï¹L¡A²{¦b¦pªG¶]µ{¦¡·|¥X²{¦p¤Uªº¿ù»~¡G
°õ¦æ¶¥¬q¿ù»~ '9':
°}¦C¯Á¤Þ¶W¥X½d³ò

°»¿ùªº¸Ü·|°±¦b²Ä9¦æªº
ReDim Ar(a.Length - 1)

¤£ª¾¦p¦ó¦]À³³o­Ó·s­×ªººô­¶¥h­×§ïµ{¦¡¡H
ÁÂÁ¡I

iamaraymond

¦^´_ 26# oligo

«¢Åo¡A§Ú­è­è¤W¶Ç¤F¤@­Ó·sÀɮסA¥Î¨Ó§ì¨ú§ïª©«áªº¸ê®Æ¡A½Ð°Ñ¦Ò
    http://forum.twbts.com/thread-20646-1-1.html
§Æ±æ¦³À°¨ì±z~