Pull Delivery Date from website and place in worksheet using VBA

Hi All,

I have had a good look around this and various other forums which has got me to the current point and would like it if someone could assist me to progress further on this project.

I need to create a VBA script to help monitor the delivery times of goods we send out on courier. Basically at the end of each month we get a list of all the consignments we sent out (connote number, send date, weight, number of cartons, etc). This courier company has the ability to look up the delivery time on their website however to look up each connote manually is a time consuming process.

Now on the website we need to enter the connote number into a search box and press submit. This then loads up information on the shipment including shipping date, destination depot, delivery date etc. The only information I need to pull from the website is the date it was delivered.

Now I am only a small way into this but have an issue where when I press (or it is simulated in the code) the submit button the resulting page opens in a new window (but only the first time the search button is pressed). Resulting times in the new new window this appears to not happen) Therefore the exporting of the page data exports the original page instead of the new page which has the delivery information.

So firstly I either need to know how I can disable the function which is causing it to open in a new window or alternatively find a way to reference the new instance of Internet Explorer.

Once this is done my next real issue is finding a way to reference the last scan date where it states item delivered. Note sometimes the delivery will have multiple scannings done so need to reference the first line where delivery is confirmed. Suspect this may be quite difficult and if so can reference just the first record though this will induce a small margin of error in my reporting that is not ideal.

Lastly how can this date be copied and pasted into a cell in the excel spreadsheet. Which is a matter of parsing the data. Not too sure on how to do this but should hopefully be able to work this part out myself if need be.

Then I just need to make it scroll through all the records in a column looking for this information. I should be right to sort this part out.

Please Login or Register  to view this content.

A connote you can try if required to see layout etc. of the page is HUN81000. Note this is not a connote which relates to my company but the formatting etc. is exactly the same. Cell A1 in my sample sheet is the first connote number.

Any help people can offer for any of the above queries would be appreciated.

If you don't know, to refer to a cell you can use Range or Cells(row,column), for example it's the same use Range("a5") or cells(5,1) o cells(5,"a").
Try with this macro, on my pc it works fine:

Please Login or Register  to view this content.

Regards,
Antonio

Thanks Antonio that has worked a treat. Didn't expect you to basically finish the process for me. Thanks for your awesome help. Your a legend.

Now have the system working perfectly for the first courier. Now found out I need to set it up for another courier company. I have adapted the above code and have it inputting the connote number and loading the page with the data (this one doen't open a new window like the other couriers webpage did). My problem comes with how to reference the delivery date in this site and pull that data into column B next to the connote. I am unable to follow the logic behind the table code from the first courier to adapt it to this new one and haven't been able to find much assistance on the web which guides you through what it all means. So my issue is with the bottom 20 odd lines of code (from "With IE.Document.all down".

Sample connotes are as follows 02294810 through to 02294814.

Any assistance is appreciated.

Please Login or Register  to view this content.