SharePoint output tool- create new Excel file and write into several sheets

[PH_80]

Hello,

I want to create a new Excel file and write data into two separate sheets. I am only getting the second sheet “Sheet2” in below example in my output.

It works to select an exiting file to write into, but when I select the option to add a file on both the SharePoint tools then the second output overwrites the first output tool, so it seems that it’s not meant to be used the same way we would use the regular output tool.

I suspect that there is something in the configuration that I am missing here?

Thanks in advance!

[PH_80]

@alexnajm Do you have any suggestions on the above? Thanks in advance!

[alexnajm]

@PH_80 I replicated the same workflow on my side, and it worked. Are you sure each tool is configured for each sheet? and to Overwrite Sheet?

[PH_80]

@alexnajm @Hi, thanks for quick reply

Yes both sp tools are configured for each sheet and I have the overwrite option. But how is your configuration on the "select file" tab of both tools? Did you select add file on both? And then wrote the same filename on both?

Or maybe you didn't select add file and just typed in the name in the file path field?

[alexnajm]

Same file being written to on both tools - I selected the file from the list it gave me.

[PH_80]

@alexnajm Ok so you already had an existing file in the sp library that you selected? That works for me as well, but in my case I want to create a new file, so therefore I choose "add file" and write a name for the new file..you didn't do that?

[alexnajm]

Ah I see - then I am not sure. I might look at creating the file name ahead of time and passing that through - but this is why I avoid the SharePoint tools typically. Good luck!

[PH_80]

Yeah same here, trying to avoid them but forced to use them on the server. Right now it only works when I have an existing file with the sheets already created.

With "creating the file name ahead of time" do you mean like a dynamic file naming with formula tool?

Thanks

[PH_80]

@gawa Do you have any suggestions on how to solve the above? Appreciate your input, thanks!

[gawa · Accepted solution]

@PH_80 I found the way to realize it finally. I didn't know this way till now. It is similar to "Take File/Table name From Field" in Output tool. You can create a new excel file and append other sheets simultaneously with one Sharepoint Output tool.

Overall WF looks like this.

1) Append 2 columns by Formula tool: "FileName" and "SheetName"

For "FileName", specify the name of the file you want to create.

For "SheetName", put sheet name row-wise, depending on your  criteria(e.g., IF [tax_code]=10 THEN "Sheet1" ELSE "Sheet2" ENDIF)

2) Put Sharepoint Output Tool after 1).

In "Select File" tab, select "Add File"

Popup windows appear, so select "By Field" and choose "FileName" *field you created in step-1)

Move to "Configuration File" tab, and configure as below.

Select Sheet=By Field

Select Field=SheetName *field you created in step-1)

Existing File Action=Overwrite File

With this configuration, execute WF. In my environment(Designer=ver 2022.3/Sharepoint tool=ver2.4.1), it worked well.