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

¨Ï¥ÎVBA§ì¨úºô­¶¸ê®Æ¡A¤j¬ù¤£¨ì200­¶´N·|·í±¼¡A¨D¸Ñ

¨Ï¥ÎVBA§ì¨úºô­¶¸ê®Æ¡A¤j¬ù¤£¨ì200­¶´N·|·í±¼¡A¨D¸Ñ

¦U¦ì¤j¤j¡A¤p§Ì§Ú¥Ø«e¹J¨ì¤@­Ó¦Û¤v¯à¤OµLªk¸Ñ¥Xªº°ÝÃD¡AÁÙ±æ¦U¦ì¤j¤jª¾¹D«ç»ò¸Ñ¨Mªº§iª¾¤p§Ì¡A·PÁ±z¡C
¥Ø«e­n§ì¨ú¬Y®a¤½¥q©ñ¦bºô¸ô¤Wªº¹s¥ó¸ê®Æ¡A¤j¬ù4¸U¦h­¶¡A¥u­n¶]¤j¬ù200¥ª¥k´N·|·í¾÷¡A¤£ª¾¹D¬O¤°»ò°ÝÃD¡A¤p§Ì¦³©ñ¤Wµ{¦¡¡AÁٽЦU¦ì¤j¤j¨ó§U¡A·PÁ±z¡C

§ìºô­¶¸ê®Æµ{¦¡_W.zip (101.26 KB)

µ{¦¡ÀÉ®×

clio

¦^´_ 1# clio

Sub ¥¨¶°1()
    Cells.Clear
SURL = "URL;http://www.passivecomponent.com/asp/search_chip.aspx?page2"
Set myQT = ActiveSheet.QueryTables.Add(Connection:=SURL, Destination:=Range("$A$1"))
    With myQT
        .WebFormatting = xlWebFormattingNone
        .Refresh BackgroundQuery:=False
        .Delete
    End With

SET MYQT=NOTHING
End Sub

­¶¼Æ¦Û¤v´«

TOP

¦^´_ 2# joey0415
HI Joey0415,
´ú¸Õ¹L¡A³o¼Ë§ó¦­´N·í±¼¤F»¡¡K
clio

TOP

¦^´_ 3# clio
¨S¦³§Oªººô§}¥i¤U¸ü¶Ü?
´ú¨ì1500­¶¶]ªñ10¤ÀÄÁ, 40377 ­¶­n¶]«Ü¤[½Ð¦Û¤v´ú¸Õ
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Sh(1 To 2) As Worksheet, q As QueryTable, i  As Long, Rng As Range
  4.     Dim xTime As Date
  5.     With ThisWorkbook
  6.         Set Sh(1) = .Sheets(1)
  7.         Set Sh(2) = .Sheets(2)  '**.Sheets("¤u§@ªí3")
  8.     End With
  9.     With Sh(1)
  10.        '****³o¬q¬O­n§R°£¤u§@ªí1¤W¦³¤Ó¦hªº QueryTable  (·|·í¥i¯à¬O¦b³o)****   
  11.         For Each q In .QueryTables
  12.             q.Delete
  13.         Next
  14.         '****³o¬q¬O­n§R°£¤u§@ªí1¤WQueryTableªº¦WºÙ
  15.         For i = .Names.Count To 1 Step -1
  16.             .Names.Item(i).Delete
  17.         Next
  18.         '**³]©w§Aªº¥~³¡¬d¸ß¦b©T©wªºQueryTable¤W
  19.         If .QueryTables.Count > 0 Then
  20.             Set q = .QueryTables(1)
  21.         Else
  22.             Set q = .QueryTables.Add(Connection:="URL;http://www.passivecomponent.com/asp/search_chip.aspx?page=1" _
  23.                         , Destination:=.[A1])
  24.         End If
  25.     End With
  26.     xTime = Time
  27.     Application.ScreenUpdating = False
  28.     For i = 1 To 40377
  29.         With q
  30.             .Connection = "URL;http://www.passivecomponent.com/asp/search_chip.aspx?page=" & i
  31.             .WebFormatting = xlNone
  32.             .RefreshStyle = xlInsertDeleteCells
  33.             .AdjustColumnWidth = True
  34.             .Refresh BackgroundQuery:=False
  35.             DoEvents
  36.             Set Rng = .ResultRange '**¥~³¡¬d¸ßªº¸ê®Æ°Ï
  37.             If i = 1 Then
  38.                 Sh(2).UsedRange.Clear
  39.                 Sh(2).Range("A1").Resize(Rng.Rows.Count, Rng.Columns.Count) = Rng.Value
  40.             Else
  41.                 Sh(2).Range("A" & Sh(2).Cells(Rows.Count, 1).End(xlUp).Row + 1).Resize(Rng.Rows.Count, Rng.Columns.Count) = Rng.Offset(1).Value
  42.             End If
  43.         End With
  44.         Application.StatusBar = "¤U¸ü¶}©l: " & xTime & " ¦@ " & i & " ­¶ ok " & Application.Text(Time - xTime, ["m¤Às¬í"])
  45.     Next
  46.     Application.ScreenUpdating = True
  47.     MsgBox Application.Text(Time - xTime, ["m¤Às¬í"]) & "   Finish"
  48. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 3# clio

§Ú¤]´ú¸Õ¹L®@¡I¤T¤ÀÄÁ¤U¸ü300­¶¨S¦³°ÝÃD
«Ü¶¶

