Problem importing data from webpage

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Noel S Pamfree - 03 Sep 2005 11:07 GMT

I am trying to import some data from a website where the data 'pops up' in a
window and does not give you the web page address, so I can't use Data /
Import External Data / New Web Query to obtain it.

I tried a simple copy and paste but all I get is an annoying bitmap icon in
the top left of my screen.

How can it change this into the data I want?

Thanks,

Noel

Reply to this Message

Dodo - 03 Sep 2005 22:38 GMT

> I am trying to import some data from a website where the data 'pops
> up' in a window and does not give you the web page address, so I can't
> use Data / Import External Data / New Web Query to obtain it.
>
> I tried a simple copy and paste but all I get is an annoying bitmap
> icon in the top left of my screen.

Simple copy and paste? Depends on how you do this!

Usually you can mark (part of) the contents of a web page by dragging the
cursor over the data. If you then do Ctl-C and a Ctrl-V in the Excel sheet,
the data are pasted in consecutive rows.

Can you give me the link to the data you are trying to copy?

Signature

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

Reply to this Message

Noel S Pamfree - 04 Sep 2005 09:13 GMT

Yes - that is what I tried.

Here is the link, I want the data under the heading GCSE Results 2005 click
on: Top Schools at GCSE (All)

http://www.timesonline.co.uk/section/0,,591,00.html

Thanks

Noel

>> I am trying to import some data from a website where the data 'pops
>> up' in a window and does not give you the web page address, so I can't
[quoted text clipped - 11 lines]
>
> Can you give me the link to the data you are trying to copy?

Reply to this Message

Don Guillett - 04 Sep 2005 12:58 GMT

with xp home xl2002 all I had to do was right click in the field and select
import to excel.
Painting and then copy/paste also worked well.
Of course, you could also have a macro to import so you wouldn't have to
manually goto the site.

Signature

Don Guillett
SalesAid Software
donaldb@281.com

> Yes - that is what I tried.
>
[quoted text clipped - 22 lines]
> >
> > Can you give me the link to the data you are trying to copy?

Reply to this Message

Noel S Pamfree - 04 Sep 2005 13:47 GMT

Thanks Don for your help.

May I ask you one more thing?

How did you work out the address of the pop-up webpage? - if I could have
done that I need not have troubled you!

Many thanks,

Noel

> with xp home xl2002 all I had to do was right click in the field and
> select
[quoted text clipped - 31 lines]
>> >
>> > Can you give me the link to the data you are trying to copy?

Reply to this Message

Noel S Pamfree - 04 Sep 2005 19:40 GMT

I just discovered that if I had checked the Properties of the page I would
have found the web address of the page!

Thanks again,

Noel

> Thanks Don for your help.
>
[quoted text clipped - 43 lines]
>>> >
>>> > Can you give me the link to the data you are trying to copy?

Reply to this Message

Don Guillett - 04 Sep 2005 13:21 GMT

try this macro
Sub getit() 'name a sheet Data
'use this once and then refresh
myurl = "http://www.timesonline.co.uk/displayPopup/0,,67194,00.html"
With ActiveWorkbook.Worksheets("Data").QueryTables.Add( _
Connection:="URL;" & myurl, Destination:=Worksheets("Data").Cells(1,
1))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

Signature

Don Guillett
SalesAid Software
donaldb@281.com

> Yes - that is what I tried.
>
[quoted text clipped - 22 lines]
> >
> > Can you give me the link to the data you are trying to copy?

Reply to this Message

Noel S Pamfree - 05 Sep 2005 10:42 GMT

Hi Don,

Although I have now managed to get the info thanks to Dodo I would really
like to get your macro working. I have very little experience of these in
Excel.

If you have time could you explain the REM statement in your macro ( i.e.
Sub getit() 'name a sheet Data 'use this once and then refresh)

Thanks for your patience.

Noel

> try this macro
> Sub getit() 'name a sheet Data
[quoted text clipped - 38 lines]
>> >
>> > Can you give me the link to the data you are trying to copy?

Reply to this Message

Don Guillett - 05 Sep 2005 13:42 GMT

I thought it was pretty clear. To use the macro, as written, place in a
module>rename your sheet1 or whatever to Data.... or change my sheet
reference. Then execute the macro. Then if you need the data again just
place cursor in cell a1>right click>refresh data to update from the web
page.

As you will see the macro will automatically goto the web page and get the
data. All you have to do is have your internet connection active.

Signature

Don Guillett
SalesAid Software
donaldb@281.com

> Hi Don,
>
[quoted text clipped - 51 lines]
> >> >
> >> > Can you give me the link to the data you are trying to copy?

Reply to this Message

Dodo - 04 Sep 2005 16:13 GMT

> Yes - that is what I tried.
>
> Here is the link, I want the data under the heading GCSE Results 2005
> click on: Top Schools at GCSE (All)
>
> http://www.timesonline.co.uk/section/0,,591,00.html

I saw you got a different solution from somebody, but are you still
interested in why the simple way (mark text, Ctrl-C and Ctrl-V) didn't do
it?

I get all data perfectly in columns and rows into my spreadsheet by
following that method.

What browser are you using?

Hmm, I just found something goes wrong when you select the data without
including the header "Top schools at GCSE - The Times ranking of combined
independent and state schools".

Apparently the table makeup is missing that way.

Signature

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

Reply to this Message

Noel S Pamfree - 04 Sep 2005 19:39 GMT

Hi Dodo,

First of all thanks for replying.

When Don sent me the page address I had no trouble downloading the data
using Data / Import External Data / New Web Query etc.

However since reading your reply I have tried several things. I can get the
info fine into Word using cut and paste but when I use Excel (where I really
need it in order to manipulate and sort the data) I get a little TextBridge
icon in the top left corner.

Now I usually use Firefox but I tried Internet Explorer and found that if I
include the heading (Which I did not do the first time) I get the data ok;
but not if I start with the first school - all data seems to get entered
into cell A1!

It looks like the problem is probably caused by Firefox but why IE won't let
you import the data without the title without putting it all in one cell I
don't know.

Thanks again,

Noel

>> Yes - that is what I tried.
>>
[quoted text clipped - 17 lines]
>
> Apparently the table makeup is missing that way.

Reply to this Message

Dodo - 04 Sep 2005 21:26 GMT

> However since reading your reply I have tried several things. I can
> get the info fine into Word using cut and paste but when I use Excel
[quoted text clipped - 9 lines]
> won't let you import the data without the title without putting it all
> in one cell I don't know.

If you are in FireFox on the popup screen, rightclick somewhere in the page
and select: View Page Info

You'll see it has actually been made with Excel 10 (2003 or XP?).

I have no problem getting the data into Excel (2000) by selecting all
including the title. Might be a setting somewhere in FF. I cannot reproduce
what you're describing.

It only works OK if I use Ctrl-C to copy the data from the web page (so not
through the rightclick menu/Copy)!

Signature

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

Reply to this Message

Don Guillett - 05 Sep 2005 13:53 GMT

With my xphome,xl2002,ie6 all I had to do to do the exact same thing was
right click in the field and select import to excel. A lot less hassle. Try
it

Signature

Don Guillett
SalesAid Software
donaldb@281.com

> Hi Dodo,
>
[quoted text clipped - 42 lines]
> >
> > Apparently the table makeup is missing that way.

Reply to this Message