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

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.