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

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

¦^´_ 20# norafang
§A­n¥ÎIE¶×¤J¥~³¡¸ê®Æ¬OµLªk¥Î¿ý»s,
¥\¯àªí«ü¥O:¸ê®Æ-> ¶×¤J¥~³¡¸ê®Æ,¥i¥H¥Î¿ý»s¤U¨Ó­×§ï
19#ªº°ÝÃD ­n¦h¬Ý¬ÝVBA»¡©úªº ¨ç¼Æ,¤èªk,³¯­z¦¡,ÄÝ©Ê.¨Ó¤F¸Ñ¥Îªk,¥i¦Ê¤Ø¬ñÀY,§óªñ¤@¨B.
  1. Option Explicit
  2. 'Const ³¯­z¦¡ «Å§i±`¼Æ , ¨ä­È¦p¦r­±©Ò¥Ü
  3. Sub a()
  4.     Dim x, ur As String
  5.     x = "2022"
  6.     Const url As String = "http://jsjustweb.jihsun.com.tw/z/zc/zcl/zcl_AAAA.asp.htm"
  7.     MsgBox url
  8.     ur = "http://jsjustweb.jihsun.com.tw/z/zc/zcl/zcl_" & x & ".asp.htm"
  9.     MsgBox ur
  10. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 21# GBKEE
G¤j,ÁÂÁ±zªº­@¤ß«ü±Ð,¨Ì±zªº¤è¦¡,§Úªº¼gªk¦p¤U:
¦³¥H¤U´X­Ó°ÝÃD
1.¬°¦ó§Ú¤w«ü©w¥N¸¹¦bA1,¦ý±a¥XªíÀY,¦Ó¨S¦³¸ê®Æ?

2.¬°¦ó§Ú«ü©w©ñ¸m¦ì¸m©óRange("AA1").Activate,¶]§¹ÁÙ¬O©ñ¦bA2?
                                                       
Sub Test()
    Dim x, ur As String
    x = Worksheets("sheet1").Range("a1")
    Const url As String = "http://jsjustweb.jihsun.com.tw/z/zc/zcl/zcl_AAAA.asp.htm"
    MsgBox url
    ur = "http://jsjustweb.jihsun.com.tw/z/zc/zcl/zcl_" & x & ".asp.htm"
    MsgBox ur
    Cells.Clear
    Set ie = CreateObject("internetexplorer.application") '¨Ï¥Î¦¹¤è¦¡¥i¥H§K°£ "³]©w¤Þ¥Î¶µ¥Ø"
    With ie
        .Visible = False 'True¬°¶}±Òie, False¬°¤£¶}±Òie
        .Navigate url
        Do While .ReadyState <> 4 'µ¥«Ýºô­¶¶}±Ò
            DoEvents
        Loop
        .ExecWB 17, 2 'Select All
        .ExecWB 12, 2 'Copy selection
        Sheets("Sheet1").Cells.Select
        Range("AA1").Activate
        ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
                False, NoHTMLFormatting:=True
    End With
    Columns("A:B").Delete
    ie.Quit
    MsgBox "¸ê®Æ½Æ»sµ²§ô"
End Sub

    ·Ð½ÐG¤j¦AÀ°§Ú¬Ý¬Ý,ÁÂÁÂ

TOP

¦^´_ 22# norafang
VBAªº¸gÅç­È¤Ó¤Ö¤F,»Ý¦h­×·Ò 21# ªºµ{¦¡½X¬O¦b»¡©ú Const ³¯­z¦¡
  1. Option Explicit
  2. Sub Test()
  3.     Dim x, ur As String, IE As Object
  4.     x = Worksheets("sheet1").Range("a1")
  5.   '  Const url As String = "http://jsjustweb.jihsun.com.tw/z/zc/zcl/zcl_AAAA.asp.htm"
  6.    ' MsgBox url
  7.     ur = "http://jsjustweb.jihsun.com.tw/z/zc/zcl/zcl_" & x & ".asp.htm"
  8.    ' MsgBox ur
  9.     Set IE = CreateObject("internetexplorer.application") '¨Ï¥Î¦¹¤è¦¡¥i¥H§K°£ "³]©w¤Þ¥Î¶µ¥Ø"
  10.     With IE
  11.         .Visible = False 'True¬°¶}±Òie, False¬°¤£¶}±Òie
  12.         .Navigate ur
  13.         Do While .ReadyState <> 4 'µ¥«Ýºô­¶¶}±Ò
  14.             DoEvents
  15.         Loop
  16.         .ExecWB 17, 2 'Select All
  17.         .ExecWB 12, 2 'Copy selection
  18.         With Sheets("Sheet1")
  19.              .Cells.Clear
  20.             '.Cells.Select
  21.             .Range("AA1").Activate
  22.             .PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
  23.                 False, NoHTMLFormatting:=True
  24.         End With
  25.     End With
  26.     Columns("A:B").Delete
  27.     IE.Quit
  28.     MsgBox "¸ê®Æ½Æ»sµ²§ô"
  29. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 23# GBKEE
G¤j,·PÁ±z,¤w¸g¥i¥H¶]¤F,ÁöµM¤º®e¦³«Ü¦h§ÚÁÙ¬O¤£¤Ó¤F¸Ñ,¦ý±z¤w¸gµ¹¤F«Ü¤jªº¤è¦V¤F,·PÁ­ò!

TOP

½Ð°Ý¦pªGºô­¶¨C10¬í¸ê®ÆÅÜ°Ê
¦p
http://mis.twse.com.tw/stock/fibest.jsp?stock=3673
¹j 10 ¬í¦Û°Ê§ó·s
[¤W¥«] 3673 F-TPK(¤¸¡A¥æ©ö³æ¦ì)
³Ìªñ
¦¨¥æ»ù        º¦¶^»ù®t
(¦Ê¤À¤ñ)        ·í½L
¦¨¥æ¶q        ²Ö¿n
¦¨¥æ¶q        ´¦¥Ü
¶R»ù        ´¦¥Ü
¶R¶q        ´¦¥Ü
½æ»ù        ´¦¥Ü
½æ¶q        ¶}½L        ³Ì°ª        ³Ì§C        »¡©ú
200.00        ¡¶1.50(0.76%)        359        2118        199.50        11        200.00        58        199.50        200.50        198.00       
¯à§_½ÐÀ°¦£§iª¾¦p¦ó§ì ÁÂÁÂ

TOP

