有没有办法通过使用VBA的301重定向获取返回的URL？ [英] Is there a way to get the returned URL from a 301 redirect using VBA exclusively?

问题描述

我有一个很长的公司的产品编号，后来的代码中，这些号码必须转换为HTML，然后链接到产品的相应页面。到页面的链接有许多301重定向，从一些凌乱/旧/错误/等到新的SEO友好的URL。

例如，如下所示：

site.com/12345

将被重定向到此页面：

site.com/product-name-12345

我没有列表来比较产品名称，因为它们被创建并且每天被破坏，并且该站点即时创建URL（而不是从某种数据库或某些数据库中绘制）。

由于很多复杂的原因，我只可以访问Excel来完成此任务（hooray！）。我写了（坏）代码打开Internet Explorer，转到短URL，然后将新地址复制到需要的内容中。这是有效的，但它是非常缓慢的（如你所料）。我想知道是否有一个解决方案不依赖于等待IE打开，但是完全在VBA中运行？

我已经找到了很多方法看看重定向是否发生，我已经找到很多方法来获取标题，但是我还没有找到一种获取最终URL的方法。这些主要关注于WinHTTP.WinHTTPRequest.5.1，但是似乎没有任何类型的WinHTTPResponse调用（如.NET支持的HttpResponse in .NET）。

任何帮助将不胜感激，谢谢！

编辑：这是在另一篇文章回复到Reddit的Excel论坛。我需要的答案是.Option（1）after .Send（如果有人需要的话）。

讨论可以在这里阅读

解决方案

这是在另一篇文章中回复到Reddit的Excel论坛。我需要的答案是.Option（1）.Send（如果有人需要）。

I have a long list of our company's product numbers, and later in the code these numbers have to be converted to HTML and then linked to the product's corresponding page. The links to the pages have numerous 301 redirects to get from something messy / old / mistyped / etc. to the new SEO friendly URLs.

For example, something like this:

site.com/12345

Will get redirected to this page:

site.com/product-name-12345

I do not have a list to compare the product names against, as they are created and destroyed daily, and the site creates the URL on the fly (rather than drawing from some kind of database or something).

For a lot of complicated reasons, I only have access to Excel to complete this task (hooray!). I've written (bad) code to open Internet Explorer, go to the short URL, and then copy the new address into whatever was needed. This works, but it's incredibly slow (as you might expect). I'm wondering if there is a solution that doesn't rely on waiting for IE to open, however runs exclusively in VBA?

I have found a lot of ways to see if the redirect takes place, and I have found a lot of ways to get the headers, however I have yet to find a way to get the final URL that occurs. These mostly focus on WinHTTP.WinHTTPRequest.5.1, however it seems as though there isn't any kind of WinHTTPResponse to call (like HttpResponse in .NET) supported by Excel.

Any help would be greatly appreciated, thanks!

EDIT: This was answered on another post to Reddit's Excel forum. The answer I needed was .Option(1) after the .Send (if anyone else needs it).

Discussion can be read here

解决方案

This was answered on another post to Reddit's Excel forum. The answer I needed was .Option(1) after the .Send (if anyone else needs it).

这篇关于有没有办法通过使用VBA的301重定向获取返回的URL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！