Problem importing data from webpage

N

Noel S Pamfree

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
 
D

Dodo

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.
Click to expand...

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?
 
D

Don Guillett

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.

--
Don Guillett
SalesAid Software
[email protected]
Noel S Pamfree said:
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
Click to expand...
 
D

Don Guillett

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


--
Don Guillett
SalesAid Software
[email protected]
Noel S Pamfree said:
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
Click to expand...
 
N

Noel S Pamfree

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


Don Guillett said:
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.
Click to expand...
 
D

Dodo

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
Click to expand...

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.
 
N

Noel S Pamfree

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
 
N

Noel S Pamfree

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
 
D

Dodo

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.
Click to expand...

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)!
 
N

Noel S Pamfree

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

Don Guillett said:
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
Click to expand...
 
D

Don Guillett

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.
 
D

Don Guillett

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

--
Don Guillett
SalesAid Software
[email protected]
Noel S Pamfree said:
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
Click to expand...
 
Top