Macro Excel capture link redirects

Asked

Viewed 314 times

0

It is possible to create a macro that copies the redirects I have on links in cells A1:A100 ?

For example in the cell A1 have www.pplware.com, but when we open this url I am redirected to another url https://pplware.sapo.pt

What I need is for the macro to write this redirect in the cells B1:B100, it is possible to do this ?

excel vba macro

  • Did you ever mount any vba code? If you edit the question and add it.

  • Thank you, it’s already solved.

1 answer

3

The Axel-Richter I’ve already been able to help and it’s working well.

Printscreen

  Public Function testRedirect(oCell As Range) As String

   testRedirect = "Não Redireciona"

   strURL = oCell.Hyperlinks(1).Address

   WinHttpRequestOption_EnableRedirects = 6

   Set oWinHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
   oWinHttp.Option(WinHttpRequestOption_EnableRedirects) = False

   oWinHttp.Open "HEAD", strURL, False
   oWinHttp.send ""

   If oWinHttp.Status = 301 Then
    strResponseHeaders = oWinHttp.getAllResponseHeaders()
    For Each strResponseHeader In Split(strResponseHeaders, Chr(10))
     If Left(strResponseHeader, 9) = "Location:" Then
      testRedirect = "redirected to " & strResponseHeader
     End If
    Next
   End If

End Function

Browser other questions tagged excel vba macro

You are not signed in. Login or sign up in order to post.