17 January, 2007

Load Web Data into Excel

Did you know that you can easily load data from a website into Excel? You can even do it with a Macro for sites you visit often. For example if you were tracking your investments or you were monitoring the stats for fantasy basketball. Collecting the data is 7 easy steps.

  1. Select Data from the menu
  2. Select Import External Data
  3. Select New Web Query...



  4. Enter the URL of the page that contains the data to import
  5. Excel will recognize tables. Select the table(s) that contains the data you want to import
  6. Select the Import button
  7. Confirm the first cell of where the data should be inserted


That's all there is to it. If you want to create a Macro, use the Macro Recorder (Tools menu) and repeat the steps. You can then add additional code if you want to import different sites into different sheets.
Happy data collecting!

6 comments:

Anonymous said...

Is there a way to pick outlook calendar and put it into a web application?

Chris Todd said...

Check out this website: http://www.slipstick.com/calendar/olpubcal.htm. this would allow you to export your calendar to HTML. If you wanted something interactive, you would need to interface with Microsoft's Exchange Server OWA (Outlook Web Access). There are also 3rd-party providers such as OutShare (http://outshare.4team.biz/Default.asp) that may be able to assist.

info said...

Good article, thanks. But if it's possible to load data from page wich requires login?

CHRISdotTODD said...

I'm not 100% certain... it may vary by login type and/or Excel version. Try the procedure I suggest, and see if you can login first. If that doesn't work, try first logging into the site before going to Excel. I suspect one of these two ways will work in most cases.

lordyoyo said...

Hi!
I know about Excel's ability to import tables from websites, what I'd like to know if it is possible to import data that are not in table format on the site. So for example if ther's a web game with research data layed out in 2 by 7 format, but not in table. Can this somehow be imported in excel and if yes, how?

CHRISdotTODD said...

Have you tried just copy-n-paste? Sometimes that will work with one browser and not another, so it's worth trying with IE and Firefox. If that fails, give us the URL of where we can access the data, and perhaps someone can find a solution for you.