Connecting powerbi to an xlsm excel file

[SoroLassina]

Hello to all of you,

I'm asking the community for help because it's been almost a week that I can't find any solution to the problem I'm having with my dashboard on power bi.

Indeed, I programmed an excel file in vba, with forms and codes for the users of my service. This is to structure the data that I receive and that will be used to fill my dashboard.

The problem is that when I use the named tables of my xlsm excel file as a data source, after the transformation when I click on "close and apply", I get errors such as: the external table "X" is not in the right format or that the file is in use and therefore power bi cannot access (NB: I always take the trouble to close the file)

However when I save the file in xlsx format, the problems disappear.

I even tried to build a data model directly in the file through power pivot from excel that I was going to retrieve to get around the problem but to no avail.

I really hope someone can help me get past this. Thank you in advance for your consideration of this request.

I remain available for further information.

[Knighthawk]

Hi SoroLassina,

I came across the following post, which provides some comments on a solution they were able to find with their data, and has two additional hyperlinks to other posts as reference, all seeimingly with a similar issue to yours.  I am not sure whether you have already had an opportunity to check any of these posts out, but I wanted to share the link to them just in case it could be helpful:

https://community.powerbi.com/t5/Desktop/Trouble-importing-data-from-Excel-macro-enabled-file-xlsm/m...

Essentially, from what I am gathering, there might be some programming limitations with Power BI that Microsoft may have not yet resolved with .xlsm files; howvever, these are older posts, so these issues may have been resolved by now and may not be realted to what you are experiencing.

I hope that one of these sources helps, or that someone on here has a much more educated solution, since I am still relatively new to Power BI.

Best Regards