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

[µo°Ý] ³B²z¤@©w®É¶¡ ¤¤Â_°õ¦æ

[µo°Ý] ³B²z¤@©w®É¶¡ ¤¤Â_°õ¦æ

³Ìªñ¦b¥ÎQueryTable ³sºô¯¸§ì¸ê®Æ

¦]¬°¦³¨Ï¥Î¨ì°j°é¨Ó³B²z

©Ò¥H°õ¦æ¤W¦³ÂI½wºC

¦³®É­Ô¦pªG¸ê®Æ¶q¤Ó¤j
Excel·|¶]¶W¤[

½Ð°Ý¦U¦ì¤j¤j

¯à§_¦b°õ¦æ¹Lµ{¤¤¥[¤J¤@­Ó¸g¹L®É¶¡ªº­pºâ

¦pªG³B²z®É¶¡¶W¹L¤@©w®É¶¡  «h¥ý¤¤Â_¨ú®ø°õ¦æ

¤£ª¾¯à§_³o¼Ë³B²z


ÁÂÁÂ
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

¦³®É­Ô¦pªG¸ê®Æ¶q¤Ó¤j
Excel·|¶]¶W¤[
li_hsien µoªí©ó 2014/9/1 11:45

ªþÀɬݬÝ,¬O§_¦³§ïµ½ªÅ¶¡,¥iÁYµuµ{¦¡¹B¦æ®É¶¡.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE

¤U¤èªºpage_no¬O¥Î¨Ó´«­¶

¦]¬°­¶­±¤@¦¸¶È¯àÅã¥Ü100µ§

¦ý¦³ªº¬d¸ßµ²ªG·|¤j©ó100µ§

«á¨Ó§Úµo²{ºô§}¤¤·|¦³­Ópageoffset
¦pªGpageoffset=100¡A«h·|´«­¶¡AÅã¥Ü²Ä101~200µ§

©Ò¥H§Úªº§@ªk¥Î­Ó°j°éÅýpageoffset +100
¤@ª½´«­¶¨ìµ§¼Æ¸ò¤W¤@¦¸¤@¼Ë¡A§Y¥Nªí³Ì«á¤@­¶¤F¡A«h°±¤î°õ¦æ¡C
  1. Sub Search_Click()

  2.     Dim Key_URL As String
  3.     Dim start_row As Integer
  4.     Dim flag_row As Integer
  5.     Dim page_no As Integer
  6.         
  7.     '¦h­¶¤Á´«
  8.     page_no = 0
  9.     '°_©lÄæ¦ì
  10.     start_row = 2
  11.    
  12.     '¦pªG¤@ª½´«­¶ ´«¨ì¨S¦³µ§¼Æ®É «h°±¤î(¶}©l¤ñ¼Æ=µ²§ô¤ñ¼Æ ¥¼¼W¥[)
  13.     Do Until (start_row = flag_row)
  14.    
  15.         start_row = Worksheets("BC Data").Range("C65536").End(xlUp).Row + 1
  16.         
  17.            Key_URL= "URL;http:// *****  & "pageoffset=" & page_no
  18.    
  19.             With Worksheets("BC Data").QueryTables.Add(Connection:=Key_URL, _
  20.                 Destination:=Worksheets("BC Data").Range("A" & start_row))
  21.                 .WebSelectionType = xlSpecifiedTables
  22.                 .WebFormatting = xlWebFormattingNone
  23.                 .WebTables = "5"
  24.                 .WebPreFormattedTextToColumns = True
  25.                 .WebConsecutiveDelimitersAsOne = True
  26.                 .WebSingleBlockTextImport = False
  27.                 .WebDisableDateRecognition = False
  28.                 .WebDisableRedirections = False
  29.                 .Refresh BackgroundQuery:=False
  30.                 .Delete
  31.             End With
  32.    
  33.         '¤@­¶­±100µ§ °õ¦æ§¹´«­¶
  34.         page_no = page_no + 100
  35.         
  36.         '¥h¼ÐÀY
  37.         Worksheets("BC Data").Rows(start_row).Delete
  38.         
  39.         '¬ö¿ý³Ì¥½¤ñ
  40.         flag_row = Worksheets("BC Data").Range("C65536").End(xlUp).Row + 1
  41.         
  42.         '¦pªG<100µ§ °õ¦æ¤@¦¸µ²§ô§Y¥i
  43.         If Worksheets("BC Data").Range("C65536").End(xlUp).Row < 201 Then Exit Sub
  44.    
  45.     Loop
  46.    
  47. '    Debug.Print page_no
  48.    
  49. End Sub
½Æ»s¥N½X
P.S. ¦]¬°¬O¤½¥q¤º³¡ºô¯¸¡A©Ò¥H§Ú¥H*****¨ú¥N¡C
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¦^´_ 3# li_hsien
¸Õ¸Õ¬Ý
  1. Sub Search_Click()
  2.     Dim Key_URL As String, T As Date  '*****
  3.     Dim start_row As Integer
  4.     Dim flag_row As Integer
  5.     Dim page_no As Integer
  6.     T = Time  'µ{¦¡°õ¦æ¶}©l®É¶¡       '******
  7.         
  8.     '¦h­¶¤Á´«
  9.     page_no = 0
  10.     '°_©lÄæ¦ì
  11.     start_row = 2
  12.    
  13.     '¦pªG¤@ª½´«­¶ ´«¨ì¨S¦³µ§¼Æ®É «h°±¤î(¶}©l¤ñ¼Æ=µ²§ô¤ñ¼Æ ¥¼¼W¥[)
  14.     Do Until (start_row = flag_row)
  15.         If Time > T + #12:10:00 AM# Then Exit Sub   'µ{¦¡°õ¦æ¶W¹L10¤À,Â÷¶}µ{¦¡
  16.         start_row = Worksheets("BC Data").Range("C65536").End(xlUp).Row + 1
  17.         Key_URL= "URL;http:// *****  & "pageoffset=" & page_no
  18.         
  19.         With Worksheets("BC Data").QueryTables.Add(Connection:=Key_URL, _
  20.                 Destination:=Worksheets("BC Data").Range("A" & start_row))
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# GBKEE

·PÁª©¤j¨ó§U !!!

©Ò¥H§Ú¥Ø«eªºµ{¦¡½X

¦b³B²z®Ä²v¤W¬O§_ÁÙ¦³§ïµ½ªÅ¶¡©O ???

ÁÂÁÂ
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¦^´_ 5# li_hsien
¦]§A³o¬O¤º³¡ºô­¶,¬Ý¤£¨ì­ì©lÀÉ,©Ò¥H¤£ª¾¦p¦ó§ïµ½.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 6# GBKEE

·PÁª©¤jÀ°§U

§Ú¦A·Q·Q¯à§_´£¨Ñ¬ÛÃö­ì©l½X

ÁÙ¬O§½³¡ªº­ì©l½X

ÁÂÁÂ
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¦^´_ 7# li_hsien



¦pºô­¶¤W¦³³oÁ`­¶¼Æ,¥i§ì¨ú³oÁ`­¶¼Æ¨Ó¶]°j°é.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 8# GBKEE

½Ð±Ð¤@¤Uª©¤j

¤U¤è¬O§ÚºI¨ú³¡¤À¸ò­¶¼Æ¦³Ãöªº­ì©l½X

¦ý§Ú¤£ª¾¹D¸Ó¦p¦ó§ì¨úÁ`­¶¼Æ(11)
  1. <SCRIPT LANGUAGE="JavaScript1.1">
  2. <!--
  3. function goToPage(s) {
  4.    var thisForm = window.document.forms[0];
  5.    var page_n = 11;
  6.    var page_range = 100;
  7.    var box_name = "go_to_page" + s;
  8.    var search_link = **********;
  9.    for (var i=0; i < thisForm.elements.length; i++) {
  10.       var nextElement = thisForm.elements[i];
  11.       if (nextElement.name == box_name) {
  12.          go_to_page = nextElement.value;
  13.       }
  14.    }
  15.    if (go_to_page == null || go_to_page == 0) {
  16.       alert("±z¥²¶·¿é¤J 1 ¨ì 11 ¤§¶¡ªº¨ä¤¤¤@­Ó­¶½X¡C");
  17.    }
  18.    else if (1 <= go_to_page && go_to_page <= page_n) {
  19.       var offset = (go_to_page - 1) * page_range
  20.       location.href = search_link + offset;
  21.    }
  22.    else {
  23.       alert("±z¥²¶·¿é¤J 1 ¨ì 11 ¤§¶¡ªº¨ä¤¤¤@­Ó­¶½X¡C");
  24.    }
  25. }
  26. //end hiding -->
  27. </SCRIPT>
  28. &nbsp;<P><B>
  29. ²Ä 1 ­¶¡A¦@ 11 ­¶<A HREF=**********>>></A>
½Æ»s¥N½X
·Ð½Ðª©¤j¸Ñ´b

ÁÂÁÂ!!!
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¦^´_ 9# li_hsien
  1. Option Explicit
  2. Sub Ex()
  3.     Dim URL As String, A As Object, B As String
  4.     URL = "d:\aaa.htm"  '§A¤º³¡ºô­¶ªººô§}
  5.     With CreateObject("InternetExplorer.Application")
  6.         .Navigate URL
  7.         .Visible = True
  8.         Do While .Busy Or .readyState <> 4:         Loop
  9.         Set A = .document.getElementsByTagName("b").Item(0)
  10.         '§A³oºô­¶¥u¬Ý¨ì¤@­Ó <B>  ªºElement¤¸¯À,©Ò¥H¬OItem(0)²Ä¤@­Ó
  11.         B = A.innertext    '"²Ä 1 ­¶¡A¦@ 11 ­¶>>"
  12.         B = Trim(Mid(B, InStr(B, "¦@") + 1, InStrRev(B, "­¶") - InStr(B, "¦@") - 1))
  13.         
  14.         'LTrim¡BRTrim »P Trim ¨ç¼Æ
  15.         '¶Ç¦^¤@­Ó¨S¦³«eÀYªÅ¥Õ (LTrim)¡B«á­±ªÅ¥Õ (RTrim) ©Î«e«á§¡µLªÅ¥ÕªºVariant (String)¡A¨ä¤¤©Ò§t¬°µ¹©wªº¦r¦ê¡C
  16.         
  17.         'InStr ¨ç¼Æ
  18.         '¶Ç¦^¦b¬Y¦r¦ê¤¤¤@¦r¦êªº³Ì¥ý¥X²{¦ì¸m¡A¦¹¦ì¸m¬° Variant (Long)¡C
  19.         
  20.         'InStrRev¨ç¼Æ
  21.         '¶Ç¦^¤@­Ó¦r¦ê¦b¥t¤@­Ó¦r¦ê¤¤¥X²{ªº¦ì¸m¡A±q¦r¦êªº¥½§Àºâ°_¡C
  22.         
  23.         MsgBox B
  24.         .Quit
  25.     End With
  26. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¬°¤H³B¥@­n¤p¤ß²Ó¤ß¡A¦ý¤£­n¡u¤p¤ß²´¡v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD