VBA - Tweak code to 'unwrap' shortened URLs for specified range on click

Hi all,

I found this code which gives you the full URL of a shortened (e.g. bitly) link:

Please Login or Register  to view this content.

You basically use this on a cell by cell basis via =unwrap(A1) formula.

I have a fixed range of shortened URLs (that change dynamically) that I would like to get unwrapped one by one, but only at the click of a button. So ideally, this wouldn't be a public function, but a simple sub that I can manually execute on a specified range of cells.
Anyone has an idea how to do that? Tried a few things, but totally lost :-(

Hope you can help?
Thanks!

Hey CheeseSandwich,

Ace - thanks for the super speed reply!

So I added the worksheet to look at as well and updated the range, but sadly, this throws an error on the "rng = " row: Run-time error '91': Object variable or With block variable not set

Do you know what that means?

Please Login or Register  to view this content.

Also, I just noticed that the cells in range H9:H contain a formula that outputs the shortened URL.
Tried the previous =unwrap(A1) on those and it didn't work as I believe it reads the formula, not the output. Is there any tweak to that I could incorporate?

Thanks so much for the help!
Cheers!

Great - thanks! That got it to work, but it sadly only copies the URL instead of 'unwrapping' it - I guess this is because the cells in column H contain a formula that outputs the URL.

An example - this is the formula in cell H9:

Formula:

Please Login or Register  to view this content.

...it basically searches a large text document for a specific, shortened bitly link.

The output of this formula is "https://bit.ly/3b2vDXN".

If I apply the =unwrap(A1) formula on just the URL, this works, however, it doesn't work on cell H9 as it contains the formula (I believe).
Do you think there's any workaround for that? I.e. let VBA pick the value of the cell rather than the formula to run this?

Also, some cells in my range H9: range are left empty as the IFERROR formula kicks in. The script seems to stop when there is an empty cell so I changed the output of the formula to be "-" instead of a blank so the script continues to run, but not sure if that would throw an error now as "-" is not a valid URL?

Thanks so much for the help - appreciate it!!

Sure - attached, hope this works?

One thing to notice is that it does seem to work on some URLs, but not on others (can't share those ones sadly). So I may need to look into all the options there are for "oRequest" - maybe I can tweak this to also recognize other, shortened URLs.

Thanks so much for your help!

Thank you!! That's super helpful - I tweaked it slightly to skip blank cells now (not the ones with "-").

Please Login or Register  to view this content.

Only problem that remains is that some wrapped URLs don't seem to work (they do work and redirect if I open in browser). The script just seems to copy/paste the same URL into column "I".
Think this may be down to some redirect setting maybe. Are there other methods of unwrapping the URL? I'll see if I can find anything...

Thanks so much!

Ok, I think I found something - apparently (according to this), WinHTTP.WinHTTPRequest.5.1 only looks at the first Location header (not the last link in the chain of redirects).
It is recommended to use something like this instead:

Please Login or Register  to view this content.

Tried to just change WinHTTP.WinHTTPRequest.5.1 to MSXML2.ServerXMLHTTP, but that doesn't work. Any chance you could help me tweak the code to look at the last link in the chain of redirects?

Thanks so much!

Thanks for the super speedy reply! Tried this, but unfortunately get a "compile error: User-defined type not defined" on row "Function unwrap(url As String)".

Is there also a chance too add in the redirect type in another column? The script I found does this nicely, but sadly only for that 1 URL :-(

OMG! LIFESAVER! This works perfectly - thank you soooo much!!!