Trbl Trying to Add New Sheet to Multiple Existing Excel Workbooks

[hellyars]

I need help trying to print to multiple EXISTING Excel workbooks.

The existing workbooks share the same name as [ASSMNT].   They have an existing worksheet created (rendered) by another workflow. 

I want to: 

1) append a new sheet called "COMPARE" to add to each ASSMNT workbook. There are no spaces in [ASSMNT] (to include the workbook filenames).  I replaced all spaces with "_".

2) be able to re-run the workflow and update the new COMPARE tables at a later time.

How should it be configured?

Images below represent my current (not working) configuration.

[atcodedog05]

Hi @hellyars 

Issue might be with  [ASSMNT] column.

[hellyars]

@atcodedog05  @mceleavey 

So, I went back to [FieldAndSheet] set to

"_"+[ASSMNT]+".xlsx|||COMPARE"

THe Output Tool's 

Write to Database is set to

C:\Users\Steve\OneDrive\_staging\workbooks\assessments\.xlsx|||compare

Take File/Table Name From Field is checked.

Set to Change Entire File Path

Field Containing File Name or Path of File Name is

[FieldAndSheet]

It create the workbook, but does not save it to the OneDrive folder.  Instead it saves it to the workflow's folder.

[atcodedog05]

Hi @hellyars 

I guess formula should be

"C:\Users\Steve\OneDrive\_staging\workbooks\assessments\"+[ASSMNT]+".xlsx|||COMPARE"

[mceleavey]

@hellyars ,

the Write to File Or Database field is irrelevant as you are overwriting this with the setting at the bottom:

This means it's replacing the string in the Write to File or Database field with the value in the FileAndSheet field for each file.

Can you first confirm this value is correct?

M.

[danilang]

Hi @hellyars 

If you're trying to write the same sheet to many files, you'll need to duplicate your data with one complete data set for each output file.

Use the Append Fields tool, which does a cross join of the T and S input data sets.  The first one from the left appends the Sheet name to the file names and the following Formula tool adds the "|||" delimiter to give you an output path with the same sheet name on each file.  Append this list of output paths to your input data, represented by the topmost Text Input tool.  You now have one complete set of records for each output file.  Configure the Output tool to replace the entire file path and you'll get the same sheet in multiple files

If the list of files is dynamic, replace the "File Names" Text Input with a Directory tool that returns the list of file paths, incl. the file name

Dan  

[hellyars]

@danilang  @mceleavey @atcodedog05 

First, thank you all.

So, I had my original setup wrong.  The expression in the formula tool should reference the entire file path.  The Output tool should be set to Change Entire File Path with the Take Field.... checked.   That however, was only a secondary issue to the real problem.

The main issue appears to be a version compatibility issue between Alteryx and Excel. The issue is that this workflow is trying to output an Excel file to a target workbook which itself is a creation of another workflow, but importantly that workflow outputs the workbook using the Render Tool.  Stock, the Output Tool renders to some current Excel format.  The Render Tool outputs in an older Excel 2007 format.  So after getting this workflow to successfully add each Assessment's respective sheet to its respective target workbook, the file is corrupted upon opening it. 

I spent an hour on an call with an ACE.  We found that you have to first open and close the target workbook (outputted by the Render Tool) before you can start outputting new sheets to it form this workflow, but that kind of defeats the purpose of automation.

We also downloaded an Access driver to get the Excel 2007 driver added back to Alteryx.  I believe this use to be included, but it was not present on m installation of Alteryx.  This, in theory, meant that this workflow was outputting to the same generation of Excel as the Render Tool.    BUT, it was only partially successful.   We could add the workbook without the corruption error upon opening it, but the formatting was completely off (requiring the need to manually editing each sheet).  I suspect the formatting of the original sheet created by the Render Tool is to blame. 

Failing that, we also tried a .bat file using Alteryx and the Command Tool to open and close each of the original workbooks before outputting the sheet from this workflow.  This did not work.  The workflow would not complete.  We would have to manually stop it.  It created the updated file, but we had to manually go into the cmd line to manually run it -- at which point, it actually worked for that single workbook.

So anybody know a .bat process using the Alteryx Command Tool that can open and save workbooks outputted by the Render Tool to put them into a modern Excel format?  (I know nothing about .bat files.)

Thanks everyone.