MS Excel automation

I want to read all excel files from particular folder and all excel files names are different, user don’t know the files name. I have made the program which get all excel files name and sheet names but I am not able to read them, when I am using ReadRange activity it throws error “Its being used by another process”
Thats my worlflow :

Hi @kalpitmantri ,

Try to add Close Workbook Activity after Read Range Activity and add Kill Process activity with filling ProcessName like “EXCEL” after Excel Application Scope.

Alper

hello @alpersahin I tried but CloseWorkbook needs workbook variable not string and my workbook name is stored in Int.

Why you are using workbook activity i.e. Read Range inside Excel Application Scope for same file
Use Excel Read Range it will not thow any error while reading that sheet

Give a random variable like “varWorkbook” for Excel Application Scope Output.
Then use it on CloseWorkbook Input. You don’t have to assign different value for this. it will assign different value automatically when you open an excel file with different names.

Also the main issue is after Read Range activity. I see that you do not use any activity after it. But Output of the Read Range activity is not changing every time. It will rewrite on same datatable constantly .

@alpersahin Yes its rewriteing on same datatable, any idea how to resolve issue.I have also have to write in particular cell but its giving error that Column on found.

Hi @kalpitmantri,

Could you explain what is the next step after read range activity? If you write the data that you’ve already read to somewhere it would not be any problem i think.

So after Read Range Activity you can add a Write Range Activity may be.

@alpersahin I want to write in excel sheet but because of overwriting its not able to contains previous data table and it throw error that column not found. can you tell me that how to save datatable value?
this is my workflow now

@kalpitmantri,

Try to put all For Each Row Activity into For Each Activity after Output Data Table Activity and also put Assign (Counter = 2) activity before first For Each Activity unless if you do not change always the excel sheet which you copied datas for each excel file.
When excel file name changed, the sheet of excel that you want to write on (System Template-Sheet) would not change for this process.

Thanks @alpersahin Its working and writeing data in particular cell but after writeing its throwing error
image
I think its because overwriteing in same datatable.There are lots of sheet to read and its overwriteing in same DT.

Hi @kalpitmantri,

Yes you are right. Could you give me examples how do your sheets look like?

The data you took is always on the same column or does it change for each sheet and excel file?

@alpersahin There are multiple sheets , suppose sheet1 name is EmpCode its have 3 Column Name , EmpCode, Department and sheet2 name is DeptCode its have 3 Column DeptCode, EmpCode, Manager …So I have to read from Sheet1 and write EmpCode in sheet2. Its writing but its overwriting in same DT.

So let me explain what i got. You have many excel files like 5.

For each 5 excel files have 2 sheets and those names are EmpCode and DeptCode.

Also Each sheets have 3 columns. And you want copy data from employee code column, sheet1(EmpCode) and write sheet2(DeptCode),Column B.

Sheets name in each excel files and columns name in each sheets always be same. Is that right?

@alpersahin NO I just given example to you I have to copy some column from sheet1 and paste it in sheet 2, there are multiple sheet and every sheet have multiple column.