I’ll preface this by noting that I’ve only used the software for a little under four days so I apologize if this is a stupid question. I’ve been working to try to build a bot that consolidates multiple excel files by creating a new file with the input files becoming the sheets. Ideally I am trying to build this workflow in a manner that will continue to work with large numbers of input files (anywhere from 10-100). Although I’ve got a rough format built out: Excel Consolidation Bot.xaml (26.7 KB) I have consistently been getting errors with the “Write Range” activity once all the files have gone through the loop. I’m hoping to figure out how to get that particular activity to create a new sheet on the results workbook for each input file.
One of the issues is that you are using the following if activity inside the each row loop:
If item=“xls.ConsolidatedFile.xlsx” then break. This activity exits the For Each activity and continues the workflow with the activity that follows it.
If item!=“xls.ConsolidatedFile.xlsx” then continue. This activity enables you to skip the current iteration inside a for each loop.
So, you are going to exit the for each loop in any case.
Other issue is that you are using the name “Sheet1” to write the data of all the datatables. So at the end you are going to get only the data for the last file. Maybe you want to write the file1 in the sheet1, the file2 in the sheet2 and so on.
Checking your code I think that you want to store the file ““xls.ConsolidatedFile.xlsx”” in the folder inputDirectory + “\converted” that you have created previously. But if you do not specify the full path in the Excel Application Scope Activity the file is going to be created in the project folder.
Appreciate it! The If statement is a holdover from when I had the file getting sent to the project folder instead of the directory. I’ve got that squared away based off your recommendations.
The only remaining question I have is how to set up an expression that would ensure that each file would go into a seperate sheet into perpetuity, so any number of files could be processed by the bot. Would assigning a count variable to the items and then using that count with a “in” “SheetName” statement suffice?
Most of the solutions I’ve seen when browsing the forums have been setting up seperate “Write Range” activities for each individual file, which would not work if the file count exceeds the number of activities.
Then, for the write range activity you can use this variable to write each file in a different sheet, something like below.
With this option the sheets will appear:
If you want an ascendant order then you can use a int variable to store the count of files and decrease it in each iteration you could use something like String.Format(“Sheet{0}”,ExcelList.count-fileIndex)
For some reason I’m still getting an error in the “Excel Sheet Copying Process” activity but I’ve linked the attached workflow to show the adjustments. Excel Consolidation Bot.xaml (26.5 KB) Other than that the formula you provided for the “Write Range” activity is producing the output I need, albeit I still need to figure out how to get the resulting file to save to the directory folder I created earlier.
ashokkarale
sharazkm32
Nidhi_Gupta1
Matt67
RobertRussell_Monsalud
Youri98
Slow_Learner
Roman-Routinuum
adi.mehare
Akash_Javalekar1