TOP

¦^´_ 5# joey0415
Hi Joey0415,
¯à§_¤]§â±zªº§¹¾ãµ{¦¡¡AÅý§Ú¨Ó¬ã¨s¤@¤U¡A¬Ý¬O®t§O¦b¨ºÃä¡A¬°¤°»ò§Úªº·|·í±¼¡A­Y¬O¤è«Kªº¸Ü¡A·PÁ±z¡C
clio

TOP

¦^´_ 4# GBKEE
·PÁÂGBKEE,
«ôŪ¤F±zªºµ{¦¡¡A§Ú¤j¬ùª¾¹D§Ú¥d¦b¨º­Ó¦a¤è¤F¡A¦Ó¥B©pªº¼¶¼g¤è¦¡¡AÅý§Ú¨ü¥Î«Ü¦h¡A¤Ó·PÁ±z¤F
§Ú¤j¬ùª¾¹D¸Ó¸Óªí³æªºQueryTables¤Ó¦h¤F¡A¦³¤­¦Ê¦h­Ó¡AÃø©Ç·|¥d¦í¡A©Ò¥H§Ú¤F¸Ñ­n§âQueryTales¬å±¼ªº­ì¦]¤F
¦ý¬O¬°¤°»ò·|¦³³o¼Ë¦hªºNames¡A¦Ó¥B§â³o¨ÇNames¬å±¼ªº¦n³B¬O¡A¤°»ò¡A³oÂI§Ú¤£¤Ó²M·¡¡A¯à§_½ÐGBKEEÀ°§Ú¸Ñ»¡¤@¤U©O¡H
·PÁ±z
clio

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2018-10-9 12:56 ½s¿è

¦^´_ 7# clio
ÀÉ®×½G¨­
QueryTable ,Name¤Ó¦hÀÉ®×·|µê­D,§A¤@ª½ªº¦sÀɰʧ@,·|³Ý¦º.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 8# GBKEE
¤F¸Ñ¤F¡A·PÁÂGBKEE¡A©Ò¥H§â©Ò¦³ªºQueryTable§R±¼¡A¥u¯d¤U¤@­Ó¡A¨Ã§Q¥Î°j°é§ïÅÜqªºconnection­È¡A´N¤£·|ÅýQueryTableªº¼Æ¶q¤@ª½¼W¥[¡A³o¼Ë¤]¤£·|¹³§Ú­ì¨Óªºµ{¦¡¤@¼Ë¡A¦]¬°QueryTable¶W¹L¤@©wªº¼Æ¶q¡A³y¦¨·í±¼¡A³o¼Ë§Ú´N¥þ²z¸Ñ¤F¡A·PÁ±zªº­@¤ß¸Ñ»¡
clio

TOP

¦^´_  clio
ÀÉ®×½G¨­
QueryTable ,Name¤Ó¦hÀÉ®×·|µê­D,§A¤@ª½ªº¦sÀɰʧ@,·|³Ý¦º.
GBKEE µoªí©ó 2018-10-9 12:55



    ³o­Ó¤ñ³ë¯u¦n... ½s¼gµ{¦¡®É «Ü¦h³£¥u¦b·N³t«× ½T³s¤£¥Î¥á¶i°j°éªº¤]¥á¶i¥h ¾É­P¤@­Ó©T©w­È ³Q°õ¦æN¦¸ ....

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD