¨Ï¥ÎVBA§ì¨úºô¶¸ê®Æ¡A¤j¬ù¤£¨ì200¶´N·|·í±¼¡A¨D¸Ñ
- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¦^´_ 3# clio
¨S¦³§Oªººô§}¥i¤U¸ü¶Ü?
´ú¨ì1500¶¶]ªñ10¤ÀÄÁ, 40377 ¶n¶]«Ü¤[½Ð¦Û¤v´ú¸Õ- Option Explicit
- Sub Ex()
- Dim Sh(1 To 2) As Worksheet, q As QueryTable, i As Long, Rng As Range
- Dim xTime As Date
- With ThisWorkbook
- Set Sh(1) = .Sheets(1)
- Set Sh(2) = .Sheets(2) '**.Sheets("¤u§@ªí3")
- End With
- With Sh(1)
- '****³o¬q¬On§R°£¤u§@ªí1¤W¦³¤Ó¦hªº QueryTable (·|·í¥i¯à¬O¦b³o)****
- For Each q In .QueryTables
- q.Delete
- Next
- '****³o¬q¬On§R°£¤u§@ªí1¤WQueryTableªº¦WºÙ
- For i = .Names.Count To 1 Step -1
- .Names.Item(i).Delete
- Next
- '**³]©w§Aªº¥~³¡¬d¸ß¦b©T©wªºQueryTable¤W
- If .QueryTables.Count > 0 Then
- Set q = .QueryTables(1)
- Else
- Set q = .QueryTables.Add(Connection:="URL;http://www.passivecomponent.com/asp/search_chip.aspx?page=1" _
- , Destination:=.[A1])
- End If
- End With
- xTime = Time
- Application.ScreenUpdating = False
- For i = 1 To 40377
- With q
- .Connection = "URL;http://www.passivecomponent.com/asp/search_chip.aspx?page=" & i
- .WebFormatting = xlNone
- .RefreshStyle = xlInsertDeleteCells
- .AdjustColumnWidth = True
- .Refresh BackgroundQuery:=False
- DoEvents
- Set Rng = .ResultRange '**¥~³¡¬d¸ßªº¸ê®Æ°Ï
- If i = 1 Then
- Sh(2).UsedRange.Clear
- Sh(2).Range("A1").Resize(Rng.Rows.Count, Rng.Columns.Count) = Rng.Value
- Else
- 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
- End If
- End With
- Application.StatusBar = "¤U¸ü¶}©l: " & xTime & " ¦@ " & i & " ¶ ok " & Application.Text(Time - xTime, ["m¤Às¬í"])
- Next
- Application.ScreenUpdating = True
- MsgBox Application.Text(Time - xTime, ["m¤Às¬í"]) & " Finish"
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|