Sub CommandButton12()
Const colUrl As Long = 1 'Must always be the first column
Const colFacebook As Long = 3 'Must always be the last column of Some platforms
Const colError As Long = 4 'Must always be the last column
Dim url As String, http As Object, htmlDoc As Object, nodeAllLinks As Object
Dim onelink As Object, pageLoadSuccessful As Boolean
Dim tbl_url_oal As String, tbl_all As String, cr_tbl_urls As Long, lastRowTableUrls&
Dim cr_table_all(1 To 3) As Long, lastRowTableAll&, addressCounters&(2 To colFacebook)
Dim checkCounters As Long, cel As Range
tbl_url_oal = "foglio2" 'Name of Sheet
cr_tbl_urls = 4 'First row for content
tbl_all = "Sheet1" 'Name of Sheet
Sheets(tbl_url_oal).Activate
With New XMLHTTP60
On Error Resume Next
For Each cel In Range("A4:A" & Cells(Rows.Count, 1).End(xlUp).Row)
.Open "GET", cel.Value, False
.send
If .Status = 200 Then cel.Offset(, 1).Value = Split(Split(.responseText, "<title>")(1), "</")(0)
Next
End With
For checkCounters = colUrl To colFacebook
cr_table_all(checkCounters) = 2 'First rows for content
Next
Set htmlDoc = CreateObject("htmlfile")
Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
'Loop over all URLs in column A in the URL source sheet
Do While Sheets(tbl_url_oal).Cells(cr_tbl_urls, 1).Value <> ""
If Not Sheets(tbl_url_oal).Cells(cr_tbl_urls, 1).Font.Bold Then
If ActiveSheet.Name = tbl_url_oal Then
Sheets(tbl_url_oal).Cells(cr_tbl_urls, 1).Activate
End If
url = Sheets(tbl_url_oal).Cells(cr_tbl_urls, 1)
On Error Resume Next
http.Open "GET", url, False
http.send
If Err.Number = 0 Then
pageLoadSuccessful = True
End If
On Error GoTo 0
If pageLoadSuccessful Then
htmlDoc.body.innerHTML = http.responseText
Set nodeAllLinks = htmlDoc.getElementsByTagName("a")
cr_table_all(1) = Sheets(tbl_all).Range("a" & Rows.Count).End(xlUp).Row + 1
For Each onelink In nodeAllLinks
DoEvents
Sheets(tbl_all).Cells(cr_table_all(1), 2) = Right(onelink.href, Len(onelink.href) - InStr(onelink.href, ":"))
Sheets(tbl_all).Cells(cr_table_all(1), 1) = url
cr_table_all(1) = cr_table_all(1) + 1
addressCounters(2) = addressCounters(2) + 1
Next
Else
End If
pageLoadSuccessful = False
Erase addressCounters
lastRowTableAll = Sheets(tbl_all).Cells(Rows.Count, colUrl).End(xlUp).Row
For checkCounters = colUrl To colFacebook
cr_table_all(checkCounters) = lastRowTableAll + 1 'First rows for next page content
Next
Sheets(tbl_url_oal).Cells(cr_tbl_urls, 1).Font.Bold = True
End If
cr_tbl_urls = cr_tbl_urls + 1
Loop
Set http = Nothing: Set htmlDoc = Nothing
Set nodeAllLinks = Nothing
Set onelink = Nothing
Dim r As Range, c
Sheets("Sheet1").Activate
[b1] = "header"
[c1] = [b1]
For c = 4 To Sheets("Foglio2").[3:3].Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
[c2] = "*" & Sheets("Foglio2").Cells(3, c) & "*"
Columns("B:B").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("c1:c2"), CopyToRange:=[e1], Unique:=True
Set r = [e1].CurrentRegion
Set r = Range(Cells(2, 5), Cells(r.Rows.Count, 5))
If Len([e2]) Then r.Copy Sheets("Foglio2").Cells(4, c)
r.Delete
Next
End Sub