I'm trying to do a Power (Web) Query on Excel for Mac. I select the URL and I want to use to run the Query on and save it as an 'iqy' file in my Queries Folder. But when I go into Excel and execute Data/Get External Data/Run Web Query, my.iqy files are all greyed out and I can't select them. Excel web query is an excellent way to automate the routine task of accessing a web page and copying the data on an Excel sheet. If you use web query, you can instruct Excel where to look (web page) and what to copy (tables of data). What this will accomplish is that Excel will automatically import the data onto a worksheet for you. When I save the file in Mac OS Plain Text Format, it's greyed out in the Excel Run Saved Query dialog, and can't be selected. I haven't found a way to save in MS-DOS format from Pages or TextEdit, but it's easy enough to do in Word for Mac 2011. My queries work fine without the.iqy suffix described at dummies.com. Microsoft 365 includes premium Word, Excel, and PowerPoint apps, 1 TB cloud storage in OneDrive, advanced security, and more, all in one convenient subscription. With Microsoft 365, you get features as soon as they are released ensuring you’re always working with the latest. Create, view, edit, and share your spreadsheets using Excel for Mac. If you’ve tried Power Query in Excel for Mac, leave you feedback in the comments below. UPDATE 2-October-2019: Power Query in Excel for Mac has hit GA. Read more here (url). Update 9-December-2019: I recently did a webinar to cover more on this topic. You can watch the full recording from the video below.
Excel 2010 and 2007 for Windows have the option to import data from the web. Excel for Mac users don’t.
An integral part of working with Excel is using keyboard shortcuts. They make your life so much easier (in the Windows versions at least, in the Mac version I think they tend to shorten your life span).
In my last post I dealt with getting a Help Topic URL, here I’m going to use the web page Keyboard shortcuts in Excel 2010 and import to a spreadsheet.
Get a Help Topic Web Page Address
As you will see, it helps to have the web address or URL on the clipboard before importing data from the web. In this example I’ll use the following steps to get the URL for Keyboard Shortcuts for Excel 2010:
- Press the F1 key
- Type Excel keyboard shortcuts in the search box
- Click the link for Keyboard Shortcuts for Excel 2010
- Right click on the topic heading then select Properties
- Triple click the Address (URL) link then copy (Ctrl+C) to the clipboard
- Click Cancel and close the Help window
Now we have the URL on the clipboard.
Get Data From a Web Page
Choose Data > Get External Data > From Web to bring up the New Web Query dialog box. This dialog box functions as a Web browser and can be re-sized. Clear the Address bar and paste the URL from the clipboard, then press Enter or click Go.
The web page above will appear in the New Web Query window. Scroll down and you’ll see a right-arrow in a yellow box at the top of each table. Click an arrow to queue any table for import into Excel.
We want the entire page so I’ll click the right-arrow in a yellow box at the top-left corner of the web page. This will give us the entire page. Once you click the right arrow it turns to a green check in a box.
Now click the Options… button then select Full HTML formatting.
Since we’re importing the entire page this option will give the best formatting. Now click the Import button and Excel will ask where you want to put the data. I’m leaving the default location cell A1. Click OK.
The data on the web page is imported into the worksheet. This is now an active external query.
To Edit the Query choose Data > Get External Data > Refresh All > Connection Properties then select the Definition tab and click Edit Query. You’re now back to the Edit Web Query dialog box where you can make modifications.
To modify the data range properties, right-click any cell in the imported data range and select Data Range Properties from the pop-up box.
The great thing about a web query is that if the web page data is updated all you have to do is Refresh the query to update the worksheet.
Related posts:
In Office 2011 for Mac, Excel can try to load tables from a Web page directly from the Internet via a Web query process. A Web query is simple: It’s just a Web-page address saved as a text file, using the .iqy, rather than .txt, file extension. You use Word to save a text file that contains just a hyperlink and has a .iqy file extension. Excel reads that file and performs a Web query on the URL that is within the .iqy text file and then displays the query results.
You can easily make Web queries for Microsoft Excel in Microsoft Word. Follow these steps:
Go to a Web page that has the Web tables that you want to put in Excel.
Highlight the Web address in the address field and choose Edit→Copy.
Switch to Microsoft Word and open a new document.
Launch Word if it’s not open already.
Choose Edit→Paste.
The URL is pasted into the Word document.
In Word, choose File→Save As.
The Save As dialog appears.
Click Format and choose Plain Text (.txt) from the pop-up menu that appears.
Type a filename, replacing .txt with .iqy as the file extension.
Don’t use the .txt extension. The .iqy file extension signifies that the file is a Web query for Microsoft Excel.
If you encounter the File Conversion dialog, select the MS_DOS radio button, and then click OK.
Select the Documents folder.
Click the Save button.
After you save your Web query, follow these steps to run the Web query:
Open Excel.
Choose Data→Get External Data→Run Saved Query.
Open the .iqy file you saved in Word.
Excel For Mac Free Download
Excel attempts to open the Web page for you, which creates a query range formatted as a table. Web queries work with HTML tables, not pictures of tables, Adobe Flash, PDF, or other formats. The fancy Web query browser found in Excel for Windows is not available in Excel for Mac.
Web Queries With Excel For Mac Os
You can refresh a Web query quickly by first positioning the selection cursor anywhere in the data table and then choosing Data→Refresh Data.