Accessing websites via VBA

Hey guys,

I'm working on my master thesis right now and I'm facing quite a complicated problem (at least for me). Maybe someone can help to solve it or least tell me if it is actually solvable. I haven't found anything comparable in the topics database here.

I have an Excel file with more than a thousand links to websites in one column. They always refer to kickstarter.com, could be for example this link:

https://www.kickstarter.com/projects...ref=nav_search

So, I'll explain to you what I manually would need to do now. I want to click each link in the Excel file, visit the website, check if there is a link somewhere (slight problem: there might be more than one link), click this/these link(s), and check if the/these link(s) is/are still active. Then I want to make a note into the Excel file, more precisely into the column next to the respective link, which says either "No link available on kickstarter.com" or "Link(s) available but the respective website(s) is/are inactive" or "Link(s) available and website(s) still active". It doesn't matter if one or more more than one link is still active, that would always be category 3. Well, actually I wouldn't write out the three options either, just assign 1,2, and 3 to them. But just so that you get the picture.

My question is now: Is it possible to automate that in Excel oder in any other (affordable) software? Stata for example would actually also work, I have a license for that. And if it is not possible, can I at least automate to click the respective link in the Excel file and then check on this website if there is an URL at all?

Thanks a lot in advance!

Cheers,
Jonas

Jonas,
There are 152 links on the above site ONLY. There are about 15 with HTTP, and 3 e-mails link. The majority are "about".
I do not want to try to open some of the about links as I am warned by my Anti virus. Most of the HTTP links returned "OK" status.
Imagine excel has to go to each links for each site. If you assume the average link per site is 100, you are going to end up with massive data. I am not sure if execel is the right tool. If you have other better tools, you should avoid using excel.

Hey,

thanks for your quick response.

Only the links that appear in the "About" section where the person or group explains the project are relevant and only http or https links need to be considered. If I read through the text in my specific example I don't find too many links? Does that help in any way?

Thanks and best regards,
Jonas

To give you some idea. I have run the code on the above site. All links are returned on column A and I did run some test on HTTP to see if the link work. Most of them do. The facebook site is auto-logged which is annoying. Try and see what do you want to do. The value on column B do not align to the links.

Thank you! I see, there are a lot of links... The one we would need is in row 65 (http://thevaultmusicstudios.nl/). This is obviously the link to there website and for our research we need to know if this link is still working (in this case it is). This might not be true if the respective company is already out of business. So, there are a bunch of (older projects) for which the link is not functioning anymore when we checked it manually.

I do not know what are you trying to accomplish. I filter out the links with HTTP only. You are now left with 17 links. How do you identify which links you are interested on?

We are almost there! If we then filter out all links that contain social media stuff (facebook, twitter, tumblr, pinterest, instagram, youtube) and all links that contain "kickstarter", we are left with two links. Is it possible to automatically check in excel if these two links are working?
If not, it is still already a huge step for us if we can get these two links (might sometimes be one or three but probably not ten) shown in the columns next to the original kickstarter links. This would only leave us with the task of checking the links. The first step (going to the respective kickstarter page and search for all relevant links) would be done automatically, right?

Okay!
This will do for now, but I probably need to tweak a bit for aligning column B with A. It will also help if you could add more URLs on column A for testing. When you attach a sample, it has to be an excel book, not a picture please.

Thanks so far, this is really awesome!

I attached a workbook with further link example in the first sheet.

The second sheet shows how we currently do it manually. So, the first column shows the actual Kickstarter link. If more than one link is found on the Kickstarter website for the respective project, the links are shown in column B and each link has always the original Kickstarter link in column A. Right now, it makes it easier for us to analyze it. I don't know if it is complicated to program? By the way, for the Central Standard Time project, the website is not available anymore. However, the link is still leading to something, so this would give a "Link is working", right?

Another way to do it is shown in the third worksheet, that would also be fine with us.

Also, what happens if there is no link found on the Kickstarter. Does it result in a blank cell in column B oder does the entire link disappear?

Thank you so much, you're really helping us!

Alright, it's still great! It narrows it down a lot and saves us days!

If we were in the same town, I would totally buy you a beer for that

Hey, so I tried to work with your excel a bit and I have a problem that I cannot fix. If I try to run the macro, it always gives Runtime Error 429 “ActiveX component can’t create object”. I have never had this problem before with macros and I tried to google possible solutions but nothing has worked so far. I found that there might even be several reasons for the error. Do you know the specific problem in this case?

I had spent over 6 months on "Error 429 “ActiveX component can’t create object."
I had given up twice after spending a week on searching. I stumbled across a Japanese site and did Google translation. At last I found the reason for the error. There are many reasons for this error. On my particular case was ActiveX only works on the 32 bits excel. My code does not refer to ActiveX, so I do not know why the code errors. In which line does the code error?

It seems like it is in the line

With CreateObject("MSXML2.XMLHttp")

I really do not know.
What versions of excel do you have?
Did you not use this code before and was working?
You could try this line.

With CreateObject("Winhttp.winhttprequest.5.1")

Still not working... I use Excel 2016 on a Mac and I also tried it on a Windows Laptop, also with Excel 2016

Mac is no go area for me.
I thought you run the code and was working last week. What has changed since then? You did not get an error when you run it first time.

Well, it started when I actually tried to transfer the macro to the overall Kickstarter list excel file. But if you don't know what the the problem is, I will find a solution another way. I mean it's obviously working for you. What version do you use? I have also access to several others computers, it might work on one of them. This runtime error can apparently occur on one computer while it does not on another

I have excel 2010, 64 bits.
Can you check this

On the code (VBA Project) go to "Tools" Then "References" and from the drop down, check what versions of XML do you have?

Mac doesn't support active x, you won't get this code working.

To do web queries on a mac you need to use AppleScript, I've seen it done but never done it so I'm afraid I can't help either

Fortunately, I have access to other computers and I found one on which it is working properly.

So I played around a bit and up to now there are two problems occuring with certain links. To be honest, I don't know what is different about them. I have attached two links as examples. For the first one I get some kind of security warning and the macro stops. And the second one says something about an invalid argument. Do you have the same problems with these links and can you tell the reason? The problem is that the entire macro stops when it comes to These links. It would be fine to just leave them out or give an "INVALID" where otherwise the link from the Kickstarter website would be or so.

Thank you!

Yes to the first one, but not on the second one.
The code is supposed to return 140 links, but only returning 138. I am trying to find out the culprit link, but it is not easy. I do not know why it errors until I see the link. In the mean time, you can cheat VBA, though, not a good idea by adding an error trapping which skips that link and the code will finish.
On error resume next

It looks like the hyper link is broken.

p><a href="http://Deviantart:http://nichi-san.deviantart.com/" target="_blank">Deviantart:http://nichi-san.deviantart.com/</a><br><a

The code returns this as Text rather than a hyperlink, hence for the error.

Ok, so if such an error happens, for our analysis it would probably best to somehow state in the output that an error occurred and then just go on with the next link

Hey, so luckily I did not mark the thread as solved.

I have one more problem that I tried to solve by myself but I did not get there. After running the macro for like 500 links, it gives me an error that there is not enough memory. The computer on which it is running has sufficient RAM and I tried to delete the cache after every iteration. I also tried to save it after every iteration which makes the entire process slower but apparently does the trick of reducing memory storage sometimes. All these options did not working and I still cannot run the program for more than 400 or 500 links. I need to do it with 45000 though, so doing it all at once would be awesome.

I hope you can once again help me. I attached the current version of the excel with 500 links in it.

Thanks!

So, I talked to my supervisor since the whole list was not created by me. He does not want the entire list to be published on the internet due to intellectual property concerns. I hope you can still help me. However, since this problem is not directly related to the topic of this thread, I started a new thread so that others can maybe have a look at it. I hope that's okay.

Hi !

Speed issue : saving workbook at each iteration of loop
as well creating the same new object within loop (may occurs memory issue) instead of creating it once before the loop …

It would be replay helpful to me if you incorporated the recommendation in the actual code. Thanks!