How to refresh dataset from an opening Excel file

Hi community,

Here is my issue: I have an Excel document that connected to my dynamics 365 data, I saved this Excel document in my company one drive and schedual auto refresh so that everytime we have new data in dynamics 365, this Excel document will update, for example: if we create a new contact record in D365, then this Excel document will automttically add this contact data as an extra line, I do need to keep this Excel document open 24/7 as it won't auto-update if the document was close.

Then I connect this Excel to our Power BI, however, we cannot refresh the Power BI dataset as this excel was opened, so im kinda stuck now...I need this Excel document open 24/7 to get data from D365, however, I cannot refresh the Power BI dataset while the Excel file is open.

Any suggestions?

Attached the error code from Power BI when I try to refresh the dataset.

Cheers,

Dom