Importing Non-Table Data

[ercedwards]

I use a web site to monitor when my son gets dispatched on a fire call

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.toronto.ca/community-people/public-safety-alerts/alerts-notifications/toronto-fire-active-incidents/

I have a macro that works fine on my Windows 8 computer but not on Windows 10 and no one in the world seems to have an answer as to why although lots of people have the same issue. The infamous 8150002e run-time error.

The real data I want from this web site is NOT in a table so using Web Query is not an option. The real data is <td> and <tr> objects on the web page.

Does anyone know of a way to grab this data in VBA and drop it into Excel?

Thanks
[/FONT]

 

[sandy666]

do you want this or something more?

Prime Street	Cross Street	Dispatch Time	Incident Number	Incident Type	Alarm Level	Area	Dispatched Units
	/	02/03/2019 11:14​	F19023310	Vehicle - Personal Injury Highway	0​	441​	R441, A415
BRIDGELAND AVE, NY	DUFFERIN ST / CALEDONIA RD	02/03/2019 11:39​	F19023318	Vehicle - Personal Injury	0​	145​	P145
M9P		02/03/2019 11:45​	F19023319	MEDICAL	0​	443​	P443
M4K		02/03/2019 11:45​	F19023320	MEDICAL	0​	322​	P322

 

[sandy666]

after refresh I got this:

Prime Street	Cross Street	Dispatch Time	Incident Number	Incident Type	Alarm Level	Area	Dispatched Units
BRIDGELAND AVE, NY	DUFFERIN ST / CALEDONIA RD	02/03/2019 11:39​	F19023318	Vehicle - Personal Injury	0​	145​	P145
M1B		02/03/2019 11:57​	F19023323	MEDICAL	0​	214​	A213
M5V		02/03/2019 12:05​	F19023324	MEDICAL	0​	334​	P332
M2M		02/03/2019 12:06​	F19023325	MEDICAL	0​	111​	P111
	REXDALE BLVD / HUMBERWOOD BLVD	02/03/2019 12:07​	F19023326	Vehicle - Personal Injury	0​	412​	R412
CHEROKEE BLVD, NY	NAVAHO DR / APACHE TRL	02/03/2019 12:10​	F19023327	Fire - Residential	0​	113​	A113, HZ145, P234, C11, P116, R115, R241
M4X		02/03/2019 12:10​	F19023328	MEDICAL	0​	313​	P313

 

[ercedwards]

That is exactly what I want, thanks

 

[ercedwards]

The site refreshes every 5 minutes to show new dispatches.

 

[sandy666]

so,
you can use Power Query (Excel 2010/2013 add-in, Excel 2016 and above - built in as Get&Transform)

then you can set refresh connection every 5 minutes

Prime Street	Cross Street	Dispatch Time	Incident Number	Incident Type	Alarm Level	Area	Dispatched Units
DANFORTH AVE, TT	PLAYTER BLVD / BOWDEN ST	02/03/2019 12:46​	F19023340	Fire - Commercial/Industrial	0​	313​	S313, P313, A322, C32, P322, P324
	DUNDAS ST W / DUFFERIN ST	02/03/2019 13:03​	F19023348	Check Call	0​	426​	P426, A345, C34
	/	02/03/2019 13:04​	F19023351	Vehicle - Personal Injury Highway	0​	445​	R444, P443, P433
M6L		02/03/2019 13:31​	F19023360	MEDICAL	0​	146​	P146
SPADINA RD, TT	LOWTHER AVE / KENDAL AVE	02/03/2019 13:33​	F19023361	Fire - Highrise Residential	0​	344​	P344, P315, A315, C31, P312, A315
M6M		02/03/2019 13:35​	F19023362	MEDICAL	0​	421​	R421
M6S		02/03/2019 13:39​	F19023364	MEDICAL	0​	422​	P422
BRIAR HILL AVE, TT	BATHURST ST / MANITOU BLVD	02/03/2019 13:40​	F19023365	Alarm Highrise Residential	0​	135​	P132, R341, A135, P131, C31
ALTON TOWERS CRCL, SC	BATTINGER GT / PENMARRIC PL	02/03/2019 13:46​	F19023366	Alarm Commercial/Industrial	0​	242​	P242, P211, A244, C24, R241

 

[ercedwards]

Unfortunately I don't have the higher level of Excel for Power Shell.

The good news is however that less than 2 minutes ago I tried something bizarre in terms of fixing the

infamous 8150002e run-time error.

That bizarre move seem to have my other VBA working again.

Thanks for all your help.
​

 

[sandy666]

here is example file

refresh interval is set to 3 minutes, you can change it in connection properties

edit:

btw. this is NOT Power Shell but Power Query (Get&Transform)

anyway I am glad that you solved the problem

 

[ercedwards]

I'd love to see how your solution works but I only have Office 2010.

Thanks again

 

[sandy666]

so you can download PowerQuery add-in from MS site (it's free), install it and open the file from the post

Power Query add-in for Excel - choose correct version (32 or 64 bit)

don't care about version warning , just Close