Copying data from website into excel

[terry87]

I want to copy data from a website into excel. The data is in table format on the website but when I copy to excel the data is copied into a single column rather than in the same table format as the website. This means I have to to copy and transpose the copied cells into the correct cells. This I could do in a macro but I would have to do this up to 100 times and a for next loop doesn't help because the data is not necessarily in the same place for eah copy and paste transpose.

I have tried using IE11, Chrome and FireFox with same result.

It was working fines until the website was recently redesigned and the change has resulted in the change in the pasting format.


terry8

 

[AFPathfinder]

Is it a public website that we can look at to test?

 

[terry87]

yes it is...the data is for my personal use and not shared with anyone.

https://www.punters.com.au/odds-comparison/horse-racing/ascot/2017-12-09/?raceNumber=all

The page shows the current odds for throughbred racing in Perth West Australia for all races at Ascot. I want to copy the tables into Excel and then calculate the average odds for each horse. Before the site was redesigned after copying the data (Paste and Match Destination Formatting), I used the average function placed in the cell after the last datum for each horse. I then used a Vlookup to get the average onto my main sheet which has a heap of indicators for each horse. I can then compere the relative indicators for each horse.

terry87

 

[AFPathfinder]

On the browser, it's not an html table and I think that is why the xml conversion isn't locating the row tags. The only success I was having is when I print the webpage as a PDF and copy only the numbers from the table on the PDF, then it would paste as columns and rows. As soon as I started including the names, the copy/paste was messing up again.

 

[terry87]

Hi AFPathfinder,

That will work if I copy the names of the horses first. I then select the numbers and convert them to PDF. Select the numbers from the PDF ans paste them next to the names. But I would need to do that for 5 race meetings and 8-9 races in each meeting .... up to 45 times. Not practical but its closer than I've got to before. So I will play around with that idea and extrapolate. Also might paste into OneNote and see what happens. If successful I will post here.

Thanks for your time and effort
terry87