[µo°Ý] ¦p¦ó§ä¨ì¥¿½Tªººô§},ÅýEXCEL¶×¤JWEB¬d¸ß

bioleon69

http://www.cmoney.tw/finance/f00026.aspx?s=2330
·Q­n§â¦¹ºô§}¶×¤J,¥ÎEXCELªºweb¬d¸ß
(°ò¥»¸ê®Æ¨º¥÷ªí®æ)
µ²ªG,¥¢±Ñ

¥Îfiddler§ä¨ìgetºô§}
http://www.cmoney.tw/finance/ashx/mainpage.ashx?action=GetStockListLatestSaleData&stockId=2330&cmkey=XYlSc5pnmuVR2Cz27paqpg%3D%3D&_=1474712478440
¤@¼Ë,¥¢±Ñ

¦p¦ó§ä¨ì¥¿½Tºô§},¦A§â2330§ï¦¨ÅܼÆ
¥Îiqy¤è¦¡¶×¤Jexcel

¥H¤U¬°¶×¤J¦¨¥\®×¨ÒªºiqyÀÉ®× (YAHOOªÑ¥«)
ok.rar (271 Bytes)

¤U¸ü¦¸¼Æ: 18

2016-9-24 18:34

GBKEE

¦^´_ 10# bioleon69

1. Option Explicit
   
2. Sub Ex_ºô­¶¤¸¯À()
   
3.     Dim i As Integer, xlTab As Object, R As Integer, C As Integer, Surl$
   
4.     If Sheets("¤u§@ªí1").[A1] = "" Then MsgBox "½Ð¿é¤J¥¿½TªÑ²¼¥N¸¹": Exit Sub
   
5.     Surl = "http://www.cmoney.tw/finance/f00026.aspx?s=" & Sheets("¤u§@ªí1").[A1]
   
6.     With CreateObject("InternetExplorer.Application")
   
7.         .Visible = False
   
8.         .Navigate Surl
   
9.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
   
10.             Set xlTab = .Document.ALL.tags("TABLE")(0)
    
11.         If Not xlTab Is Nothing Then
    
12.             With ActiveSheet
    
13.                 .UsedRange.Clear
    
14.                 For R = 0 To xlTab.Rows.Length - 1
    
15.                     For C = 0 To xlTab.Rows(R).Cells.Length - 1
    
16.                         .Cells(R + 1, C + 1) = xlTab.Rows(R).Cells(C).innertext
    
17.                     Next
    
18.                 Next
    
19.             End With
    
20.         Else
    
21.             MsgBox "½Ð¿é¤J¥¿½TªÑ²¼¥N¸¹"
    
22.         End If
    
23.         .Quit        'Ãö³¬ºô­¶
    
24.     End With
    
25. End Sub

½Æ»s¥N½X

bioleon69