¦^´_ 25# alantsai777
¹j 10 ¬í¦Û°Ê§ó·s ?? ³oºô­¶ ¶¡¹j 5 ¬í¦Û°Ê§ó·s!!
µ{¦¡½X½Æ»s¨ì¤@¯ë¼Ò²Õ
  1. Option Explicit
  2. Dim IE As Object
  3. Sub Ex_°ò¥»¥«ªp³ø¾Éºô¯¸()
  4.     Dim A As Object, xDate As Date, EDATE As Date
  5.     Set IE = CreateObject("InternetExplorer.Application")
  6.     With IE
  7.        ' .Visible = True
  8.         .Navigate "http://mis.twse.com.tw/stock/fibest.jsp?stock=3673"
  9.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  10.     End With
  11.     Ex_°Æµ{¦¡
  12. End Sub
  13. Private Sub Ex_°Æµ{¦¡()
  14.     Dim A As Object, K As Integer, i As Integer, ii As Integer
  15.     With IE
  16.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  17.         Set A = .Document.getElementsByTagName("table")(1)
  18.     End With
  19.     With ActiveSheet    '¥i«ü©w¤u§@ªí
  20.         .UsedRange.Clear
  21.         K = 1
  22.         For i = 0 To A.Rows.Length - 1
  23.             For ii = 0 To A.Rows(i).Cells.Length - 1
  24.                 .Cells(K, ii + 1) = A.Rows(i).Cells(ii).INNERTEXT
  25.             Next
  26.             K = K + 1
  27.         Next
  28.     End With
  29.     If Time <= #1:30:00 PM# Then   '¦¬½L®É¶¡ ¦Û¦æ½Õ¾ã
  30.         Application.OnTime Time + #12:00:05 AM#, "Ex_°Æµ{¦¡"  '¶¡¹j5¬í
  31.     Else
  32.         IE.Quit
  33.     End If
  34. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

½Ð±ÐGBKEE¶W¯Åª©¥D:
¥H¤Uºô­¶ªºVBA­n¦p¦ó¼g
https://tw.futures.finance.yahoo.com/future/options.html?opmr=optionfull&opcm=WTXO&opym=201509
cotton

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2015-9-14 06:14 ½s¿è

¦^´_ 28# aaron1059
¸Õ¸Õ¬Ý
À°§A§ä¥X¶Ç¦^´ÁÅv¸ê®Æªººô§}¤£¤@¼Ë.
  1. Option Explicit
  2. Sub Ex() 'Yahoo!©_¼¯ªÑ¥«--´ÁÅv
  3.     Dim ie As Object, k As Integer, S As Integer, jj As Integer, i As Integer, AA As Object
  4.     Set ie = CreateObject("InternetExplorer.Application")
  5.     ie.Navigate "https://tw.screener.finance.yahoo.net/future/aa03?opmr=optionfull&opcm=WTFO&opym=201510&random=0.01296169775357775"
  6.     ie.Visible = True
  7.     Do While ie.Busy Or ie.ReadyState <> 4: DoEvents: Loop
  8.     Set AA = ie.Document.getelementsbytagname("table")
  9.     With Sheets(1)
  10.         .Cells.Clear
  11.         k = k + 1
  12.         For S = 0 To AA.Length - 1                 '¤w§ä¥Xºô­¶ªºtable¤º®e¦b 5-7 ¤¤
  13.             For i = 0 To AA(S).Rows.Length - 1                 '¸ê®Æªº¦C¦ì
  14.                 For jj = 0 To AA(S).Rows(i).Cells.Length - 1   '¸ê®ÆªºÄæ¦ì
  15.                     .Cells(k, jj + 1) = AA(S).Rows(i).Cells(jj).INNERTEXT
  16.                 Next
  17.                 k = k + 1
  18.              Next
  19.           Next
  20.        End With
  21.      ie.Quit
  22. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ bioleon69 ©ó 2017-4-30 22:24 ½s¿è

GBK¤j ½Ð±Ð¤@¤U
±q¤W­±ª¦¤å½m²ß,¥Ø«e¥i¥H±qEXCEL¥s¥XÂsÄý¾¹
¤§«á­n«ç»òŪ¨úºô­¶¤º®e,¤º®e¼g¤JEXCEL
A3¶}©l¼g¤J
³oÃä¯uªº¥d¦í¤F..
¥H¤U¬O¥Ø«eµ{¦¡½Xªº¶i«×
  1. Option Explicit
  2. Dim ie As Object
  3. Sub ¶°«O()
  4.     Dim keyin As String
  5.     keyin = Range("a1")
  6.     Set ie = CreateObject("InternetExplorer.Application")
  7.       With ie
  8.          .Navigate "http://norway.twsthr.info/StockHolders.aspx?stock=" & keyin & ""
  9.          .Visible = True
  10.          Do While ie.Busy Or ie.readyState <> 4: DoEvents: Loop
  11.      End With
  12. Ex_°Æµ{¦¡
  13. End Sub

  14. Private Sub Ex_°Æµ{¦¡()
  15.     Dim A As Object
  16.     With ie
  17.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  18.         Set A = .Document.getElementsByTagName("table")
  19.     End With
  20.     With ActiveSheet    '¥i«ü©w¤u§@ªí
  21.         .UsedRange.Clear






  22.     End With
  23.     ie.Quit
  24. End Sub
½Æ»s¥N½X
Àµ½ÐGBK¤jÀ°¦£«üÂI12...

TOP

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

¥Ø«e¦¨¥\¥i°õ¦æ¤F,¤]¦hÁ«ª©¤W³\¦h¸ê·½
¦³´X­Ó°ÝÃD·Q½Ð±Ð





½Ð°Ý¤@¤U
1.¬°¤°»ò§Ú°õ¦æ¥X¨Ó«á·|¦³ªÅ¥Õªº¦C?
¬O­þÃä¥X¤F¤°»òª¬ªp?



2.¥i¥H§â¼g¤Jexcelªº¸ê®Æ,©w¸q¦¨¤@­ÓªF¦è¶Ü
¥i¥H¥Îwith  end with¨Ó±±¨î¤º®e(¥u±±¨î¼g¤Jªº³¡¤À)
¤ñ¤è»¡¦rÅé¤j¤p,¼e«×°ª«×,¤W¦â,§R°£/²M°£,µ¥µ¥
¤£·|²o°Ê¨ì©P³òªº¸ê®Æ

3.¥Ø«eµ{¦¡½XÁÙ¥iÀu¤Æ¶Ü?
¦]¬°·Q­n¦A§Ë¤@­Ó°j°é°õ¦æ«áÅý¥¦¶]1500¦¸
³oµ{¦¡½X·|¤£·|«Ü¦Y¨t²Î¸ê·½?(©È¹q¸£lag)

·PÁÂ
¥H¤Uµ{¦¡½X
  1. Option Explicit
  2. Dim ie As Object
  3. Sub ´ú¸Õ()
  4.   Set ie = CreateObject("InternetExplorer.Application")
  5.     With ie
  6.         .Navigate "http://norway.twsthr.info/StockHolders.aspx?stock=2330"
  7.         .Visible = True
  8.         Do While ie.Busy Or ie.readyState <> 4: DoEvents: Loop
  9.     End With
  10.     UsedRange.Clear
  11.     Ex_°Æµ{¦¡
  12. End Sub

  13. Private Sub Ex_°Æµ{¦¡()
  14. Dim A, i, ii
  15.     With ie
  16.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  17.         Set A = .Document.getElementsByTagName("table")(9)
  18.     End With
  19.     With ActiveSheet    '¥i«ü©w¤u§@ªí
  20.    
  21.         For i = 0 To A.Rows.Length - 296
  22.             For ii = 3 To A.Rows(i).Cells.Length - 1
  23.            .Cells(i + 1, ii - 2) = A.Rows(i).Cells(ii).innertext
  24.             Next
  25.             Next
  26.     With Cells
  27.             .EntireRow.AutoFit
  28.             .EntireColumn.AutoFit
  29.     End With
  30.     End With
  31.     ie.Quit
  32. End Sub
½Æ»s¥N½X
¥t¥~ªþ¤WÀÉ®×
test.rar (16.05 KB)

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD