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

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!

2024-03-20_17-28-33.jpg

2024-03-20_17-39-46.jpg

Output

Sharepoint

Accepted answers

gawa

@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.

All comments

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?

SharePoint output tool.png

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

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.

alexnajm

This looks awesome @gawa !

gawa

Thanks, @alexnajm. I always struggle with Sharepoint tool. They sometime show weird behavior...I google a lot by copy and paste the error message😅

PH_80

@gawa Worked really well! Thank you so much!

bertal34

@gawa this is great! Is it possible to control the order of the worksheets in the excel output? Mine are created in alphabetical order (ONE, THREE, TWO).

PH_80

@gawa Hi, the solution is working well, but I have one issue, all the sheets in the workbook are not having the same layout with the same columns in my case. One of the sheets have different columns selected but with this setup all of the columns will appear in that sheet. Since we are using the union tool and only one select tool and SP output tool I am not able to select and customize the columns appearing for that certain sheet..

Do you see any workaround for this scenario?

Thanks in advance!

MackStewart

@gawa Thank you so much.
This is exactly the reply I've been looking for.

Well, my quest was a little simpler, but you led me to the answer.

Brilliant.

acheedel

Hi @alexnajm,

Could you please suggest any guidance on the SharePoint output connector tool that produces the exact same sheets in the file multiple times.

Thanks

sharepoint output.png

alexnajm

@acheedel This is not something doable in excel since excel does not allow you to have the exact same sheet name multiple times in one file. If you have them as different names, it could work, in which case gawa’s solution from earlier is the way to go.

acheedel

@alexnajm, will check on that thanks🙂

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335