Linking Excel tables in two workbooks (one stored locally and one on OneDrive)

[Asirbu]

Hi everyone, 

I have a table in an .xlsm file stored locally, and I would like to link the data to an .xlsx file stored on OneDrive. The goal is to have the tables synchronized, so any data changes in the local .xlsm file would be automatically reflected in the cloud-based .xlsx file. Is this possible?

1. The VBA code in the .xlsm seems to break when the file is stored in the cloud. Hence, the requirement for the file to be stored locally.

2. The .xlsx file needs to be in the cloud, as the data is further used by a flow. Having an additional .xlsx file seems necessary, as the Excel Online Connector in Power Automate does not recognize .xlsm files.

Thank you!

[#2 · Accepted solution]

@Asirbu 

If you allow, here is a little thought-provoking aid / information about your plans.

Differences between using a workbook in the browser and in Excel

https://support.microsoft.com/en-gb/office/differences-between-using-a-workbook-in-the-browser-and-i...

Get data from Excel workbook files

https://docs.microsoft.com/en-gb/power-bi/connect-data/service-excel-workbook-files

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

[#3]

Thank you so much for sharing these articles @NikolinoDE ! Saving it as an .xlsb did the trick. 

I was able to use it in Power Automate without having to go through the pain of creating an additional .xlsx file, and figure out how to link the data between the two. Much appreciated!

[#4]

Your welcome.
I was pleased to be able to help you with my humble knowledge.

Nikolino
I know I don't know anything (Socrates)