GBKEE

¦^´_ 30# bioleon69

1. ''CreateObject("InternetExplorer.Application") »Ýµ¥­Ôºô­¶¤U¸ü§¹²¦³t«×¸ûºC
   
2. '°õ¦æ°j°éÅý¥¦¶]1500¦¸,¼K³t«×·|ºC³\¦h
   
3. Option Explicit
   
4. Sub ´ú¸Õ()
   
5.     Dim oXmlhttp As Object, oHtmldoc As Object, surl, E As Object
   
6.     Set oXmlhttp = CreateObject("msxml2.xmlhttp")
   
7.     Set oHtmldoc = CreateObject("htmlfile")
   
8.     surl = "http://norway.twsthr.info/StockHolders.aspx?stock=2330"
   
9.     With oXmlhttp
   
10.         .Open "Get", surl, False
    
11.         .Send
    
12.         oHtmldoc.write .responseText
    
13.     End With
    
14.     With oHtmldoc
    
15.         Set E = .all.tags("TABLE")(9)
    
16.     End With
    
17.     Application.ScreenUpdating = False
    
18.     Ex_°Æµ{¦¡ E
    
19.     Application.ScreenUpdating = True
    
20. End Sub
    
21. Private Sub Ex_°Æµ{¦¡(A As Object)
    
22.     Dim i As Integer, R As Integer, C As Integer
    
23.     With ActiveSheet    '¥i«ü©w¤u§@ªí
    
24.         .UsedRange.Clear
    
25.         For R = 0 To 2 * 5    'Ū¨ú5µ§¸ê®Æ ' Âù¼ÆªºA.Rows¬°ªÅ¥Õ¸ê®Æ
    
26.             If R <= 1 Or R > 2 And R Mod 2 = 1 Then '­ç°£ Âù¼ÆªºA.Rows
    
27.                 i = i + 1
    
28.                 For C = 2 To A.Rows(R).Cells.Length - 1
    
29.                     .Cells(i, C - 1) = A.Rows(R).Cells(C).innertext
    
30.                 Next
    
31.             End If
    
32.         Next
    
33.         With .UsedRange  ' CELL ¬°¾ã¤u§@ªíªºÀx¦s®æ ½d³ò¤j(¶O®É)
    
34.            ' .UsedRange ¤u§@ªí¦³¨Ï¥Î¨ìªºªºÀx¦s®æ ½d³ò¤p(¬Ù®É)
    
35.             .EntireRow.AutoFit
    
36.             .EntireColumn.AutoFit
    
37.         End With
    
38.     End With
    
39. End Sub

½Æ»s¥N½X

bioleon69

¦^´_ 31# GBKEE


¤F¸Ñ
¹ï³o¨â­Óª«¥óÁÙ«Ü­¯¥Í
CreateObject("msxml2.xmlhttp")
CreateObject("htmlfile")
¥Ø«e¤]´X¥G³£¬O§Û¼gG¤j¯d¤Uªºµ{¦¡½X­×§ï¾Ç²ß
»°ºò¨Ó¾Ç²ß¬Ý¬Ý

THX G¤j!

bioleon69

¨â­ÓºÃ°Ý
#¶Ã½X
#¥þ³¡ªí®æªº«ü©w¤èªk


http://mops.twse.com.tw/nas/t21/sii/t21sc03_106_3_0.html
¦pªG¬O³o­Óºô­¶
·|Åܳo¼Ë

¤U¸ü (62.68 KB)

2017-5-3 06:32

¦pªG¬O­n§ì³oºô­¶ªº¥þ³¡ªí®æ¤º®e
«D³æ¤@«ü©wªí®æ

À³¸Ó«ç»ò­×§ï?
With oHtmldoc
        Set E = .all.tags("TABLE")(9)
End With

ÃöÁäÀ³¸Ó¬O³o¤@¦æ?«á­±¥´(0)·|¥X²{¿ù»~

¨D«ü¾É!·PÁÂ

GBKEE

¦^´_ 33# bioleon69

¥i§ï¥Î·|¤J¥~³¡¸ê®Æ .QueryTables

1. Option Explicit
   
2. Sub Ex()
   
3.     With ActiveSheet.QueryTables.Add(Connection:="URL;http://mops.twse.com.tw/nas/t21/sii/t21sc03_106_3_0.html", Destination:=Range("A1"))
   
4.         .WebSelectionType = xlSpecifiedTables
   
5.         .WebFormatting = xlWebFormattingNone
   
6.         .WebTables = "4"
   
7.         .RefreshStyle = xlOverwriteCells
   
8.         .WebPreFormattedTextToColumns = True
   
9.         .WebConsecutiveDelimitersAsOne = True
   
10.         .WebSingleBlockTextImport = False
    
11.         .WebDisableDateRecognition = False
    
12.         .WebDisableRedirections = False
    
13.         .Refresh BackgroundQuery:=False
    
14.     End With
    
15. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 33# bioleon69

1. Option Explicit
   
2. Sub ´ú¸Õ()
   
3.     Dim oXmlhttp As Object, oHtmldoc As Object, surl, E As Object
   
4.     Set oXmlhttp = CreateObject("msxml2.xmlhttp")
   
5.     Set oHtmldoc = CreateObject("htmlfile")
   
6.     surl = "http://mops.twse.com.tw/nas/t21/sii/t21sc03_106_3_0.html"
   
7.     With oXmlhttp
   
8.         .Open "Get", surl, False
   
9.         .Send
   
10.         'oHtmldoc.write .responseText
    
11.         oHtmldoc.write BinToStr(.responseBody, "BIG5") 'ºô­¶½s½X "¤¤¤å"
    
12.     End With
    
13.     With oHtmldoc
    
14.         Set E = .all.tags("TABLE")(3)
    
15.     End With
    
16.     Application.ScreenUpdating = False
    
17.     Ex_°Æµ{¦¡ E
    
18.     Application.ScreenUpdating = True
    
19. End Sub
    
20. Private Sub Ex_°Æµ{¦¡(A As Object)
    
21.     Dim i As Integer, R As Integer, C As Integer
    
22.     With ActiveSheet    '¥i«ü©w¤u§@ªí
    
23.         .UsedRange.Clear
    
24.         For R = 0 To A.Rows.Length - 1
    
25.                 For C = 0 To A.Rows(R).Cells.Length - 1
    
26.                     .Cells(R + 1, C + 1) = A.Rows(R).Cells(C).innertext
    
27.                 Next
    
28.         Next
    
29.         With .UsedRange  ' CELL ¬°¾ã¤u§@ªíªºÀx¦s®æ ½d³ò¤j(¶O®É)
    
30.            ' .UsedRange ¤u§@ªí¦³¨Ï¥Î¨ìªºªºÀx¦s®æ ½d³ò¤p(¬Ù®É)
    
31.             .EntireRow.AutoFit
    
32.             .EntireColumn.AutoFit
    
33.         End With
    
34.     End With
    
35. End Sub
    
36. Function BinToStr(arrBin, strChrs) As String
    
37.     With CreateObject("ADODB.Stream")  '¤G¶i¦ì¤åÀÉ,¶Ç°e,Àx¦s
    
38.         .Type = 2
    
39.         .Open
    
40.         .Writetext arrBin
    
41.         .Position = 0
    
42.         .Charset = strChrs   '«ü©w½s½X
    
43.         BinToStr = .ReadText
    
44.         .Close
    
45.     End With
    
46. End Function

½Æ»s¥N½X

bioleon69

¦^´_ 35# GBKEE


º}«G,¥Ø«e¦b¾ÇµÛ¥ÎXML°Õ
¨º­ÓÂà½Xªºº}«G,ÁöµM¤£¬O«ÜÀ´¼gªk
¤p§Ì¥u¯à¥ý§Û¤U¨Ó¦º­I®M¥Î
½×¾Â²×©ó¦n¤FXDD

·PÁÂGBK¤j!

bioleon69

¦^´_ 35# GBKEE

¤È¦w G¤j

§Aªº³o­ÓÂà½X¨t²Î

¦pªG¬O¦bQTªº¸Ü,¸Ó«ç»ò©I¥s?
ÁÂÁ±z^^¨¯­W¤F

¨Ò¦p(¥H¤U)

1. Sub ¤U¸üCSV()
   
2. Set book1 = ActiveSheet
   
3. Set bookshow = book1.QueryTables _
   
4.     .Add(Connection:="TEXT;https://smart.tdcc.com.tw/opendata/getOD.ashx?id=2-8", _
   
5.         Destination:=book1.Range("a1"))
   
6. With bookshow
   
7. 
   
8.     .TextFileParseType = xlDelimited
   
9.   .TextFileCommaDelimiter = True
   
10.     .Refresh
    
11. End With
    
12. 
    
13. End Sub

½Æ»s¥N½X

bioleon69

¦^´_ 37# bioleon69


GGGG¤j..ÁÙ¦³¤@­Ó°ÝÃD..
(©çÁÂ,ÅýG¤j³Ìªñ«Ü¦£ «¢«¢!)

§Ú¤@­Ó¤@­Ó´ú¸Õ,À³¸Ó¬O²Ä12­Óªí®æ¨S¦³¿ù
¬°¤°»ò·|¤U¸ü¤£¤F©O??©_©Ç

1. Sub TEST()
   
2.     With ActiveSheet.QueryTables.Add(Connection:="URL;http://mops.twse.com.tw/mops/web/t56sb21_q3?encodeURIComponent=1&run=Y&step=1&TYPEK=sii&year=105&smonth=01&emonth=02&sstep=1&firstin=true", Destination:=Range("A1"))
   
3.         .Name = "¤W¥««ùªÑÂàÅý"
   
4.         .WebFormatting = xlWebFormattingNone
   
5.         .WebTables = "12"
   
6.         .RefreshStyle = xlOverwriteCells
   
7.         .WebPreFormattedTextToColumns = True
   
8.         .WebConsecutiveDelimitersAsOne = True
   
9.         .WebDisableDateRecognition = False
   
10.         .Refresh BackgroundQuery:=False
    
11.     End With
    
12. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 38# bioleon69

³o¸Ì¬Ý¬Ý    µLªk¥Î¥~³¡¸ê®Æ¶×¤Jªººô¯¸¯à¥ÎVBAŪ¨ú¸ê®Æ¶Ü¡H

bioleon69

¦^´_ 39# GBKEE


­n¥Îieªº¤èªk¶Ü??
·Ð½Ð¤j¤jÀ°¦£¬Ý¤@¤Uµ{¦¡½X­þÃ䦳°ÝÃD,§Ú¼gªº¨º¨â¦æ³£¤£¯à«ö¤U¥h

1. Sub test()
   
2. Dim ie, ab, cc
   
3.     Set ie = CreateObject("InternetExplorer.Application")
   
4.     With ie
   
5.          .Visible = True
   
6.         .navigate "http://mops.twse.com.tw/mops/web/t56sb21_q3"
   
7.         Do Until .ReadyState = 4
   
8.             DoEvents
   
9.         Loop
   
10.         Set cc = .document
    
11.         Set ab = .document.forms("form1")
    
12.         ab.typek.Value = "otc"
    
13.         ab.Year.Value = "105"
    
14.         ab.smonth.Value = "03"
    
15.         ab.emonth.Value = "04"
    
16.         'cc.getelementbyid("search_bar1").Click
    
17.        ' ab.submit
    
18.         End With
    
19. End Sub

½Æ»s¥N½X