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

[µo°Ý] ¯à§_¦P®É°õ¦æ¤G­Ó¥¨¶°µ{¦¡

[µo°Ý] ¯à§_¦P®É°õ¦æ¤G­Ó¥¨¶°µ{¦¡

½Ð±Ð¦U¦ì«e½ú:

¥Ø«e¨Ï¥ÎHTTP§ìºô¸ôªÑ¥«¸ê®Æ,1101~9999,»Ý­n«Ü¦h®É¶¡(°²³]4hr)
¯à§_¤À¦¨¤G­Ó¥¨¶°µ{¦¡,¦P®É°õ¦æ
A¥¨¶°,§ì1101~4999,
B¥¨¶°,§ì5000~9999
³o¼Ë,2hr´N¥i§â©Ò¦³¸ê®Æ§ì§¹
  
¦Ó³oA.B¥¨¶°µ{¦¡¤¤,·|¥Î¨ìªº¥D­n«ü¥O
1. °²³]
"URL;http://tw.stock.yahoo.com/q/q?s=1101¡¨
2.
Workbooks.Open Filename:=1101
3.
Temp.COPY
ActiveSheet.Paste
StockNo.Select
  
¦]¬°·|¥Î¨ì¶}±ÒÀÉ®×,©MCOPY&PASTE;©Ò¥H¤G­Ó¥¨¶°µ{¦¡¦b°õ¦æ®É¤£¯àA¥¨¶°°õ¦æCOPY®É,B¥¨¶°­è¦nPASTE,³y¦¨A¥¨¶°ªº¸ê®Æ(1101)¶K¨ìB¥¨¶°(5000),³oºØ¯QÀs±¡§Î

·Q½Ð°Ý¤@¤U,©Î¦³¤°»ò¨ä¥¦¤èªk
¤d¸U§O§i¶D§Ú,´«¥úÅÖ
·P®¦

¦^´_ 1# conchih
¨Ì§Aªº·Qªk¡A¤G­Ó¥¨¶°¦P®É§ì¸ê®Æ·|¤ñ³æ¤@¥¨¶°¨Ó±o§Ö¡I¦ý¬O§A©¿²¤¤F¡yÀW¼e°ÝÃD¡z¡C°²³]§AªºÀW¼e¬°2M¡A³æ¤@¥¨¶°§ì¸ê®Æ®É¡AÀW¼e¬°2M¡F¦ý¥Î¨â­Ó¥¨¶°¤@°_§ì¸ê®Æ®É¡A¨C­Ó¥¨¶°¶È¯à¨Ï¥Î1MÀW¼e¡]Á`ÀW¼eÁÙ¬O2M¡^¡C©Ò¥H¾ãÅé¨Ó»¡³t«×¨SÅܧ֡A®Ú¥»ªº¸Ñ¨M¤èªk´N¬O´£°ªÀW¼e¡I

TOP

¥»©«³Ì«á¥Ñ thps51134 ©ó 2011-11-15 10:24 ½s¿è

¦^´_ 1# conchih
¡@¦b²Ä¤T¨B
    «ü©w¨âÃä¶K¨ì¤£¦Psheet? (ÁקK­«½Æpaste)

¡@À˵ø¥¨¶°¤¤¦³¨S¦³¤£¥²­nªºselect¸òµøÂIÂà´«, µ¹¥LÃö±¼¸ò®³±¼? (¥[§Ö³t«×)

¤£¤Ó¥¿¸g¦a¦^¤@¤U¡A
¡@·LÆ[¨Ó¬Ý¡A¤@ÁûCPU¦P®ÉÀ³¸Ó¥u°µ¤@¥ó¨Æ§a? (Âù®Ö¥H¤W¤£°Q½×)
    ©Ò¥H¤£À´¬°¤°»ò¨â­Ó¥¨¶°·|¤ñ¸û§Ö....¬Æ¦Ü§Ö¨ì¤@­¿
¡@¡@¤£¤É¯Åºô¸ô­ò
¡@  ¡@¨º¦A¥[­ÓRAM·|¦³À°§U¶Ü?¡@:D¡@(¥u¦³copy & paste¤]®t¨S¦h¤Ö§aXD)

TOP

ÁÙ¦³­Ó°ÝÃD
¥¨¶°§Ö±¶Áä?

TOP

1101~9999,»Ý­n«Ü¦h®É¶¡(°²³]4hr)
¤£·|§a¡H­n³o»ò¦h®É¶¡¡H
1101~9999¤¤¯u¥¿¦³¼Æ¾Úªº¥u¦³945µ§
§Ú°µ¦P¼Ëªº¾Þ§@¥u¦³´X¤ÀÄÁ¡C
  1. Sub Macro1()
  2.     Dim arr(), i%, j%, x, y
  3.     t = Timer
  4.     ActiveSheet.UsedRange.Offset(1, 0) = ""
  5.     For i = 1101 To 9999
  6.         With CreateObject("Microsoft.XMLHTTP")
  7.             .Open "get", "http://tw.stock.yahoo.com/q/q?s=" & i, False
  8.             .send
  9.             On Error Resume Next
  10.             k = .responsetext

  11.             x = Split(Split(k, " href=""/q/bc?s=" & i & "")(1), "<")(0)
  12.             If Err.Number = 0 Then
  13.                 m = m + 1
  14.                 ReDim Preserve arr(1 To 11, 1 To m)
  15.                 arr(1, m) = Mid(x, 2)
  16.                 For j = 1 To 10
  17.                     y = Split(Split(k, "<td align=""center"" bgcolor=""#FFFfff"" nowrap>")(j), "</")(0)
  18.                     arr(j + 1, m) = y
  19.                 Next
  20.                 arr(6, m) = Split(arr(6, m), ">")(1)
  21.             End If
  22.         End With
  23.     Next
  24.     Cells(2, 1).Resize(m, 11) = Application.Transpose(arr)
  25.     MsgBox Timer - t
  26. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# conchih
¦]¬°²o¯A¨ì½Æ»s»P¶K¤Wªº°Ê§@,
©Ò¥H§Aªºµ{¦¡¦b°õ¦æ´Á¶¡À³¸Ó­n§â½Æ»s»P¶K¤W³s¦b¤@°_±µÄò°õ¦æ.

¦Ó­Y¬O¦P®É°õ¦æ¨â­Ó¥¨¶°ªº¸Ü«o¤£®e©ö¦P¨B©¼¦¹ªº°õ¦æ®É¶¡.(ºô­¶Åª¨ú¬O¦³¥i¯àµo¥Í Delay ªº)
©Ò¥H«Øij§A§ï±Ä¥ý¦b­I´º¥Î¥t¤@­ÓÂsÄý¾¹µøµ¡Åª¨ú·sªº¸ê®Æ,
µM«á¦b­ì¥ý¤wŪ§¹¸ê®ÆªºÂsÄý¾¹µøµ¡°õ¦æ½Æ»s»P¶K¤Wªº°Ê§@,
±µµÛ½ü¬y¦b¨â­Óµøµ¡°õ¦æ¤W­z°Ê§@,
³o¼Ë´N¥i¬ÙŪ¤Jºô­¶ªº®É¶¡.(ÁöµMÀ³¸ÓÁÙ¬O¬Ùªº¤£¦h)

¤£¹L§Ú­Ë¬O«Øij¼Ó¥D°®¯Ü¥hÃÒ¨é°Ó¶}¤@­ÓÃÒ¨é±b¤á,
¥L­Ì³£·|´£¨Ñ¬Ý½L³nÅ骺.

TOP

        ÀR«ä¦Û¦b : µêªÅ¦³ºÉ¡D§ÚÄ@µL½a¡AµoÄ@®e©ö¦æÄ@Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD