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.  

[NicoleJ]

What version of Alteryx are you on, @EW ?

[MarqueeCrew]

@EW ,

 perhaps 🤔 you could simplify the use case one ☝️ more step and change the xlsm workbook to an xlsx workbook. 

my guess is that could be the challenge for v the output tool 🐞 

cheers,

 mark

[apathetichell]

Is Amp on? Turn it off.

[SPetrie]

I havent had much luck getting Alteryx to actually write over the existing range format. It always shifts the items down.

What Ive done to get around this is use conditional formatting rules for my templates.

Using the rules means that even if the cells get shifted down by the flow, the formatting will still get applied.

I made a few minor adjustments to the flow to get it to work on my system since it didnt like the Crew macros but the overall process is basically unchanged. I really only changed the original excel template.

[apathetichell]

@SPetrie- do you remember to click the skip field names box? That usually creates the issue you are talking about.

[SPetrie]

@apathetichell  - Yes. It does the same if I have it checked or not. It inserts the rows into the specified range, but any formatting gets shifted down. The only consistent way I have been able to update excel templates and keep the formatting the way I want is by using the conditional formatting rules.

[apathetichell]

@SPetrieinteresting - is this for macro enabled files? I haven't seen this on vanilla .xlsx files.

[SPetrie]

@apathetichell This is for all excel templates I have dealt with. This post was the first time Ive actually messed with a macro enabled excel file in Alteryx. Its always been .xlsx for me in the past.

I was on a previous Alteryx version until a week ago so have not tried with an xlsx file recently, but all my existing files are already rule enabled and working properly so no reason to rock the boat by messing with them now. :)

[binuacs]

@EW I changed all the .xlsm to .xlsx and seems to be working for me(with AMP engine on)