cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pcooperKAC
Level: Powered On

Excel table formatting w/Delete row and Insert row

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:

 

Capture.JPG

 

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:

Capture2.JPG

 

After deleting original record and inserting new record:

Capture1.JPG

 

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Excel table formatting w/Delete row and Insert row

@pcooperKAC 

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-

View solution in original post

pcooperKAC
Level: Powered On

Re: Excel table formatting w/Delete row and Insert row

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.

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Excel table formatting w/Delete row and Insert row

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!

Super User
Super User

Re: Excel table formatting w/Delete row and Insert row

@pcooperKAC 

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-

View solution in original post

Super User
Super User

Re: Excel table formatting w/Delete row and Insert row


@edgonzales wrote:

@pcooperKAC 

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!

pcooperKAC
Level: Powered On

Re: Excel table formatting w/Delete row and Insert row

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

 

-Phil

pcooperKAC
Level: Powered On

Re: Excel table formatting w/Delete row and Insert row

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.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,636)