Create one Excel Output File with several sheets?

[horth007]

Hi All

I have a problem to create one excel fiel with three sheets. Currently, I do the following

Creation of three Excel workbooks via formula and the following name definition:

1) German Austrian Distribution\Output Verification Reports\"

+DateTimeToday() + " Pay Date " + [First_Pay Date]+" Germany Scope and Summary.xlsx||Scope Check summary"

2)German Austrian Distribution\Output Verification Reports\"

+DateTimeToday() + " Pay Date " + [Pay Date]+" Germany Details.xlsx|Details"

3)German Austrian Distribution\Output Verification Reports\"

+DateTimeToday() + " Pay Date " + [Pay Date ] +" Germany ERROR REPORT TO CHECK.xlsx|Error CHECK"

Finally threee excel wokrbook wil be created, with specific named sheet. But I would like to receive one workbook with three sheet instead of:

German Austrian Distribution\Output Verification Reports\"

+DateTimeToday() + " Pay Date " + [Pay Date ] +" Germany Summary.xlsx with the sheets 1) Scope check summary 2) Details 3)Error CHECK

The Block Until Done is not working, as the Information are prepared via different steams....

How the output file must be configured to ensure this?

Thanks a lot....

[grazitti_sapna]

@horth007  in order to do so you need to have :

a) 1) Scope check summary 2) Details 3)Error CHECK names in one column suppose the sheet name column consists of all the specified sheet names at row level.

b) configure your output tool as follows

I hope this helps if not then I would suggest you provide a sample workflow so that we can help you out.

Thanks!

[horth007]

Thanks, it is one file now,

But the problem is, the formula results are different in the headers and when using the union tool, the three sets of header will go to each sheet.

[grazitti_sapna]

@horth007 is it possible for you to share a sample workflow?

[horth007]

 @grazitti_sapna I made a sample input data, based on the 3 sheets I need. let me know if this sample workflow helps?

[grazitti_sapna]

Hi @horth007, I have gone through your workflow and have made some tweaks but unfortunately, there is no way to create a dynamic file name unless creating a separate file as you are doing now.

One way I can tell you  is to create a file name manually as per the required format and pass sheet name 1 column as in the attached file to get the desired output that is all the sheets in a single file

I hope this helps!

Thanks!

[horth007]

@grazitti_sapna Thanks for your help, I will try if easier if I will consider three outputs instead. But in case your help is very much appreciated.

[grazitti_sapna]

@horth007  you are most welcome.

[HomesickSurfer]

Hi @horth007 

Export the attached workflow package.  

Sample formula expression to output 1 file with input sheetname in the same directory as input, prefixed dates to filename:

If this works for you, please like, share and/or accept as solution.

FileGetDir([FileName])+DateTimeToday() + " Pay Date " + [First_Pay Date]+" "+"Germany Output Verification Reports"+Replace(REGEX_REPLACE(FileGetExt([FileName]),'[[:punct:]]|[\$\+<=>\^`\|~]',''), "xlsx", ".xlsx|||")

[horth007]

@HomesickSurfer Thanks for this, but for me it is not working. I only attached a simplified test workflow as example. In the complete workflow, there is no input file to prepare the file name.

I saw you added to the input file the option "5 Output File Name as Field Full Path to generate the File name for the further consideration.

But in the workflow, the considered data are from a "summarize" or "union" tool, therefore, I cannot add the full path to have the proper names.

I only have the output folder to the related server  "\\euro\dfs$\Digitalization\Alteryx Tools\German Austrian Distribution\Output Verification File\"

Then the file name should be created dynamic 

+DateTimeToday() + " Pay Date " + [Pay Date]+" Germany Summary.xlsx"

to consider three sheets in the workbook:

|Scope and Summary.

|ERROR Report to check

|Details

Not sure if this would to complex as I can also go with three workbooks