WinHttp Request is a really good option to get your site status. There are other options, but in case of delivered information I would recommend that method. By using WinHttp You can achieve also other goals, but about that maybe in other article. You can do this with WinHttpRequest - set a reference to Microsoft WinHTTP Services v5.1. Something like this to capture the URL redirect: Code: Dim HTTPreq As WinHttpRequest, redirectURL As String Set HTTPreq = New WinHttpRequest With HTTPreq.Open 'GET', URL, False 'or POST maybe.Option (WinHttpRequestOptionEnableRedirects.
If You want to check if link is still active, You will just click it and see if site is online. But what should You do if You got 1000 links to check? Or more? In this article I’ll show You how to use WinHttp Request to get your site status.
In the first place I thought that my code should open Internet Explorer or any other Internet browser, then go to the chosen site address and based on content macro will know if it is active, or not. Luckily, few minutes of Google searching later I realized, that there are easier methods of checking website status.
Finally, I’ve chosen WinHttpRequest, because it was giving me all the information I wanted.
By information I mean the codes, whole variety of status codes. After some tests with different website links I realized, that I am interested in 2 numbers – 200 and 300.
Why these numbers?
First number – 200 – stands for situation, when your browser displays the file, for instance instruction in .pdf, or when your browser asks to save this file on your computer. Second number – 300 – stands for fully loaded website.
I was not interested in:
– redirection to other location or to homepage,
– error 404 page not found,
– situation when page can’t load at all.
Avoid unexpected issues
First two points are giving status number (301, 302 and 404), but the last one is different history. For this kind of situation I implemented into code:
This part is responsible to keep the code safe and uninterrupted.
Security Certificate warning
This was the second thing, which I struggled with working on this tool. After research I found out, that one magic line can deal with the problem:
Reset WinHttp object
Also a good thing is to reset object to, let’s call it, clean the memory.
I did not notice much difference with or without it in this case, but I consider this as a good approach.
Example sheet
Imagine a simple table with 10 rows of links You want to check in column A. If the link is active code will fill cell as green, if not as red.
Code
Summary
WinHttp Request is a really good option to get your site status. There are other options, but in case of delivered information I would recommend that method. By using WinHttp You can achieve also other goals, but about that maybe in other article.
I already described 2 methods how to download file from URL in one of my old articles. They are really good, but not the only methods. In this article I’m going to present the way to download file using WinHttp & ADODB method.
How to understand mix of those methods?
In simple words – send request to the server of specified URL, put the request response body into created data flow (stream) and save to the specified location.
Having this little imagination, we can start coding.
So, to be able to use ADODB, we need to send the request using WinHTTP object.
WinHttp method
First of all, let’s set the reference to have the possibility to use early binding. In my case it is Microsoft WinHTTP Services, version 5.1 in Options/References.
Having that done we can declare and set the WinHTTP object.
Now it’s time for the URL of the file to download. For the URL example we will use the meme from the How to download file from URL article.
Then we can open and send the request to the server to get the file.
There is one last thing before we can go to the second part of our approach. We need to pass the path for the new file. Not to complicate the things we will just take the path of ThisWorkbook and type picture1.jpg for the name of the file.
OK, there is everything what we will require to pass to the ADODB method to download the file.
Excel Install Microsoft Winhttp Services Version
ADODB method
To be able to use early binding of ADODB we have to set the Microsoft ActiveX Data Objects reference.
To be able to save the file using SaveToFile function we have to set the Microsoft Scripting Runtime reference.
So firstly we need to declare and create new ADODB Stream.
Secondly we should create-open the Stream and set its type as binary.
And there goes the most important part – we will write to the stream the responseBody property of the WinHTTP object and setting the stream position to the start.
Excel Install Microsoft Winhttp Services
To use the SaveToFile function we will declare and set the FileSystemObject of the Scripting library.
And finally we can save and close the downloaded file.
WinHTTP & ADODB full code
Gathering all together, there are 2 subroutines to have everything nice and clear.
In this article You could learn how to download file using WinHTTP and ADODB. I hope it will be another useful alternative to download from URL.