Internet & VBA

Resolved · Low Priority · Version 2010

Mon 21 Jan 2013, 10:52 Delegate Alistair said...

Alistair has attended:
Excel VBA Intro Intermediate course
Excel VBA Intermediate course

Internet & VBA

Hello,

This might be just an excel question, but I am assuming that VBA could help this process.

I often need information from the internet which I copy & paste into and excel and then use some VBA macros to re-jig the information.

Is there a way I could automate the copy & paste part from the internet?

The reason why I would like this is because on the specific website, there is often between 100-200 links to other web pages, and I need to go into each one; copy & paste, then back back to the main page.

This one has 186 races:

http://88.85.4.10/results_455.html

I start from race 1, copy & paste, back to the main page, open race 2 and repeat.

Thanks

Mon 21 Jan 2013, 11:36 Trainer Gary said...

RE: Internet & VBA

Hi Alistair

Thanks for getting in touch. You're absolutely right to get VBA involved for the re-jigging, but the copy and paste part is a little more tricky. There probably is a solution that loops through available tables on the page but I have a suspicion that the time involved to develop the solution would eclipse the copying and pasting. it probably involves another language such as VB.NET as well to work efficiently.

You could explore retrieving the data in a different way: via a web query. Go to the Data tab and select From Web. A window will pop up allowing you to enter the URL that you're trying to download from.

When the page appears it will be littered with yellow arrowed boxes. Click each one that denotes the table of data you need, and when you click Import at the bottom it will pull them all back into the current sheet.

It's not a perfect automated system but it will grab the data into one location that you can then run your macros from.

I hope this helps.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector