How to Scrape information from a website and put in specific column of existing worksheet.

Hi Everyone,

I have been working on something and my boss has thrown a complete curve ball at me that has left me totally stumped. Im pretty new to using VBA and am learning every day but this is just a step too far for me. Really hope someone can help me out.

Basically what i am trying to do is scrape the "Heading" information from this website

http://ted.europa.eu/udl?uri=TED:NOT...N:HTML&tabId=3

and put it into column D on my "Tender_Review" worksheet.

This is the HTML that i can find by right clicking on the page and searching that page for "Heading"

HTML Code: 

This is an extract from my current excel sheet.ExcelInfoPic.JPG

I know everything will need to be shifted along but that's easy enough. A major problem i can see is the fact that each Tender has a code so every link to the website and specific page is different. Also the information in the heading "field" on the website will change each time.

I am totally at a loss as to where to even start with this problem. But at the same time if it can't be done then i would also be grateful if someone could tell me this too.

Thanks in advance for any help at all that is forthcoming,

Best Regards,

It is difficult to see the out put you want in a text format. I do not know which part of the heading you want to extract. I assumed the entire row. If this is not what you want, you need to attach a sample showing clearly the out put in excel sheet, not a picture.

Scrape information from a websitexlsm.xlsm


Hi, Apologies i wasn't clear, i have attached an updated version of your excel showing the layout exactly as i would like it to be.

Thanks so much for your help!!!

I do not know where you are getting the columns from. I have made some assumption on some headings. All the data are imported in to row 3.
I have also attached the entire table if I were to import it. You need to show me which columns and data from the table you want to include in your heading. For instance, I can not see where the job title is on the table? I thought you want to get data from a table, but I may have misread your request.

Glove,
I thought you want to extract data from a table, but this does not appear to be the case.

"P.s. Looking at the code provided. This is the only required table detail"

Unless you have different URLs, there is a single table on the attached URL. Infact you can use Excel's import function to copy the table in to excel and then cut and slice the data you want.
I only chip in to answer the thread, that is, to get the data from a table but this requires massive coding and time which unfortunately I do not have right now

Hi AB33,

Thanks for looking over everything.

Is there no way that i can have the URL something like: URL = "http://ted.europa.eu/udl?uri=TED:NOTICE:"*column A data":DATA:EN:HTML&tabId=3" to allow it too look at the table number on the workbook? or possibly column (D) = (I) + "&tabId=3"

From there i could import the table row (7)alone and delete the unwanted data using the code you have provided already? using VBA as i have done before (not sure if you looked over my code)

I'm just guessing/ clutching at straws here, but i do understand that it would be a lot of work. If a guru is saying that then i'm hoping my boss will be somewhat understanding.

Thanks again for all of your assistance,

Regards,

Hi AB33,

Further to my last post i edited your code and it gives me the data i need, its as follows:

Please Login or Register  to view this content.

The only issue now is the URL. http://ted.europa.eu/udl?uri=TED:NOTICE:365450-2015:DATA:EN:HTML&tabId=3

The BOLD text would need to change for each row. What i was asking is would there be a way to have that part of the URL look at column A? and then use that as part of the URL therefore adding the data from that table row for that specific tender.

I'm sorry i haven't been clear on my request.

Thanks again for any further help you can give.

Regards,

Your explanation is much to be desired, but since you are new to the forum, I will give you the benefit of the doubt.
I managed to get all the data, but the website banned me for 24 hours as I made lots of request. When I run the code for the first time, I got all the data. When I tried to do a second test, I am banned. I have got the following line.

"You have made too many requests within a given time-frame, and you will be excluded from using for 24 hours."

I will try it in the morning. In the mean time, run it and see what error will you get.

I have just run the code and seems to work, except for submission date- that is when there is no date.

Hi AB,

I think we have crossed wires again.

The original code you supplied almost did what i needed. I was messing around yesterday and i have almost got what i need. The code below scrapes the "Heading" Information i need from the website based on the Tender code seen on the workbook i sent named Weekly_Tender_Populated.

Please Login or Register  to view this content.

I am trying get it to work so that the full range of Tender Codes can be taken into account and the detail from the site placed in column D in the row appropriate to its tender code. You can see my attempt in the commented code above but i cant get it to work. I think i am close but no cigar as yet.

Regards,

I am now completely lost. I have spent over two days and I am not nearer to understand your request. Did you try my last code?

Hi,

Yes i tried your code and it was bringing out more information than i needed. So i edited it to the following code.

Please Login or Register  to view this content.

Before the code above is run the sheet looks like the image below. All other data on the worksheet already exists at the point we start bringing in the info from TED.

PreCodeRun.JPG

After the code has been Run the sheet looks like the image below. Notice that the number in column A (Tender Code) is also in the link to the website. So whatever is in column A, the code will use that as part of the link to find the appropriate table for that specific tender.

PostCodeRun.JPG

If i change the above code to look at say cells A10 and populate D10 with the information from the website the workbook would look like the following image.

PostCodeChange10.JPG

I need the code to look at each cell in column A and populate the corresponding cell in column D with the information from TED Table.Rows(7).Cells(2) no matter what is in Col A and no matter how many tender codes are in column a. I have looked into UsedRange, Last Row and i cant quite get it. Id like to not have to enter 100 versions of the above code with A2-A100 and D2-D100. Id like it to find how many rows there are automatically.

Im really sorry this has put you out, i feel bad for dragging you into my noob world. I hope i have been clearer above. I thought sending the workbooks might help, hopefully the above images will clear things up.

Apologies again for being a PITA,

G

Hi AB33,

I have managed to figure it out. Code as follows. Thanks so much for pointing me in the direction. Is there a way that i can give feedback and mark the issue as solved?

Please Login or Register  to view this content.

Okay! Glad it has been resolved.
At the top right hand of this page, there are menus and options. You can click from the drop down and mark it as solved

Me too!

No problem, Thanks again for your help.