Preserve Format on Overwrite not preserving format (Output Data to Excel XLSM)

[EW]

I'm attempting to use the super cool Blob template process that @NicoleJ described here.

My problem is that even when Preserve Formatting is checked, background color, borders, and other formatting is lost.  

I've seen elsewhere that you have to specify the range, so that's what I've done.  I've tried it both skipping and including field names, and every other which way I can figure out.  Does anyone know of a solution or workaround?

I've made a simplified example to show the issue, my actual template and data will be a lot more complex once I figure this out!  I'm not sure if it's relevant, but my workflow also generates a BAK file for each output spreadsheet when run.  The Wait and Block tools seemed necessary to prevent file corruption from writing the data too fast, I believe.  

[EW]

Thanks so much everyone for your replies and feedback!  Love this community.  😁 

@NicoleJ - I am on version 2021.3.1.47945, non-admin.  It might be time for an update anyway!

@MarqueeCrew - it appears to be doing the same thing with XLSX files.  I'm hoping XLSM isn't the problem, as VBA macros will be critical for my actual use case.  I'm attaching an XLSX version though.

@apathetichell - AMP is off, I verified it does the same thing with AMP off or on.  

@SPetrie - conditional formatting seems to work in this example, I'll need to verify if it will work in my actual use case since that's a lot more complex.  It might be problematic to go that route, but could be an option if there's no fix in Alteryx.  

@binuacs - I copied your workflow, replacing the CREW macros with a block until done (that part seems to work great!) and changing it to  XLSX.  I've tried with AMP off or on, and it's STILL removing the formatting for me!  I'm not sure what the difference is!  I'm attaching my simplified workflow and with XLSX template.  

I'm still puzzled by this - XLSX didn't solve it for me, and I'm hoping that's not the best solution since my actual use case depends on macros.  I'm hoping this further simplified example will help identify the issue.  I'll also try updating Designer when I'm able to and see if that gets it.  Thanks so much everyone!

[NicoleJ]

@EW AMP is definitely not the cause, nor is XLSX vs XLSM, so you should be fine to keep both those in play :) 

I would recommend trying to format something less than the full column - if you only formatted 1000 rows, for example, it seems to keep the formatting just fine? This is odd behavior, so we'll look into it more on our end to see if there is something we can do to mitigate that, but if a partial column format solves the problem, you might be in business!

Hope that helps!

[binuacs]

@EW I copied your template to a new xlsx file and tired , it was working fine that time 

[EW]

Thanks all!  I was finally able to find time to update Alteryx, and it's behaving the same in the current version.

Using an XLSX file still eliminates the formatting, but changing it to only format certain rows, not the full column, seems to work.  

I'm hoping full column formatting can be kept in future releases, that would be pretty important in my use case.

[NicoleJ]

@EW Glad to hear the workaround works for now - can you confirm if this behavior (i.e. full column formatting preserved) worked for you in a previous version? If not, then would love to see you post this as an Idea in the Community boards as an enhancement to the existing formatting functionality so we can consider as an update in the future... if it is changed behavior from how it worked previously though, let me know and I'll follow up on it as a bug 😊

[EW]

Thanks @NicoleJ!  I've submitted an idea here in the Ideas section.