Refreshing Data Queries from external file in Excel for Mac (Microsoft 365 version)

Question

I have a file which has a query set up to reference an external data source, an excel file with data downloaded from Morningstar.  

When I attempt to refresh all from the data tab in Excel for Mac, nothing happens.  I've set the connection to point to the file, which is local on my Mac desktop.

Just to test, I tried this on the PC version of Excel (MS 365 version) and it works fine.  The options look different on the PC version for queries and connections but after I change the source and point to the file, I press refresh and I see it updating the data.

It seems as though the refresh option, although it appears in the Data tab, doesn't actually work.

Also, I have the latest update for Excel for Mac via the Insider's Fast program, which claims to allow refreshing of data.

Answer 1

Hi Costal,

Just for the future, please post questions and comments relating to Insider builds to the insider section of the forum. Different support people reply to that area of the forum.

Did you create the link to the data file in Excel for Mac or Excel for Windows?

What steps were taken when making the connection?

I would expect you would have had to set the data source up in the Mac OS ODBC manager application before the refresh would work, but maybe there's some other way to do it that I am not familiar with.

Answer 2

Hi Jim,

Thanks for the direction re: Insider.

The initial file was created on Excel for Windows and it does work if I load it on a windows machine.

When I open the file in Excel for Mac, I go to the Data tab and click on the Connections icon.  From there, one of the queries gives me the option to change the file path.  I use that option and select the reference file, which is on my mac desktop (so is the main excel file that I am using).  I then close those windows and press the "Refresh All" icon but nothing happens (no data is updated).

I'm not familiar with ODBC manager application - I thought that was just for database sources vs. a excel file (which is what I'm using).

Answer 3

Hi coastal,

Do you know what steps were taken on the Windows machine to connect Excel to the data source file?

What file format is the data source (.xlsx .csv .txt something else?).

The data connections dialog is currently being worked on by the Excel for Mac team. I don't know what the expected behavior is today - it could change at any moment!  I will try to make a comparable workbook and compare.

Meanwhile, click the Smile button and send a frown and explain what you just told me. Maybe this is supposed to work but it's broken. Maybe this is something brand new that they're currently hooking up.

Answer 4

Hi Jim,

I've actually seen it work on the Windows side and what happens is that you go to the queries and connection section, select queries and then there is an option for source - when you click on the source, you identify or browse to the file path and it updates the connection.  You then press refresh and you see it running the query and updating the data.

The reference file is an .xlsx - we tried it with csv and Excel for Mac doesn't even allow me to select a csv file for the file path.

I don't actually see the smile button or way to give MS feedback on here...

Answer 5

Hi

The most recent info I've seen from Microsoft is that for some queries you can refresh, but not create links in Excel for Mac. Hang on while I experiment.