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

Hi Vilhelm,

You may try to create a new web query by the following steps to check if you can import it.

a. Copy the API address to a new Word document, save it as 11.iqy with the File format as .txt.

b. Choose MS-DOS under Text encoding and click OK.

c. Go to Excel 2016 for Mac, click Data> Get External Data> Run Saved Query. Then choose the saved file 11.iqy.txt to import corresponding data.

In addition, to better understand your situation and help you, please provide the information below:

1.     Detailed steps and related screenshots about how you create the web query.

2.     Version of Office you are using: Excel> About Excel.

Regards,

Moja

Answer 4

Hello,

thanks for the reply. The problem is that I can't choose the file. I am able to create the file and save it in the queries folder and I can see it but I cannot select when trying run the web query.

Any idea why it does not let me select the file?

Answer 5

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.