Writing to a named range in Excel - errors

Question

Hi,

This errors crop up when I open the output file.  It is happening when I write to a named range in an XLSM Excel file.  This particular process writes about 20 named range outputs into a single file.

Currently I am building/testing, so only writing one named range at a time.  Some work fine others give me what is described below.

When writing to the named ranges (which takes a lot of layering with Block Until Dones) I get some to work without issue but others throw me what appear to be random XML errors on the Excel side.  I think there might be carriage returns or some other garbage in there somewhere that is causing the issue.  There are a bunch of text heavy "comment" fields or "explanation" fields.

The error log doesnt make sense to me.  When opening the Excel file with 7-zip file manager the reference to Line 981, column 129 is the end tag for "sheet data", however I find a beginning tag for it so I don't know what is happening.  However I am no XML expert.

I have been fumbling my way through these as I have come across them.  One being that the sheet tab name had an '&' in it which I changed to the word 'and' to solve it.  That solution worked but doesnt make sense since Alteryx makes no reference to the sheet name only to the named range on the sheet.

As mentioned I think it is random bad character(s) somewhere in the data I am passing, but I have tried various things like remove ALL punctuation with the Data Cleanse (which isnt a viable solution but I had to try it).  I also tried multi-field formula of REGEX_Replace([_CurrentField_], "[^ -z]","")

I am missing something, I just cant figure out what to try next.  Unfortunately it is highly sensitive info, so I cant share workflow or data.  If I gave sample data, I'd likely miss whatever is causing the problem anyways.

DanielG · Answer

@vil  --  same as the other post I just commented on with you tagged. I completely abandoned named ranges.  I populate new sheets to the template file copy and then I use VBA to move the data from that new sheet to the templated structure (followed by VBA deleting the "data dump" sheets.)

Named Ranges were taking me too much time to figure out, so I just completely removed them from my process.  😀

My comments from the other thread can be found here too for anyone interested:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Keep-excel-data-validation-formatting/m-p/909117/emcs_t/S2h8ZW1haWx8dG9waWNfc3Vic2NyaXB0aW9ufEwwQU9ZM0RUSTRLWUl8OTA5MTE3fFNVQlNDUklQVElPTlN8aEs#M221023

vil · Answer

Hi, do you have any updates on this? I also have similar issue, but in my case I think data validation in my excel template causes problem (without data validation it works fine, but I need to keep it for the users). By the way, what is your named range syntax in Alteryx output tool?

DanielG · Answer

Making progress.  I think it might have to do with a change in the data type that Excel has in the template to what is being passed in by Alteryx.  Need to do some more testing on this, but it is a promising lead.  Just typing out the question above got me thinking differently about it.  I had been staring at this issue for so long I think I just overlooking the issue.