Importing Non-Table Data

E

ercedwards

Board Regular
Joined
Apr 27, 2013
Messages
125
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]
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
do you want this or something more?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#A5A5A5]Prime Street[/td][td=bgcolor:#A5A5A5]Cross Street[/td][td=bgcolor:#A5A5A5]Dispatch Time[/td][td=bgcolor:#A5A5A5]Incident Number[/td][td=bgcolor:#A5A5A5]Incident Type[/td][td=bgcolor:#A5A5A5]Alarm Level[/td][td=bgcolor:#A5A5A5]Area[/td][td=bgcolor:#A5A5A5]Dispatched Units[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#EDEDED][/td][td=bgcolor:#EDEDED]/[/td][td=bgcolor:#EDEDED]
02/03/2019 11:14​
[/td][td=bgcolor:#EDEDED]F19023310[/td][td=bgcolor:#EDEDED]Vehicle - Personal Injury Highway[/td][td=bgcolor:#EDEDED]
0​
[/td][td=bgcolor:#EDEDED]
441​
[/td][td=bgcolor:#EDEDED]R441, A415[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BRIDGELAND AVE, NY[/td][td]DUFFERIN ST / CALEDONIA RD[/td][td]
02/03/2019 11:39​
[/td][td]F19023318[/td][td]Vehicle - Personal Injury[/td][td]
0​
[/td][td]
145​
[/td][td]P145[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#EDEDED]M9P[/td][td=bgcolor:#EDEDED][/td][td=bgcolor:#EDEDED]
02/03/2019 11:45​
[/td][td=bgcolor:#EDEDED]F19023319[/td][td=bgcolor:#EDEDED]MEDICAL[/td][td=bgcolor:#EDEDED]
0​
[/td][td=bgcolor:#EDEDED]
443​
[/td][td=bgcolor:#EDEDED]P443[/td][/tr]

[tr=bgcolor:#FFFFFF][td]M4K[/td][td][/td][td]
02/03/2019 11:45​
[/td][td]F19023320[/td][td]MEDICAL[/td][td]
0​
[/td][td]
322​
[/td][td]P322[/td][/tr]
[/table]
 
Upvote 0
after refresh I got this:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Prime Street[/td][td=bgcolor:#70AD47]Cross Street[/td][td=bgcolor:#70AD47]Dispatch Time[/td][td=bgcolor:#70AD47]Incident Number[/td][td=bgcolor:#70AD47]Incident Type[/td][td=bgcolor:#70AD47]Alarm Level[/td][td=bgcolor:#70AD47]Area[/td][td=bgcolor:#70AD47]Dispatched Units[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]BRIDGELAND AVE, NY[/td][td=bgcolor:#E2EFDA]DUFFERIN ST / CALEDONIA RD[/td][td=bgcolor:#E2EFDA]
02/03/2019 11:39​
[/td][td=bgcolor:#E2EFDA]F19023318[/td][td=bgcolor:#E2EFDA]Vehicle - Personal Injury[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
145​
[/td][td=bgcolor:#E2EFDA]P145[/td][/tr]

[tr=bgcolor:#FFFFFF][td]M1B[/td][td][/td][td]
02/03/2019 11:57​
[/td][td]F19023323[/td][td]MEDICAL[/td][td]
0​
[/td][td]
214​
[/td][td]A213[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]M5V[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
02/03/2019 12:05​
[/td][td=bgcolor:#E2EFDA]F19023324[/td][td=bgcolor:#E2EFDA]MEDICAL[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
334​
[/td][td=bgcolor:#E2EFDA]P332[/td][/tr]

[tr=bgcolor:#FFFFFF][td]M2M[/td][td][/td][td]
02/03/2019 12:06​
[/td][td]F19023325[/td][td]MEDICAL[/td][td]
0​
[/td][td]
111​
[/td][td]P111[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]REXDALE BLVD / HUMBERWOOD BLVD[/td][td=bgcolor:#E2EFDA]
02/03/2019 12:07​
[/td][td=bgcolor:#E2EFDA]F19023326[/td][td=bgcolor:#E2EFDA]Vehicle - Personal Injury[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
412​
[/td][td=bgcolor:#E2EFDA]R412[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CHEROKEE BLVD, NY[/td][td]NAVAHO DR / APACHE TRL[/td][td]
02/03/2019 12:10​
[/td][td]F19023327[/td][td]Fire - Residential[/td][td]
0​
[/td][td]
113​
[/td][td]A113, HZ145, P234, C11, P116, R115, R241[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]M4X[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
02/03/2019 12:10​
[/td][td=bgcolor:#E2EFDA]F19023328[/td][td=bgcolor:#E2EFDA]MEDICAL[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
313​
[/td][td=bgcolor:#E2EFDA]P313[/td][/tr]
[/table]
 
Upvote 0
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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Prime Street[/td][td=bgcolor:#70AD47]Cross Street[/td][td=bgcolor:#70AD47]Dispatch Time[/td][td=bgcolor:#70AD47]Incident Number[/td][td=bgcolor:#70AD47]Incident Type[/td][td=bgcolor:#70AD47]Alarm Level[/td][td=bgcolor:#70AD47]Area[/td][td=bgcolor:#70AD47]Dispatched Units[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]DANFORTH AVE, TT[/td][td=bgcolor:#E2EFDA]PLAYTER BLVD / BOWDEN ST[/td][td=bgcolor:#E2EFDA]
02/03/2019 12:46​
[/td][td=bgcolor:#E2EFDA]F19023340[/td][td=bgcolor:#E2EFDA]Fire - Commercial/Industrial[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
313​
[/td][td=bgcolor:#E2EFDA]S313, P313, A322, C32, P322, P324[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DUNDAS ST W / DUFFERIN ST[/td][td]
02/03/2019 13:03​
[/td][td]F19023348[/td][td]Check Call[/td][td]
0​
[/td][td]
426​
[/td][td]P426, A345, C34[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]/[/td][td=bgcolor:#E2EFDA]
02/03/2019 13:04​
[/td][td=bgcolor:#E2EFDA]F19023351[/td][td=bgcolor:#E2EFDA]Vehicle - Personal Injury Highway[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
445​
[/td][td=bgcolor:#E2EFDA]R444, P443, P433[/td][/tr]

[tr=bgcolor:#FFFFFF][td]M6L[/td][td][/td][td]
02/03/2019 13:31​
[/td][td]F19023360[/td][td]MEDICAL[/td][td]
0​
[/td][td]
146​
[/td][td]P146[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]SPADINA RD, TT[/td][td=bgcolor:#E2EFDA]LOWTHER AVE / KENDAL AVE[/td][td=bgcolor:#E2EFDA]
02/03/2019 13:33​
[/td][td=bgcolor:#E2EFDA]F19023361[/td][td=bgcolor:#E2EFDA]Fire - Highrise Residential[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
344​
[/td][td=bgcolor:#E2EFDA]P344, P315, A315, C31, P312, A315[/td][/tr]

[tr=bgcolor:#FFFFFF][td]M6M[/td][td][/td][td]
02/03/2019 13:35​
[/td][td]F19023362[/td][td]MEDICAL[/td][td]
0​
[/td][td]
421​
[/td][td]R421[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]M6S[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
02/03/2019 13:39​
[/td][td=bgcolor:#E2EFDA]F19023364[/td][td=bgcolor:#E2EFDA]MEDICAL[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
422​
[/td][td=bgcolor:#E2EFDA]P422[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BRIAR HILL AVE, TT[/td][td]BATHURST ST / MANITOU BLVD[/td][td]
02/03/2019 13:40​
[/td][td]F19023365[/td][td]Alarm Highrise Residential[/td][td]
0​
[/td][td]
135​
[/td][td]P132, R341, A135, P131, C31[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]ALTON TOWERS CRCL, SC[/td][td=bgcolor:#E2EFDA]BATTINGER GT / PENMARRIC PL[/td][td=bgcolor:#E2EFDA]
02/03/2019 13:46​
[/td][td=bgcolor:#E2EFDA]F19023366[/td][td=bgcolor:#E2EFDA]Alarm Commercial/Industrial[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
242​
[/td][td=bgcolor:#E2EFDA]P242, P211, A244, C24, R241[/td][/tr]
[/table]
 
Upvote 0
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.
​
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
355
Jagadeshrao
J
T
  • Question Question
Aggregation tables do not include real-time data
Replies
0
Views
305
TamirBasin
T
J
  • Solved
Vote for Power Query Enhancement Request - Import Zip Files
Replies
3
Views
639
jdellasala
J
E
  • Question Question
Getting Data From a web site into Excel
Replies
2
Views
523
ercedwards
E
T
  • Question Question
Excel import JSON file by Get Data, Get & Transform (Power Query) not showing data
Replies
1
Views
4K
Mark RS
M

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top