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

§ì4­Óºô­¶¦X¨Ö¤@¥÷¸ê®Æ,2­Ó¿é¤JÄæ¦ì¼gªk½Ð±Ð(¦~,¤ë)

§ì4­Óºô­¶¦X¨Ö¤@¥÷¸ê®Æ,2­Ó¿é¤JÄæ¦ì¼gªk½Ð±Ð(¦~,¤ë)

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

±qÆ[´ú¯¸¶i¥h¦³8­Ó¿ï¶µ
http://mops.twse.com.tw/mops/web/t21sc04_ifrs
¦p¹Ï

°ê¤º¤W¥«http://mops.twse.com.tw/nas/t21/sii/t21sc03_106_3_0.html
°ê¥~¤W¥«http://mops.twse.com.tw/nas/t21/sii/t21sc03_106_3_1.html
°ê¤º¤WÂdhttp://mops.twse.com.tw/nas/t21/otc/t21sc03_106_3_0.html
°ê¥~¤WÂdhttp://mops.twse.com.tw/nas/t21/otc/t21sc03_106_3_1.html
§Ú·Q±N«e¥|­Ó¿ï¶µ
¸Ì­±ªººô­¶ªí®æ¤º®e,¥þ³¡¼g¤J¦P¤@¥÷excel
excel¸Ì­±´N©ñ¨â­Ó¿é¤JªºÄæ¦ì
¦~¸ò¤ë
½Ð°Ý¦³¤°»ò¤èªk¥i¥H³o¼Ë¼g?

¥Ø«e¬O¥u·|¼g¤@­Óºô§}¶i¥h,¥Îqt
¤]ÁÙ¨S³]©w¦~¤ë¤À§O¦ba1,b1¿é¤J
  1. Sub ¤ëÀ禬()
  2. Call ²M°£¥~³¡¸ê®Æ
  3. UsedRange.Clear
  4. Set keyin = ActiveSheet
  5. Set Show = keyin.QueryTables _
  6.         .Add(Connection:="URL;http://mops.twse.com.tw/nas/t21/sii/t21sc03_106_3_0.html", _
  7. Destination:=keyin.Cells(1, 1))
  8. With Show
  9.     .Name = "¼È¦s¸ê®Æ"
  10.     .RefreshStyle = xlInsertEntireRows
  11.     .AdjustColumnWidth = False
  12.     .RefreshPeriod = 0  'Ãö³¬©w®É§ó·s
  13.     .WebSelectionType = xlAllTables
  14.     .WebFormatting = xlWebFormattingNone ' µL®æ¦¡
  15.     .Refresh BackgroundQuery:=False
  16. End With
  17. End Sub

  18. Sub ²M°£¥~³¡¸ê®Æ()
  19. Dim sh As Worksheet, qyt As QueryTable
  20. For Each sh In ActiveWorkbook.Worksheets
  21. For Each qyt In sh.QueryTables
  22. qyt.Delete
  23. Next
  24. Next
  25. End Sub
½Æ»s¥N½X
qt
msxml2
InternetExplorer.Application
¥u¯àµy·L¬Ý±oÀ´¤@ÂIÂI¤@ÂIÂI³o¤TºØªº¤èªk= =

¬ß¤j¤j­Ì±Ð¾Ç«ü±Ð

¥ý¤£¥Î³Ò·Ð¤j¤j,¤£¦n·N«ä
¥Ø«eµ{¦¡½X¥H§ó°Ê
¨S¥´ºâ¥ÎQT¤F
¥Ø«e¤wXML¬°¥D
ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ bioleon69 ©ó 2017-5-12 07:51 ½s¿è

¦^´_ 2# bioleon69

½Ð±Ð¤j¤j!
¥Ø«e¥ÎQT¶×¤Jºô­¶¸ê®Æ
¦³4­ÓURL
4­ÓSUB¤£¦Pªº¦a¤è´N¬O,URL¸ò.NAMEªº³¡¤À
¤£ª¾¹D¦³¨S¦³¿ìªk¦X¨Ö¦¨¤@­ÓSUB?(§Ú²{¦b¬O¶]4­ÓSUB,¥u¦³URL¸òNAME¤£¤@¼Ë)
³o¼Ë°õ¦æ·Pı¬O¦P¼ËªºªF¦è¶]4¦¸,³t«×¸ò®Ä²v¤W³£¦ü¥G³£®t¤F,©Ò¥H¨Ó½Ð±Ð«e½ú­Ì
¦³¨S¦³¥i¥HÅܦ¨¤@­Óªº¤èªk

¥Ø«e¬O¥Î¤@­ÓsubµM«á
call test
call test1
call test2
call test3

µM«átest,test1,test2,test3 ´Nurl¸ò.name¤£¦P
  1. Sub TEST()
  2.     With ActiveSheet.QueryTables.Add(Connection:="URL;http://mops.twse.com.tw/nas/t21/sii/t21sc03_" & ccyear & "_" & ccmonth & "_0.html", Destination:=Range("B" & maxr + 5))
  3.         .Name = "TEST"
  4.         .WebFormatting = xlWebFormattingNone
  5.         .WebTables = "2"
  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

¦^´_ 3# bioleon69
  1. Option Explicit   '¦b¼Ò²Õ¼h¦¸¤¤±j­¢¨C­Ó¦b¼Ò²ÕùتºÅܼƳ£¥²¶·©ú½Tªº«Å§i¡C
  2. Sub Ex()
  3.     Dim Ar1(), Ar2(), i As Integer
  4.     Ar1 = Array("http://mops.twse.com.tw/nas/t21/sii/t21sc03_", "XXX1", "XXX2", "XXX3")
  5.     Ar2 = Array("TEST1", "TEST2", "TEST3", "TEST4")
  6.     For i = 0 To UBound(Ar1)
  7.         TEST Ar1(i), Ar2(i)
  8.     Next
  9. End Sub
  10. Sub TEST(ByVal xUrl As String, ByVal xName As String)
  11.     With ActiveSheet.QueryTables.Add(Connection:="URL;" & xUrl & ccyear & "_" & ccmonth & "_0.html", Destination:=Range("B" & maxr + 5))
  12.         .Name = xName
  13.         .WebFormatting = xlWebFormattingNone
  14.         .WebTables = "2"
  15.         .RefreshStyle = xlOverwriteCells
  16.         .WebPreFormattedTextToColumns = True
  17.         .WebConsecutiveDelimitersAsOne = True
  18.         .WebDisableDateRecognition = False
  19.         .Refresh BackgroundQuery:=False
  20.     End With
  21. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# GBKEE

¦¬¨ì!»°ºò¨Ó´ú¸Õ¤@¤U¬Ý¬Ý!
VBA ±q0¶}©l
¥ý±q¾Ç·|¬ÝªºÀ´¶}©l
¥ý±q·|¦³°ò¥»­×§ï¯à¤O¶}©l
¤@¨B¤@¨B¾Ç²ß¤¤

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD