Data extraction

[mbugg4]

Hi All

I would like to know if there is any way of getting data from a list of URLS all in one go

The urls actual pages are all in table form

I need to this to stall all data and not delete the data from the previous URL etc etc

I know how to do this one at a time but obviously this is time consuming using Data extraction from the web

Thanks in advance

Michael

 

[hiker95]

mbugg4,

What version of Excel and Windows are you using?

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[jsotola]

getting data from a list of URLS

please clarify

to stall all data

is this a typo?

 

[mbugg4]

Hi Thanks for the reply this is what the URLS will look like


Excel 2007

	A	B	C	D	E	F	G	H	I	J	K
1	http://members.informracing.net/informfree/new2/results.php?date=31/12/2008&mycourse=UTTOXETER
2	http://members.informracing.net/informfree/new2/results.php?date=31/12/2008&mycourse=WARWICK
3	http://members.informracing.net/informfree/new2/results.php?date=30/12/2008&mycourse=TAUNTON
4	http://members.informracing.net/informfree/new2/results.php?date=29/12/2008&mycourse=MUSSELBURGH
5	http://members.informracing.net/informfree/new2/results.php?date=29/12/2008&mycourse=NEWBURY
6	http://members.informracing.net/informfree/new2/results.php?date=28/12/2008&mycourse=CATTERICK
7	http://members.informracing.net/informfree/new2/results.php?date=28/12/2008&mycourse=LEICESTER
8	http://members.informracing.net/informfree/new2/results.php?date=27/12/2008&mycourse=CHEPSTOW
9	http://members.informracing.net/informfree/new2/results.php?date=27/12/2008&mycourse=KEMPTON
10	http://members.informracing.net/informfree/new2/results.php?date=27/12/2008&mycourse=WETHERBY
11	http://members.informracing.net/informfree/new2/results.php?date=26/12/2008&mycourse=HUNTINGDON
12	http://members.informracing.net/informfree/new2/results.php?date=26/12/2008&mycourse=KEMPTON
13	http://members.informracing.net/informfree/new2/results.php?date=26/12/2008&mycourse=MARKET RASEN
14	http://members.informracing.net/informfree/new2/results.php?date=26/12/2008&mycourse=SEDGEFIELD
15	http://members.informracing.net/informfree/new2/results.php?date=26/12/2008&mycourse=TOWCESTER
16	http://members.informracing.net/informfree/new2/results.php?date=26/12/2008&mycourse=WETHERBY
17	http://members.informracing.net/informfree/new2/results.php?date=26/12/2008&mycourse=WINCANTON
18	http://members.informracing.net/informfree/new2/results.php?date=23/12/2008&mycourse=FONTWELL
19	http://members.informracing.net/informfree/new2/results.php?date=22/12/2008&mycourse=HEREFORD
20	http://members.informracing.net/informfree/new2/results.php?date=21/12/2008&mycourse=CARLISLE
21	http://members.informracing.net/informfree/new2/results.php?date=20/12/2008&mycourse=ASCOT
22	http://members.informracing.net/informfree/new2/results.php?date=20/12/2008&mycourse=HAYDOCK
23	http://members.informracing.net/informfree/new2/results.php?date=20/12/2008&mycourse=NEWCASTLE
24	http://members.informracing.net/informfree/new2/results.php?date=19/12/2008&mycourse=ASCOT
25	http://members.informracing.net/informfree/new2/results.php?date=18/12/2008&mycourse=EXETER
26	http://members.informracing.net/informfree/new2/results.php?date=18/12/2008&mycourse=LUDLOW
27	http://members.informracing.net/informfree/new2/results.php?date=17/12/2008&mycourse=BANGOR-ON-DEE
28	http://members.informracing.net/informfree/new2/results.php?date=17/12/2008&mycourse=NEWBURY
29	http://members.informracing.net/informfree/new2/results.php?date=16/12/2008&mycourse=CATTERICK
30	http://members.informracing.net/informfree/new2/results.php?date=16/12/2008&mycourse=FOLKESTONE
31	http://members.informracing.net/informfree/new2/results.php?date=15/12/2008&mycourse=AYR

<tbody>
</tbody>

Sheet1​

And this is what the data will look like that I am requiring from the URLS

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	26/02/2008
2	20-Feb	LEICESTER RH	5 run												VDW Ratings
3	Race	FROST BOYS BEGINNERS' CHASE (4) 5YO+
4		Winner £5,070.40	2m
5
6	No.	Dr.	Horse	Position	SP	Master	Lto			Avg	M+A	Cse	Dis		Ability	Form
7	1	0	Barati	3	16/1	1	1 31	1 45	68 -	23	24	-	-		0	30
8	2	0	Colemanstown	U	100/1	62	1 27	1 46	14 96	5	67	1	14		0	30
9	3	0	Fandani	1	4-Sep	80	54 91	80 108	45 128	60	140	-	80		35	15
10	4	0	Nikola	2	2-Jan	83	83 29	81 53	74 66	79	162	-	74		63	7
11	5	0	Tiffin Deano	4	100/1	69	69 124	1 139	68 261	46	115	-	-		0	27
12
13
14	Feb-50	LEICESTER RH	16 run												VDW Ratings
15	Race	BATES FAMILY HANDICAP CHASE (0-105) (4) 5YO+
16		Winner £4,554.20	3m
17
18	No.	Dr.	Horse	Position	SP	Master	Lto			Avg	M+A	Cse	Dis		Ability	Form
19	1	0	Sovereign's Joy	F	14/1	77	77 27	66 87	50 -	64	141	77	77		37	13
20	2	0	Marshalls Run	6	2-Nov	78	75 34	78 67	68 -	74	152	-	78		0	15
21	3	0	Sabreflight	-	-	51	51 60	1 325	41 -	31	82	51	-		35	24
22	4	0	Donald Will Do	U	14/1	85	69 13	74 27	73 62	72	157	85	85		0	12
23	5	0	Ice And Soda	P	25/1	77	66 34	77 133	65 142	69	146	-	77		0	13
24	6	0	Ouragan De Prairie	U	2-Nov	83	83 19	75 62	78 95	79	162	-	-		0	14
25	7	0	Dundridge Native	U	4-Nov	77	1 7	65 28	75 67	47	124	-	77		28	16
26	8	0	Ours	7	1-Dec	71	1 52	69 62	71 85	47	118	-	-		0	14
27	9	0	Gerrard	3	33/1	79	1 13	66 55	1 89	23	102	1	-		36	23
28	10	0	Charlie's Double	5	1-Sep	75	67 13	58 49	64 55	63	138	67	75		41	15
29	11	0	Ballyvaden	1	1-Dec	69	1 13	1 55	69 77	24	93	1	1		0	25
30	12	0	Tradingup	9	25/1	78	1 58	61 77	68 89	43	121	-	68		36	25
31	13	0	Parish Oak	P	66/1	80	61 13	1 27	1 62	21	101	62	62		42	30
32	14	0	Jacarado	2	50/1	60	60 13	1 142	1 354	21	81	60	60		36	29
33	15	0	Black Smoke	4	25/1	73	65 13	1 46	1 95	22	95	65	65		34	24
34	16	0	Log On Intersky	8	100/1	71	61 13	35 21	1 49	32	103	71	-		46	25
35
36
37	20-Mar	LEICESTER RH	14 run												VDW Ratings
38	Race	GWEN FROST BEST MUM NOVICES' HANDICAP CHASE (0-90) (5) 5YO+
39		Winner £2,927.70	2m 5f
40
41	No.	Dr.	Horse	Position	SP	Master	Lto			Avg	M+A	Cse	Dis		Ability	Form
42	1	0	Curradoon	B	33/1	71	1 46	1 55	71 76	24	95	71	71		0	25
43	2	0	In Extra Time	-	-	73	1 85	73 133	70 149	48	121	-	73		35	21
44	3	0	Northern Quest	3	1-Apr	76	4 46	75 60	76 70	52	128	-	4		33	8
45	4	0	Bluecoat	F	50/1	77	19 85	46 323	77 336	47	124	-	-		15	20
46	5	0	So Brash	F	1-Mar	79	78 58	62 77	79 105	73	152	-	-		35	13
47	6	0	Madam Harriet	6	16/1	75	53 21	65 32	71 57	63	138	-	71		0	18
48	7	0	Red Ensign	F	14/1	73	1 14	68 36	73 55	47	120	-	68		30	18
49	8	0	Kaparolo	P	100/1	77	7 27	31 32	1 110	13	90	7	-		37	30
50	9	0	Fourpointone	4	1-Dec	71	71 136	74 -	-	73	144	-	-		0	7
51	10	0	William Butler	2	1-Aug	65	1 33	1 68	60 90	21	86	-	-		0	29
52	11	0	Minella Lodge	1	1-Oct	79	1 19	1 55	1 71	1	80	79	1		0	30
53	12	0	Sharbasia	P	50/1	72	60 14	54 34	67 62	60	132	-	72		0	26
54	13	0	Furze Hill	5	1-Aug	63	62 26	63 55	37 82	54	117	37	63		0	16
55	14	0	Kirby's Vic	7	1-Aug	1	1 12	1 -	66 -	23	24	-	-		0	24
56
57
58	Mar-50	LEICESTER RH	7 run												VDW Ratings
59	Race	DAN FROST MEMORIAL NOVICES' CHASE (3) 5YO+
60		Winner £7,515.60	3m
61
62	No.	Dr.	Horse	Position	SP	Master	Lto			Avg	M+A	Cse	Dis		Ability	Form
63	1	0	Cathedral Rock	F	15/8	85	1 19	84 43	77 62	54	139	-	1		42	13
64	2	0	Sheriff Roscoe	2	11-Oct	85	78 49	75 76	68 98	74	159	78	78		42	6
65	3	0	Burren Legend	1	1-May	85	75 15	77 45	85 69	79	164	-	85		29	8
66	4	0	Clockers Corner	5	100/1	1	1 27	1 62	56 -	19	20	1	1		0	26
67	5	0	Desert Tommy	3	33/1	85	61 4	1 10	72 23	45	130	-	85		64	21
68	6	0	Double Intruder	-	-	76	72 32	76 89	48 322	65	141	-	-		0	14
69	7	0	Ellies Horse	4	50/1	80	1 20	80 29	60 59	47	127	-	80		0	25
70
71
72	Apr-50	LEICESTER RH	11 run												VDW Ratings
73	Race	"RIGHT TIME" WATCHES PETERBOROUGH HANDICAP CHASE (0-115) (4) 5YO+
74		Winner £5,204.80	2m 5f
75
76	No.	Dr.	Horse	Position	SP	Master	Lto			Avg	M+A	Cse	Dis		Ability	Form
77	1	0	Copper Bay	-	-	73	73 28	1 79	1 -	25	98	-	-		55	25
78	2	0	Direct Access	8	80/1	61	1 29	61 294	1 -	21	82	-	-		87	26
79	3	0	Zorro De La Vega	5	2-Nov	77	67 27	77 60	73 76	72	149	77	-		52	9
80	4	0	Sole Agent	4	1-Apr	79	77 28	79 68	43 95	66	145	-	-		43	7
81	5	0	Lysander	1	100/30	79	79 27	63 -	86 -	76	155	79	79		28	11
82	6	0	No Complaint	2	1-May	81	81 29	1 45	73 60	52	133	73	-		33	19
83	7	0	Desert Secrets	3	1-Jul	78	78 69	63 90	1 95	47	125	-	-		42	23
84	8	0	Muttley Maguire	-	-	69	69 13	81 -	69 -	73	142	69	69		47	12
85	9	0	Malaga Boy	6	20/1	71	60 28	71 71	62 90	64	135	-	-		48	17
86	10	0	Master Somerville	7	2-Nov	75	64 75	75 103	54 361	64	139	-	75		0	20
87	11	0	Glimmer Of Light	P	33/1	74	1 46	1 68	1 92	1	75	-	74		58	30

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

obviously the next url need to be placed under the last and then carry on

 

[John_w]

Try this:

Sub Get_Web_Data()

    Dim URLs As Range, URL As Range
    Dim destCell As Range
    
    With Sheets("Sheet1")
        Set URLs = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
    End With
    
    For Each URL In URLs
        With Sheets("Sheet2")
            Set destCell = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            With .QueryTables.Add(Connection:="URL;" & URL.Value, Destination:=destCell)
                .Name = "web_query"
                .WebSelectionType = xlEntirePage
                .WebFormatting = xlWebFormattingNone
                .WebDisableDateRecognition = True
                .Refresh False
            End With
            .QueryTables(1).Delete
            DoEvents
        End With
    Next
        
End Sub