Notes: Excel 2011 for Mac cannot import data from OLE DB or OLAP data sources. By default, Excel for Mac uses tables to import data. To turn off tables when importing data, in the Returning External Data to Microsoft Excel dialog box, click Properties, and then clear the Use Table check box. If the external data source you want to access is not on your local computer, you might need to.
- 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.
- Sep 14, 2006 An Excel Web query allows you to bring data from a Web site into an Excel worksheet. It will find any tables on the Web page and let you select the ones containing data you want to put into your.
- The instructions below apply to Excel 2016, 2019 and Microsoft 365 for Mac. These versions of Excel do provide an ODBC driver for connecting to SQL Server Databases. On the Data tab, click New Database Query SQL Server ODBC. Then use the dialog boxes to import the data.
I am a huge proponent of using 3rd-party tools to manage SEM campaigns. Aside from being able to manage accounts from multiple publishers in one place, 3rd-party tools offer plenty of features not available from AdWords or Bing.
One of these features has become near and dear to my heart: web queries. Web queries give you the ability to build a recurring report in your platform (using Marin for this example); rather than format it as an Excel file or PDF, the report is generated as HTML tables that can be easily imported into Excel.
This is especially useful for reports that update on a daily or weekly basis. Rather than downloading a new CSV or Excel file, you simply hit the Refresh button in Excel where you have your web query housed, and the data will update. At the rate I use web queries, I have already saved myself days’ worth of time that can now be spent on higher-priority projects for my clients.
On the Windows version of Excel, dropping a web query into your report takes maybe 3-4 clicks. This is not the case in the much-maligned Mac version of Excel (which hasn’t seen an update since 2011), but it is possible!
In this guide, I will walk you through the process of dropping a web query into an Excel file on a Mac.
Step 1: Create the Web Query File
In order to call a web query on the Mac version of Excel, the query needs to exist in a specially formatted text file. Creating this file is pretty easy; start with opening Microsoft Word or whatever text editor you use and pasting the URL to your web query on the first line.
Next you are going to want to save this as an .iqy file. To do this, simply change the format to Plain Text (.txt) and add “.iqy” to whatever you want to name your web query.
Save this file wherever you would like; just remember the location because you will need to import the file in Excel. Your text editor may ask you to specify how you would like to encode the text file. If this is the case, select the radio button for MS-DOS.
Step 2: Import the file in Excel
Next you will need to import the file in Excel. To do so, first select which cell you would like the web query tables to start at. Once you have done that, you can go to Data -> Get External Data -> Run Saved Query…
Next you will be prompted to choose which file you would like to import. Browse to the web query file we created in step one, and select “Get Data”.
You may be prompted to enter your login information if your web query is being called from a 3rd-party platform. Enter your credentials here.
After you hit connect, if you followed these steps correctly, you should have imported your web query into Excel!
Congratulations! It’s not nearly as easy as using web queries in Windows, but don’t let the process deter you from using this excellent time-saving feature. Web queries are great for budget pacing, report building, or recurring tasks such as identifying poor keywords or opportunities.
In my next guide, I will walk through how I use this feature in Marin to keep track of all my client’s spend in one document.
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:
Excel Mac Web Query
Open Excel.
Choose Data→Get External Data→Run Saved Query.
Open the .iqy file you saved in Word.
Download Excel For Mac
How To Merge Queries Excel
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.
You can refresh a Web query quickly by first positioning the selection cursor anywhere in the data table and then choosing Data→Refresh Data.