·PÁÂGBK¥H¤Îlai¨â¦ì¤j¤j
³£¦¨¥\¤F~~¦b½Ð±Ð¤@¤U
§Ú·Q¥[¤J¦bA1¦pªG¨S¦³¿é¤J¥¿½TÅܼÆ
©ÎªÌ¿é¤J¿ù»~
B1·|§e²{ ½Ð¿é¤J¥¿½TªÑ²¼¥N¸¹
¤U­±¬O¦³¨º¸Ì¦³°ÝÃD??
§Ú¥[¤J¤F³o´X¦êµ{¦¡½X

On Error Resume Next
        .Refresh False
        [B1] = ""
        If Err <> 0 Then
            [B1] = "½Ð¿é¤J¥¿½TªÑ²¼¥N¸¹"
        Else
        End If

1. Option Explicit
   
2. Sub Ex_ºô­¶¤¸¯À()
   
3.     Dim i As Integer, xlTab As Object, R As Integer, C As Integer, Surl$
   
4.         Surl = "http://www.cmoney.tw/finance/f00026.aspx?s=" & Sheets("¤u§@ªí1").[A1]
   
5.     With CreateObject("InternetExplorer.Application")
   
6.         .Visible = False
   
7.         .Navigate Surl
   
8.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
   
9.         Set xlTab = .Document.ALL.tags("TABLE")(0)
   
10.         With ActiveSheet
    
11.             .UsedRange.Clear
    
12.             For R = 0 To xlTab.Rows.Length - 1
    
13.                 For C = 0 To xlTab.Rows(R).Cells.Length - 1
    
14.                     .Cells(R + 1, C + 1) = xlTab.Rows(R).Cells(C).innertext
    
15.          On Error Resume Next
    
16.         .Refresh False
    
17.         [B1] = ""
    
18.         If Err <> 0 Then
    
19.             [B1] = "½Ð¿é¤J¥¿½TªÑ²¼¥N¸¹"
    
20.         Else
    
21.         End If
    
22.                 Next
    
23.             Next
    
24.         End With
    
25.         .Quit        'Ãö³¬ºô­¶
    
26.     End With
    
27. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 8# bioleon69

1.                 For C = 0 To xlTab.Rows(R).Cells.Length - 1
   
2.                     .Cells(R + 1, C + 1) = xlTab.Rows(R).Cells(C).innertext
   
3.                 Next

½Æ»s¥N½X

½Õ¾ã¬°

1.                 For C = 0 To xlTab.Rows(R).Cells.Length - 1
   
2.                     .Cells(R + 3, C + 1) = xlTab.Rows(R).Cells(C).innertext
   
3.                 Next

½Æ»s¥N½X

bioleon69

¦^´_ 7# c_c_lai
nice!!·PÁÂ
¦b½Ð°Ý¤@¤U
­nÅý³o¨Ç¸ê®Æ±qA3¶}©l¿é¥X ­n«ç»ò­×§ï?

c_c_lai

¦^´_ 6# bioleon69
­ì 6# ¼Ó

1.     Surl = "http://www.cmoney.tw/finance/f00026.aspx?s=& [A1].Value &"
   
2.     With CreateObject("InternetExplorer.Application")
   
3.         .Visible = True
   
4.         .Navigate " "

½Æ»s¥N½X

­×¥¿¬°¡G

1.     Surl = "http://www.cmoney.tw/finance/f00026.aspx?s=" & Sheets("XXX").[A1]
   
2.     With CreateObject("InternetExplorer.Application")
   
3.         .Visible = True
   
4.         .Navigate Surl

½Æ»s¥N½X

§Y¥i¡C

bioleon69

1. Option Explicit
   
2. Sub Ex_ºô­¶¤¸¯À()
   
3.     Dim i As Integer, xlTab As Object, R As Integer, C As Integer, Surl$
   
4.     Surl = "http://www.cmoney.tw/finance/f00026.aspx?s=& [A1].Value &"
   
5.     With CreateObject("InternetExplorer.Application")
   
6.         .Visible = True
   
7.         .Navigate " "
   
8.        ' .Navigate "http://www.cmoney.tw/finance/f00026.aspx?s=" & ÅܼÆ
   
9.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
   
10.         Set xlTab = .Document.ALL.tags("TABLE")(0)
    
11.         With ActiveSheet
    
12.             .UsedRange.Clear
    
13.             For R = 0 To xlTab.Rows.Length - 1
    
14.                 For C = 0 To xlTab.Rows(R).Cells.Length - 1
    
15.                     .Cells(R + 1, C + 1) = xlTab.Rows(R).Cells(C).innertext
    
16.                 Next
    
17.             Next
    
18.         End With
    
19.         .Quit        'Ãö³¬ºô­¶
    
20.     End With
    
21. End Sub

½Æ»s¥N½X

½Ð°Ý¤@¤U~~
.Navigate " "³oÃä­n«ç»ò´¡¤J©w¸qªºÅܼÆsurl

GBKEE

¦^´_ 4# bioleon69

1. Option Explicit
   
2. Sub Ex_ºô­¶¤¸¯À()
   
3.     Dim i As Integer, xlTab As Object, R As Integer, C As Integer
   
4.     With CreateObject("InternetExplorer.Application")
   
5.         .Visible = True
   
6.         .Navigate "http://www.cmoney.tw/finance/f00026.aspx?s=2330"
   
7.        ' .Navigate "http://www.cmoney.tw/finance/f00026.aspx?s=" & ÅܼÆ
   
8.         Do While .Busy Or .readyState <> 4: DoEvents: Loop      
   
9.         Set xlTab = .Document.ALL.tags("TABLE")(0)
   
10.         With ActiveSheet
    
11.             .UsedRange.Clear
    
12.             For R = 0 To xlTab.Rows.Length - 1
    
13.                 For C = 0 To xlTab.Rows(R).Cells.Length - 1
    
14.                     .Cells(R + 1, C + 1) = xlTab.Rows(R).Cells(C).innertext
    
15.                 Next
    
16.             Next
    
17.         End With
    
18.         .Quit        'Ãö³¬ºô­¶
    
19.     End With
    
20. End Sub

½Æ»s¥N½X

bioleon69

¦³¤j«Lª¾¹D¦p¦ó§ì¨ú¶Ü!!!!
·PÁÂ

bioleon69

¦³ÂIÅ¥¤£¤ÓÀ´XDD