community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.9

The latest release includes several enhancements designed to improve your Community experience!

Learn More

Excel Output to Named Range

Meteoroid

Hello,

 

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.


Thank you

Alteryx Certified Partner
Alteryx Certified Partner

Good morning Travis,

 

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.

 

Capture.PNG

 

Thanks,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Certified Partner
Alteryx Certified Partner

Here is the file.....

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteoroid

Marquee, 

 

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?


Thank you

Alteryx Certified Partner
Alteryx Certified Partner

How about writing to a Raw_data tab and referencing the data?

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteoroid

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.

Alteryx
Alteryx

Hi Travis,

 

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?

Meteoroid

Morning Kane - 

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.

 

Thank you for your response.

Comet

You could export data from Alteryx in a separate Excel worksheet, let's call it RawData, and in main Excel worksheet, place just links to RawData cells where Alteryx will put fresh numbers.

 

=RawData!B1

=RawData!B2

.....

 

 

Indeed it's a lot of overhead setting this worklow but your formatting must be intact.

 

Regards,

Cristian.

Alteryx
Alteryx

Hi Travis,

 

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.

 

Kane

Labels