This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We are transitioning from using the Excel Legacy output option of "Delete & Append" when writing to Excel files. We hoped to utilize "Append to Existing Sheet" - knowing that it would not do any deleting - but are noticing a key difference from the functionality the older driver has. Specifically:
Excel Legacy, using "Delete & Append" allowed outputing to a specific Named Range: C:\""\dsfaf.xlsx|test , where "test" is a specific range on Sheet1
Excel, using "Append to Existing Sheet" when given the same filepath does not recognize the "test" as a named range, it seems to end at finding Sheet1, and then searches that sheet for the last row with data, and then drops the new data in. This makes sense given the naming of Append to Existing Sheet, but the Legacy Driver did not clearly state it could write to a Named Range either, so I though it may also have been given the Named Range functionality.
Does Alteryx no longer support writing to a named range or am I missing a step to enable this? The attached ppt has pictures showing what I am referring to regarding writing to Named Range.
At first I thought the same, but I have tested this and can output to a named range. I had to specify the named range and made sure that I was Overwriting the file/sheet. In my example I have a set of data Mark & Chris where the alteryx plugs daily stats into the cells.
I've packaged this so that you should be able to get to the excel file and see for yourself.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return.
Thank you for response. I checked out workflow and agree with your assessment, however, (and I did not state this in initial post, apologies) we are writing to a template that has formatting in the headers and footnotes below the table we desire to write to. Using the Overwrite Sheet (Drop) erases all formatting/existing data on the sheet, even when writing to a specific named range. Is there an alternative that would enable us to retain our templates formatting/footnotes?
An option - we have 100 different tables (various regions are ran) that we would need to revise. Would be much simpler if the functionality had been retained. We are only making the change as we upgraded to Excel 2013 and ran into some issues with Alteryx Excel Legacy interacting with Excel 2013.Thank you for your responses.
I'm not sure what the difference is here... have a look at the attached module. Formatting is preserved in the rest of the sheet when using Overwrite. Are you meaning to keep the data in the current named range, append to that data inside the range, and preserve formatting inside the named range?
I could be missing something, but formatting is not retained in an Excel sheet when using Overwrite (Drop). Open your template, select the header Alteryx will be outputing to, set it to any random color, then run your module. You can also set the color to a random area on the page that is not having data dropped into it. When you open the template after running the formatting will be gone from the template.
In our scenario we have pre-set formatting in templates that we would like to delete data and append into on existing sheets without losing our formatting. This work(ed) with Excel Legacy.
As to Option 3, we are not setting our formatting with a ReportingTable and Render Tool, it is just a plain Excel file with formatting already in it. Using Alteryx Reporting/Table tools are tedious to set up and maintain so we avoid using whenever possible.
I see what you are saying about formatting being lost in the sections that are being replaced. My apologies, I thought you meant that formatting was lost in the rest of the sheet
So, where the difference comes in, is that the 2 comparable actions that are being used to perform this action here are:
Legacy Driver: Delete Data and Append
New Driver: Overwrite Table (Drop)
So, it kind of makes sense that formatting is lost in those ranges when using the new driver. There are many reasons why the new driver was developed that I won't go into here, but long story short is that this action is not equivalent.
The best method to solve this normally would be as @MarqueeCrew & @Cristian mentioned in having a Data sheet in the spreadsheet. I understand that it is a challenge given that you have 100 odd spreadsheets. Please feel free to add an enhancement in the Ideas forum so that other people can vote on it.