Writing to Multiple Excel Tabs with PowerShell

[Wojtek_Dz]

I think that every Alteryx Designer user has met in their work the error message, “The process cannot access the file because it is being used by another process. (32).”

During my work, this error mostly appeared when I wanted to save a couple of different tabs in the same Excel file.

My example use case looks like this: we have a data set with columns like Full Name, Job Title, Department, etc. (as below) in one sheet in one Excel file.

We would like to split this data set into separate tabs (by Job Title column) and save it as one Excel file.

We could use Control Containers, which are described here. But this option is available from the Alteryx Designer 2023.1 version. So, if you are using an older version of Alteryx Designer, I’m presenting my solutions below.

There are a couple of ways to solve this case:

• Output tool configuration: 
  • Take File/Table Name From Field - this option is available to configure in Output Tool configuration. We are choosing by which column we would like to split the data set into separate tabs (in our case, it is Job Title)

*optionally, before Output Tool, we can create a new column named Path with the Formula Tool, where we specify the path of the output file, and after “|||,” we put the column name by which we would like to split our results. In our case, the configuration should look like this:

Then, we can use this Path as the Field Containing File Name or Part of File Name in Output Tool configuration.

• The combination of Block Until Done, Append and Count tools + Output tool configuration as in the first solution.

• A Batch macro -  I’m using a list of Job Title as a control parameter for our macro, which I generate by aggregation--Group by Job Title column in the Summarize tool.

 

The inside of the macro looks like this:

• The Sys.sleep() function in the R tool

The configuration of R tool looks like this:

None of the above solutions reliably solved my problem with the error message: “The process cannot access the file because it is being used by another process. (32)”

But finally, I found a working solution which guarantees success for both Designer Desktop and Gallery workflows.

PowerShell solution

First, I created the new output file path which navigates the output tool to create Excel files with only one tab which contains only one type of Job Title.

Second, I created *.bat file which allows Alteryx to run a PowerShell script.

Third, I created script in PowerShell which reads all .xlsx files from dedicated directory (in my case all files with Grade* prefix) and then saves all sheets to one combined Excel file ALL_Sheets.xlsx.

Now, we go to the Event Mode configuration in our workflow. We choose the Run command.. option after expanding Add button.

Then in Edit Event window, I set the configuration as below:

Run Event When: After Run Without Errors

Command: Path where you save *.bat file.

Timeout (in seconds): I expanded it to 1000s

At the end click “OK”

Cool, the first event is done.

Next, I’m creating a second event by repeating the same steps as before.

In this Edit Event window, I set up the configuration as below:

Run Event When: After Run Without Errors

Command: powershell

Command Arguments: rm Grade*.xlsx (this command remove all excel files with Grade* prefix)

Working Directory: Path to the folder where Alteryx saved all separated Excel files

Timeout (in seconds): I expanded it to 1000s

At the end, click “OK.”

How does it work?

In the first step, Alteryx saves the Excel files with only one tab which contains only one type of Job Title. (in our case there is 33 new files.)

Then, the first event is on the way. It is start run the *.bat file which allows Alteryx to run the PowerShell file which combine all tabs from all *.xlsx files to one Excel file.

And in the end, the second event removes all Excel files with Grade* prefix.

Success! Now, we have one Excel file with split data by Job Title column.

Extra feature

Now, our use case is to save all the files created on the same date as one Excel file. We need to make a couple changes in our previous solution.

First, in the place where we are creating new path for output files, we append the date to our file name.

Second, we need to add lines about the date to the PowerShell script like below.

And that's all. Now, you can combine files created on the same day that the workflow was launched.