Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

npolly0212

macrumors 65816
Original poster
Sep 21, 2015
1,374
463
I am doing some homework in a class that I have to insert tables/data and whatnot from a web query into an excel worksheet.

I was wondering if there was anyway to go about doing this on a macbook?


In the Departures sheet, in cell A1, create a Web query to the departures table on this Web page: http://media.pearsoncmg.com/ph/bp/bp_myitlab/departure_schedule_files/departure_schedule.htm. Maintain full HTML formatting before importing the table.

Hint : On the DATA tab, in the Get External data group, click From Web. To specify the formatting, in the New Web Query dialog box, click Options.


I was told it cannot be done on a macbook by some other kids in class. I have researched and tried the ways up inserting the URL into a file and saving it and trying to do it from a text file, but it will not work sadly.
 
I am doing some homework in a class that I have to insert tables/data and whatnot from a web query into an excel worksheet.

I was wondering if there was anyway to go about doing this on a macbook?


In the Departures sheet, in cell A1, create a Web query to the departures table on this Web page: http://media.pearsoncmg.com/ph/bp/bp_myitlab/departure_schedule_files/departure_schedule.htm. Maintain full HTML formatting before importing the table.

Hint : On the DATA tab, in the Get External data group, click From Web. To specify the formatting, in the New Web Query dialog box, click Options.


I was told it cannot be done on a macbook by some other kids in class. I have researched and tried the ways up inserting the URL into a file and saving it and trying to do it from a text file, but it will not work sadly.

If you look at the page source, that data is actually being pulled in from a separate page and loaded in a frame. My guess is that Excel doesn't load the frames when it fetches the HTML. Either that or there's some Javascript involved.

Either way, fetching from
http://media.pearsoncmg.com/ph/bp/b...e_files/departure_schedule_files/sheet001.htm instead of the URL you gave works for me. I followed these instructions: http://3qdigital.com/experience/web-queries-mac-yes-can-heres/

I can't seem to get formatting to work, sadly. Maybe because the CSS is in a different file, maybe because Office Mac is missing features, maybe because I haven't found the right parameter in the IQY file.
 
If you look at the page source, that data is actually being pulled in from a separate page and loaded in a frame. My guess is that Excel doesn't load the frames when it fetches the HTML. Either that or there's some Javascript involved.

Either way, fetching from
http://media.pearsoncmg.com/ph/bp/b...e_files/departure_schedule_files/sheet001.htm instead of the URL you gave works for me. I followed these instructions: http://3qdigital.com/experience/web-queries-mac-yes-can-heres/

I can't seem to get formatting to work, sadly. Maybe because the CSS is in a different file, maybe because Office Mac is missing features, maybe because I haven't found the right parameter in the IQY file.

Alright, wow that's awesome thanks so much.
Yeah, I'm debating buying a cheap fell to have as a second computer since everything we do in school they like to use Windows

Spent about an hour trying to figure this out last night with no luck, so I really do appreciate the help
 
Didn't see this earlier as it's not in the Mac Apps forum, could have saved you a bit of time. The feature you're seeking has never been embedded in Mac versions of Excel - hacks/workarounds using external text editors are the only "fix" for this, and there's no means to "automate" what you're seeking. I've been using Excel on the Mac since Version 4 (before the days of the "internet"...). Honestly, I keep a version of Windows Excel around for stuff just like this.

On my Mac, I use Excel 2016 (Win) in a Parallels VM. The "cheaper" but just as effective option for work like this is to use Excel 2007 in a Crossover VM - no need for Windows - I use this setup on one of my office Macs in which we run stuff like printer scripts, Word/Excel 2007; one can find cheap Office 2003/2007/2010 licenses around - each of them would suffice for this task. The Windows versions of Excel have so many more printing/export options as well, and I spend far more time in Win Excel and use the oh-so-gimped Mac version only when I don't have the Windows version available.
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.