Grabbing HTML tables into Excel

[Wibs]

Hi,

Does anyone know of a utility or some code that allows you to click inside an HTML table, copy the entire table to the clipboard, so you can then paste it into Excel. Or any other similar function?

Regards

Wibs

[Ron Coderre]

Would this work for you?

1)Open a browser and go to the website.
2)Copy the URL (to notepad for safekeeping...just in case)
3)Switch to Excel
4)Data>Import External Data>New Web Query
5)Paste your URL into the Address field in the New Web Query window
6)Click on the section or table of data you want to retrieve
7)Click the [Import] button
8)Select where you want to data to reside...and you're done.

After that...to refresh the data, just:
Right click on the data and select "Refersh Data"

Regards,
Ron

[Wibs]

Would this work for you?

1)Open a browser and go to the website.
2)Copy the URL (to notepad for safekeeping...just in case)
3)Switch to Excel
4)Data>Import External Data>New Web Query
5)Paste your URL into the Address field in the New Web Query window
6)Click on the section or table of data you want to retrieve
7)Click the [Import] button
8)Select where you want to data to reside...and you're done.

After that...to refresh the data, just:
Right click on the data and select "Refersh Data"

Regards,
Ron

Hi Ron,

That does work, many thanks. If the webpage contains many tables though, and you want to select a particular one only, then you must do some editing to get rid of all the junk.

I just wish that it was as easy as copying a graphic off the web, by right-clicking inside the graphic and selecting 'Save', so why not right-clicking inside a table and selecting 'Save Table As...' and choosing 'Excel Spreadsheet'. Is this too difficult for programmers?

Sigh.

Wibs

[Ron Coderre]

Which version of Excel do you use? In Excel 2002, the version I use, I can
select the specific table in the web page to import.

***********
Regards,
Ron


"Wibs" wrote:

>
> Ron Coderre Wrote:
> > Would this work for you?
> >
> > 1)Open a browser and go to the website.
> > 2)Copy the URL (to notepad for safekeeping...just in case)
> > 3)Switch to Excel
> > 4)Data>Import External Data>New Web Query
> > 5)Paste your URL into the Address field in the New Web Query window
> > 6)Click on the section or table of data you want to retrieve
> > 7)Click the [Import] button
> > 8)Select where you want to data to reside...and you're done.
> >
> > After that...to refresh the data, just:
> > Right click on the data and select "Refersh Data"
> >
> > Regards,
> > Ron
> Hi Ron,
>
> That does work, many thanks. If the webpage contains many tables
> though, and you want to select a particular one only, then you must do
> some editing to get rid of all the junk.
>
> I just wish that it was as easy as copying a graphic off the web, by
> right-clicking inside the graphic and selecting 'Save', so why not
> right-clicking inside a table and selecting 'Save Table As...' and
> choosing 'Excel Spreadsheet'. Is this too difficult for programmers?
>
> Sigh.
>
> Wibs
>
>
> --
> Wibs
> ------------------------------------------------------------------------
> Wibs's Profile: http://www.excelforum.com/member.php...o&userid=28922
> View this thread: http://www.excelforum.com/showthread...hreadid=490449
>
>

[Wibs]

I use Excel 2000, and this version also has the following options in the New Web Query:

1. Enter the address for the Web page that contains the data you want.
2. Choose the part of the Web page that contains the data you want. Note that pre-formatted sections are treated as tables.

[ ] The entire page

[*] Only the tables

[ ] One or more specific tables on the page
Enter table name(s) or number(s) separated by commas.

The main problem is knowing what a table's name is in HTML, and what it's number is, especially as section 2 above states "Note that pre-formatted sections are treated as tables."

I selected "Only the tables" which produced lots of junk (and I guess these are the "pre-formatted sections" which have been treated as tables), followed by the table I wanted. I had to clear out the junk, delete some blank columns (why? On the Web pages there are no blank columns in the table), then tried to clear hyperlinks that were within the table, and graphic placeholders. In fact, just about as much work as selecting the table with the cursor, and pasting into Excel. This was the method I had been using up to now, and had the same problems of stripping out embedded hyperlinks, graphics, split or merged cells etc.

If ever there was a utility crying out out to be written...

Regards

Wibs

Which version of Excel do you use? In Excel 2002, the version I use, I can
select the specific table in the web page to import.

***********
Regards,
Ron


"Wibs" wrote:

>
> Ron Coderre Wrote:
> > Would this work for you?
> >
> > 1)Open a browser and go to the website.
> > 2)Copy the URL (to notepad for safekeeping...just in case)
> > 3)Switch to Excel
> > 4)Data>Import External Data>New Web Query
> > 5)Paste your URL into the Address field in the New Web Query window
> > 6)Click on the section or table of data you want to retrieve
> > 7)Click the [Import] button
> > 8)Select where you want to data to reside...and you're done.
> >
> > After that...to refresh the data, just:
> > Right click on the data and select "Refersh Data"
> >
> > Regards,
> > Ron
> Hi Ron,
>
> That does work, many thanks. If the webpage contains many tables
> though, and you want to select a particular one only, then you must do
> some editing to get rid of all the junk.
>




> I just wish that it was as easy as copying a graphic off the web, by
> right-clicking inside the graphic and selecting 'Save', so why not
> right-clicking inside a table and selecting 'Save Table As...' and
> choosing 'Excel Spreadsheet'. Is this too difficult for programmers?
>
> Sigh.
>
> Wibs
>
>
> --
> Wibs
> ------------------------------------------------------------------------
> Wibs's Profile: http://www.excelforum.com/member.php...o&userid=28922
> View this thread: http://www.excelforum.com/showthread...hreadid=490449
>
>