Excel table formatting w/Delete row and Insert row

[Anonymous]

Hello,

I have a flow that pulls records from Dynamics and adds them to Excel tables on a weekly basis, removing the previous week's records before insert. The relevant steps for one table are displayed in the image below:

Within the data I am pulling from Dynamics, the length of the text fields varies greatly from record to record. So the "Description" field for one record may contain a single sentence while in another it is a 2,000 character essay. The Excel report needs to be readable and printable, so "Wrap Text" is enabled and the column sizes are fixed. The problem is that when the Flow runs, the row heights will automatically resize to accomodate larger text but don't resize the other way around to fit smaller text. In other words, when a row that was previously occupied by an essay is replaced by one with a sentence, I end up with this situation:

Original record:

After deleting original record and inserting new record:

This is not the expected formatting behavior. If I manually replace the row in Excel, either by pasting or by typing in a cell, the rows automatically resize to fit smaller text. It should do the same when inserted by Flow. This issue creates a lot of wasted space on my report, as the row heights can only get larger and larger. 

I've tried a couple of workarounds to alleviate this issue:

• Overwriting the file each week with blank tables with normal sized rows - this does not work as overwriting the file will prevent the Flow from being able to read the file metadata, even if the file name, table names, etc. remain the same.
• Using a macro to clear the formatting - doesn't work as Flow can't connect to .xlsm files

At this point I've resorted to manually reformatting the report each week, but it would be nice if there was a solution that didn't require that kind of intervention. Seems like the row autofitting just isn't fully functional unless the Excel file is opened by an actual user. Can anyone provide insight into this issue?

[Brad_Groux]

These shortcummings are why Flow experts never recommend using the Excel connector, as it has severe issues and limitations. I'd recommend using a SharePoint list instead, and exporting the list to an Excel document if and when needed. 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[edgonzales · Accepted solution]

@Anonymous 

I'm not great with Sharepoint, so I sometimes need to find workarounds that don't include going there instead of Excel.

If you're in the same boat, check out this journey...it might work for you:

1. Create an Excel Macro that autosizes the rows.
2. Call the Excel Macro from Powershell.
3. Run Powershell from Flow.

I'm not saying it will be pretty, or even work 100%, but it might get you closer.  If Sharepoint is a better option for you, then ya...that connector has way more flexibility.

You might also consider making the resize as part of the report output process.

Just a thought.  Keep us posted.

-Ed-

[Brad_Groux]

---

@edgonzales wrote:

@Anonymous 

I'm not great with Sharepoint, so I sometimes need to find workarounds that don't include going there instead of Excel.

If you're in the same boat, check out this journey...it might work for you:

1. Create an Excel Macro that autosizes the rows.
2. Call the Excel Macro from Powershell.
3. Run Powershell from Flow.

I'm not saying it will be pretty, or even work 100%, but it might get you closer.  If Sharepoint is a better option for you, then ya...that connector has way more flexibility.

You might also consider making the resize as part of the report output process.

Just a thought.  Keep us posted.

-Ed-

---

The biggest problem with running Flows against an Excel file are the lockout timers of 6-12 minutes. If anyone or anything accesses the file it can lock out the file, and break the Flow.

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Anonymous]

@edgonzales Thank you for the suggestion. Would that work with an .xlsx file?

-Phil

[Anonymous · Accepted solution]

I resolved this issue with a very basic solution that I didn't think of originally. I thought I had reached a dead end when Flow couldn't access .xlsm files, but I forgot that you don't need a file to be an .xlsm to run macros on it. I don't mind pushing a button to run a macro every week, so I created a separate .xlsm file with a macro that reformats my main .xlsx file. Maybe in the future I will try automating it further with the Powershell solution that @edgonzales suggested.

Thank you both for your support.