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

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.

Blob Template Q.yxzp

Output

Accepted answers

All comments

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.

Blob Template Q.yxzp

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.

binu_acs

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

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.

@binu_acs - 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!

Blob Template Q XLSX.yxzp

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!

binu_acs

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

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 😊

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

Vinayakk88

Its still the issue even in latest version i.e. 2023

Arek

It was exactly my case. Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703