2

My company uses short URLs for marketing campaigns, which redirect to URLs with tracking. For example:

example.com/campaign

will redirect to:

example.com/utm_source=one&utm_medium=two&utm_term=three&utm_content=four&utm_campaign=five

I have an excel file with a list of short URLs, but the tracking URLs are not listed. The current process is to manually open each link and copy the URL.

Is there a way to copy the tracking URLs into excel using VBA?

So far I've explored the following:

Dim ie As Object
Set ie = CreateObject("Internetexplorer.Application")
ie.Navigate Cells(1, 1)

But I can't find a way to then copy the new URL back into excel.

I've also tried:

Dim link As Hyperlink
Cells(1, 1).Select
Set link = ActiveSheet.Hyperlinks.Add(Anchor:=Cells(1, 1), Address:=Cells(1, 1))
link.Follow
Cells(1, 2) = link.Address

However this just (unsurprisingly) brings back the original hyperlink.

How can I bring back the tracking URL? Ideally without having to install any other software (as my company's IT department is a nightmare) but if that's the only option then so be it.

Moogle
  • 434
  • 1
  • 8
  • 25

2 Answers2

3

I believe this will do what you want:

Set webClient = CreateObject("WinHttp.WinHttpRequest.5.1")
webClient.Option(6) = False
webClient.Open "GET", Cells(1, 1), False
webClient.send ("")
Cells(1, 2) = webClient.GetResponseHeader("Location")

I got the idea from this other answer.

What you are trying to do is get the location you're redirecting to.

Redirects follow a standard process described here... and I'm just grabbing the appropriate header from the response.

Community
  • 1
  • 1
duncanc
  • 321
  • 2
  • 5
1

You want the .LocationURL property

Const READYSTATE_COMPLETE As Long = 4
Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")

IE.Navigate Cells(1, 1).Value

'// Wait for page to finish loading
While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
    DoEvents
Wend

Cells(1, 2).Value = IE.LocationURL
SierraOscar
  • 17,507
  • 6
  • 40
  • 68