- ©«¤l
- 13
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 17
- ÂI¦W
- 0
- §@·~¨t²Î
- mac
- ³nÅ骩¥»
- os10
- ¾\ŪÅv
- 10
- µù¥U®É¶¡
- 2015-6-16
- ³Ì«áµn¿ý
- 2015-8-10
|
¤£¦n·N«ä¡A·Qn¦A¸ß°Ý¤@¤U
range³o¼Ëªº¼gªk¥X¤F¤°»ò°ÝÃD¶Ü¡H
Range("a" & i - 1 * 100 + 88 & ":l " & i * 100 + 88).Copy j
¬°¤°»ò¨C¦¸°õ¦æªº®ÉÔ³£¥X²{1004¿ù»~
¾ã²Õcode¨£ªþ¥ó¡A¥Dn¬On¥Îvba¥sie¡A½Æ»s¶K¤W¨ä¤º®e¦Üexcel
(·|³o¼Ë¾Þ§@¬O¦]¬°¸Óºô§}µLªk¥Îquerytable¤U¸ü¡^- Sub Â^¨úyahoofinanceetf()
- Dim j As Range
- Dim i As Integer
- Dim url As String
-
- Cells.Clear
-
- Application.ScreenUpdating = False
- Set ie = CreateObject("internetexplorer.application")
- For i = 1 To 16 '±ý½Æ»s¤U¸üªººô¶¶¼Æ¦³16¶
- With ie
- .Visible = False
- .Navigate "http://finance.yahoo.com/etf/lists/?mod_id=mediaquotesetf&tab=tab4&scol=avgcap&stype=desc&rcnt=100&page=" & i
- Do While .ReadyState <> 4
- DoEvents
- Loop
- .ExecWB 17, 2
- .ExecWB 12, 2
- End With
- If i = 1 Then '¦pªG¬O²Ä¤@¶ªº¸Ü±qA1¶}©l¶K
- Range("A1").Select
- ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
- False, NoHTMLFormatting:=True
-
- Range("a87:l187").Copy Range("a1") '¦]¬°¬O¥þ¶½Æ»s¬G¥u±N©Ò»Ýªºªí®æ½Æ»s¶K¤W¨ì«ü©w¦ì¸m
- Range("a102:l302").Clear '°£¥H¤W©Ò»Ý¥~¤§«áªº¤º®e²M°£
- Else '¦pªG¬O²Ä¤G¶¤§«áªº¤º®e
- Set j = Cells(Rows.Count, "a").End(xlUp).Offset(1) '±Nrange J«ü©w¦b¨Ï¥Î¹LÀx¦s®æªº¤U¤@ӪťÕÀx¦s®æ
- j.Select
- ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
- False, NoHTMLFormatting:=True
- Range("a" & i - 1 * 100 + 88 & ":l " & i * 100 + 88).Copy j '±N©Ò»Ýªº¸ê®Æ¶K¦Ürange j
- j.EntireRow.Delete '±N¨º¤@¦æ¾ã¦æ§R°£¡A¦]¬°¬O¼ÐÃD¦C¡A«e±¤w¦³¦P¼Ë¤º®e
- Range("a" & i * 100 + 2 & ":l65536").Clear '°£©Ò»Ý³¡¥÷¡A¨ä¾l¤º®e²M°£
- Set j = Nothing '²M°£jªº½áÈ¡A¤U¦¸´`Àô¦A«·s©w¸q·sªºrange j
- End If
- Next i
-
- ie.Quit
-
- End Sub
½Æ»s¥N½X |
|