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

[µo°Ý] ¤@­Ó¥ÎVBA±qºô­¶¨ú±o·Q­n¸ê®Æªº¼gªk~

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

TOP

¥»©«³Ì«á¥Ñ bioleon69 ©ó 2017-5-1 21:40 ½s¿è

¦^´_ 31# GBKEE


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

THX G¤j!

TOP

¥»©«³Ì«á¥Ñ bioleon69 ©ó 2017-5-3 06:35 ½s¿è

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


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



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

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

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

¨D«ü¾É!·PÁÂ

TOP

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ bioleon69 ©ó 2017-5-8 06:05 ½s¿è

¦^´_ 35# GBKEE


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

·PÁÂGBK¤j!

TOP

¦^´_ 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.     .TextFileParseType = xlDelimited
  8.   .TextFileCommaDelimiter = True
  9.     .Refresh
  10. End With

  11. End Sub
½Æ»s¥N½X
VBA ±q0¶}©l
¥ý±q¾Ç·|¬ÝªºÀ´¶}©l
¥ý±q·|¦³°ò¥»­×§ï¯à¤O¶}©l
¤@¨B¤@¨B¾Ç²ß¤¤

TOP

¥»©«³Ì«á¥Ñ bioleon69 ©ó 2017-5-13 15:16 ½s¿è

¦^´_ 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
VBA ±q0¶}©l
¥ý±q¾Ç·|¬ÝªºÀ´¶}©l
¥ý±q·|¦³°ò¥»­×§ï¯à¤O¶}©l
¤@¨B¤@¨B¾Ç²ß¤¤

TOP

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

TOP

¦^´_ 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
VBA ±q0¶}©l
¥ý±q¾Ç·|¬ÝªºÀ´¶}©l
¥ý±q·|¦³°ò¥»­×§ï¯à¤O¶}©l
¤@¨B¤@¨B¾Ç²ß¤¤

TOP

        ÀR«ä¦Û¦b : §g¤l¥ß«í§Ó¡A¤p¤H«í¥ß§Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD