Extracting Specific Data From 500 Intranet URLs In Excel On To Another Column

Gurus,

Please note that I have searched far and wide trying to piece up some VBA code but have failed miserably.


In Excel, I have COLUMN A populated with 500 URL links (all intranet urls) that have the following format: http://testportalwps/search/Pages/Pe...lts.aspx?k=411

The urls are all the same and the only thing that changes are the 3 digits in the end and they are not sequential.

All these urls open up to a webpage where I would like to extract certain pieces of information like First/Last Name, Telephone Number, etc. into COLUMN B.

I know I can do "Get External Data From Web" in excel but that gives me too much information plus I would have to plug that url in every time.

Any other ideas please? I was thinking if I could get the entire source code of each of the webpages into COLUMN B, I could then regex out the info I need. Unless anyone has any easier method.

Thanks in advance.

The link does not work.
Please attach a sample excel book, including the URLs in column A and the desired results in columns B, C or D.

Web Scraping tutorial …

You will find it difficult to get a data from the Intranet site if you are not familiar with basic HTML and web scrapping methods. Since we do not know or can not see the source file(view), it is almost impossible to come up with a solution.
For instance, I can see the source file for the site you have attached and can work through to get the area where the page I want to get in to.
I can try, but not sure if it works. Do the following:
Right click on the Last/First Name & Department
Click inspect element. This will open up the page. Do not click the source view as this opens the entire page, but we are only interested on the part of the page (Last/First Name & Department.) section
Then, copy 5 or 6 lines below and above of the line. When you click the inspect element,the page takes straight in that row.
This will show us the HTML elements for Last/First Name & Department.

Thanks for replying. I will do as instructed and report back what I find.

I appreciate it.

Hello again. I did as instructed. I even read up on using the function 'GetElementById' but I can't figure out the difference between id, span, etc.

Thank you very much for your help.


P.S. I tried inserting the html code but I kept on getting a firewall error saying that it is not allowed. So I am
including it as a txt file.