Making a formatted Excel Output for End-Users

[willcogs26]

Hello! 

I need to create an excel document from Alteryx, formatted in a presentable way. I have been trying to get this to work in a few different formats now and can't get ane one solution to work how I need it to. 

First I tried using the Reporting Tools, specifically the Table and Render Tools. However, I have one main issue with these tools, 1) I can't turn off Text wrapping which is a HUGE deal as I have two Concatinated columns that do not need to be "seen all at once" but the end-user wants them in the document this is causing some rows to be up to 200px in height. Then 2 smaller issues, 1) I can't pre-add filters to the document and 2) I have to convert everything from inches to pixels to determine the size of the columns... not the end of the world, but annoying (not that I'm looking for that to be solved here)

I then tried to create a Macro to do the work for me, using this discussion as reference: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Run-Command-Executing-Excel...

and trying to execute this, fairly simple VBA code. 

Sub Macro1()
'
' Macro1 Macro
'

'
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1:BP1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
Range("A1:BP1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Range("A1").Select
Cells.Select
Cells.EntireColumn.AutoFit
Columns("E:H").Select
Selection.ColumnWidth = 18.57
Columns("S:T").Select
Selection.NumberFormat = "#,##0"
Columns("U:AS").Select
Selection.NumberFormat = "$#,##0.00"
Columns("AT:AU").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.00%"
Columns("AW:AW").Select
Selection.NumberFormat = "#,##0"
Columns("AX:AX").Select
Selection.NumberFormat = "$#,##0"
Columns("AV:AV").Select
Selection.NumberFormat = "#,##0"
Range("A1").Select
End Sub

(Apology in advance as VBA is not something I write often)

The two issues I'm having trying to execute the VBA in Alteryx is 1) the document doesn't close, even after adding a quit clause it stays open in my task manager:

2) even if I could get the VBA script working, I'm not sure I want to send my end-user a macro-enabled excel document. 

If anyone else has a better way of adding some VERY simple formatting to an excel document through Alteryx ANY help would be greatly appreciated. I am always shocked at the lack of functionality in the reporting tools for direct to excel and trying to write VBA is not my ideal solution, but I am willing to get any work around here to finally automate the project!

Thanks!

- Will

[Luke_C · Accepted solution]

Hi @willcogs26 

I agree that the reporting tools aren't the best for Excel. There is an option for excel outputs to 'preserve formatting'. Using that setting, what you can do (assuming that the output columns are static and won't vary between runs) is create a blank 'template' file with all of your formatting there. Then the workflow can make a copy of that template each time it runs and output the data to it. The solution I provided in the below post has more details and examples of how to do it.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Clearing-Data-in-Template-before-Overw...