Run Web Query issue - Microsoft Excel Mac Office 365

Question

Hello,

I am having problems importing data from a website into excel. I managed to overcome all other obstacles but the last one is remaining. When I create a query file (stored in the correct location along with some sample files) it does not let me select the file when trying to import as you can see from the screenshot below.

What should be done to "activate" the "grey" files in order to use them as a data source? Also as you can see some of the readily available files, MSN Money files, are also not in my reach.

I would greatly appreciate if someone with the knowhow could help me out.

best, Vilhelm

Answer 1

This is the URL I tested:

https://www.x-rates.com/table/?from=USD&amount=1

Then double-check your steps. I followed Moja's instructions for saving the web query. I used the file name TestQuery.txt

Then, in Finder, I changed the text file's extension from .txt to .iqy

Then, in Excel I went to the Data menu and chose Get External Data > Run web query and chose TestQuery.iqy

Excel immediately went out and fetched the query as expected. Here's a screen shot of the results:

Somehow, along the way you are skipping a step. When you saved the file in Word, did you switch the text encoding radio button from Mac OS (default) to MS-DOS? That's essential.

I have found that sometimes, even after having selected MS-DOS as the format of the .txt file and then renaming it as an .iqy file, it still shows up as an OS-X  format and I have to reset it to MS-Dos to get it to work, Don't know why this happens.

Mike

Answer 2

If you set up a desktop folder in which to store all your .iqy  queries, it should work when you click on the "get external data" tab (after clicking on the upper "Data" tab...not the one on the ribbon.

What website are you retrieiving data from?

Mike

Answer 3

I am not having any trouble selecting the web query files. When I test, when I select the file, the Get Data button becomes available.

What version of Excel are you using? Go to the Excel menu and choose About Excel. You should have version 16.12. If you have a different version, please do the following, then test again. I tested in version 16.13 180416, so my results could be different from yours.

Make sure your Mac has a working internet connection before checking for updates. Install Mac OS X updates followed by Office updates.

Install Mac OS X updates first:

To get Mac OS updates, go to the App store on your dock, and then click the Updates button to reveal available updates.

Then Install Office Updates

To get the update:

1. 1.     Open any Office application
2. 2.     Go to the Help menu and choose Check for Updates
3. 3.     Let AutoUpdate check for and install updates
4. 4.     Restart your Mac

If you have any add-ins such as WebEx, Mendelay, EndNote, Zotero, TypeIt4me, etc it is essential that you install the latest updates for your add-ins.

I have exactly the same problem. Everything is up to date. When I open the Data tab in excel for mac 2016 and click on 'Get external data',  the Queries file does not open and no  options present themselves.  I have no idea how to get the queries folder into the web query sequence, so I am going to move all my  .txt queries into a their own folder in the Documents file to see if that works

MIke

Answer 4

It seems that the problem might be that you can't find the Queries folder. Here's a screen shot showing the path to the folder:

The trick is knowing that the User Account's Library folder is hidden. Unhide it

How to Show the User Library Folder in macOS Sierra - The ...

Here is a good explanation on how to make web queries

Web Query

https://3qdigital.com/experience/web-queries-mac-yes-can-heres

The default MSN queries do not work, I don't know why Microsoft keeps distributing them.

Answer 5

I have been having the same issue. I have just upgraded Excel for Mac from 2011 to 2016 (where the Web Query function did not work either) and now I find I cannot even select the iqy file which I created. See below.

Can anyone help??? It's driving me nuts! NB software is fully up to date.