- A+
URL数据表放在A列,代码种设置10000行数据,其实很慢,大量数据用python实现。
Sub Macro1()
Dim source As Range, req As Object, url$
Dim rc As Integer
Set req = CreateObject("Msxml2.ServerXMLHTTP.6.0")
Sheets("Sheet1").Select
Set source = Range("A1:A10000")
source.Columns(2).Clear
For i = 1 To source.Rows.Count
url = source.Cells(i, 1)
On Error Resume Next
req.Open "HEAD", url, False
req.SetRequestHeader "Accept", "image/webp,image/*,*/*;q=0.8"
req.SetRequestHeader "Accept-Language", "en-GB,en-US;q=0.8,en;q=0.6"
req.SetRequestHeader "Accept-Encoding", "gzip, deflate"
req.SetRequestHeader "Cache-Control", "no-cache"
req.SetRequestHeader "Content-Type", "text/xml; charset=utf-8"
req.SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.111 Safari/537.36"
req.Send
source.Cells(i, 2) = req.Status
On Error GoTo 0
Next
MsgBox "Finished!"
End Sub

独角兽驿站
公众号