How to output multiple sheets into existing multiple existing excel workbooks

[Lizbhernan]

Hello, I am trying to output into an already existing excel workbook(s). I tried different outputs but I can only output to ONE already existing document rather than the 16 I need. I am attaching screenshots that might give some insight. 

I set up the file format as Microsoft excel which will assist with outputting companies into separate tabs. I then clicked on the existing document but of course I can only assign one rather than 16 that I have. Any idea on how I can do this?

[alexnajm]

You need to change from "Append Suffix" to "Change File/Table Name" if you are trying to output to multiple tabs based on Company No.!

Or if you need to affect the entre path, create your path with a Formula tool beforehand, reference that column in your Output Data tool, and use the "Change Entire File Path" option

Examples will help us better understand the issue if there is more to the problem!

[Lizbhernan]

Hi @alexnajm I attached a sample of what I am trying to do in the workflow. I looked up ways that would help with my issue and it stated to add a formula tool and to add the path ( [FilePath] + "|||" + [SheetName] ) but I am stuck on that part. I attached my sample workflow, any feedback would be appreciated. 

[alexnajm]

You need to put any text in FileName in quotes - I only see quotes around the "|||". [Company no.] can be left alone - otherwise the set up looks correct

"F:\Business Transformation & Optimization\1 - Projects\01. Payroll Projects\31. Kaizen\Tip Allocation\Tip Allocation Workflow Build\Outputs.xlsx|||" + [company no.]

[binu_acs]

@Lizbhernan updated workflow attached

[Lizbhernan]

@alexnajm

Thank you, that helped! I was able to run the workflow, but I’m noticing that the outputs aren’t saving correctly based on property. To troubleshoot, I created a smaller sample workflow (for my sake since I'm still new to Alteryx) with fewer outputs, in case the issue was related to having too many companies.

In this test workflow, I’m splitting the outputs by Job Profile. The outputs should generate based on Job Profiles, so I used a formula for the file name:

"Business Transformation & Optimization\1 - Projects\01. Payroll Projects\31. Kaizen\Local 99-99A OEN\Outputs"+ "|||" + [Job Profile]

• File format: Excel

• Output option: Create New Sheet

• Under Output Options, I selected Change Entire File Path and chose Job Profile as the field containing the file name (or part of the file name).

The issue: I keep getting an error saying I must specify a sheet name. I’ve tried adjusting this in the Write to File or Database tool, but the error persists.

As a test, I also tried using Change File/Table Name. This did output, but it only wrote to the original file I had selected, not separate files for each Job Profile. I think this may explain why the workflow isn’t creating outputs for each Job Profile as intended but not sure how to fix it. I have included my sample workflow and screenshots for reference. On the first screenshot, you’ll see that it outputs by Job Profile per tab. What I actually need is for it to output into each individual existing document, as shown in screenshot 2. 

@binu_acs I also tried your suggestion and similar issue 😕

Thank you both for your help on this

[alexnajm]

@Lizbhernan you aren't using the Job Profile though to split to different files - you are using [FileName] so that's what must be selected in your Output Data tool!

Also your FileName needs to have the .xlsx in there somewhere - what you just posted ""Business Transformation & Optimization\1 - Projects\01. Payroll Projects\31. Kaizen\Local 99-99A OEN\Outputs"+ "|||" + [Job Profile]" does not have it currently. It should be at least "Business Transformation & Optimization\1 - Projects\01. Payroll Projects\31. Kaizen\Local 99-99A OEN\Outputs.xlsx"+ "|||" + [Job Profile] - adding the "F:\" part wouldn't hurt either

[Lizbhernan]

@alexnajm Sorry, for some reason community kept saying there was an HMLT so I had to remove the "F:\" part :(

I apologize in advance but on this part "you aren't using the Job Profile though to split to different files - you are using [FileName] so that's what must be selected in your Output Data tool!" Are you referring to the Field Containing File Name or Part of File Name? If so, I tried that too and still only outputting to one of the existing files and then creating a tab per Job profile.

[alexnajm]

Yes I am, and that is correct - your current configuration would create multiple tabs in one Excel file. If you want the File name to also update, you must add that as part of the FileName formula! Something like

"Business Transformation & Optimization\1 - Projects\01. Payroll Projects\31. Kaizen\Local 99-99A OEN\Employer Contributions - "+[column that contains carpenter, painter, etc.]+".xlsx|||" + [Job Profile]