Help with entering a formula in each excel file in a folder

Hi there - I am looking to loop through excel files in a folder and enter two formulas in each row. What i’d like to have happen:

Open the main reference file that contains the data i need to access
Perform an xlookup against this reference file in each sheet in the folder - Example - =Xlookup(F22,‘ReferenceFile$R2-$R4000’,‘ReferenceFile$Q2-$Q4000’,0,1)
Enter pricing data in each sheet in the folder
Calculate the difference between prices on each row in each excel sheet in a folder

I’d like to do this in StudioX and avoid code if possible. Please let me know your thoughts.


You can use write formula or write cell activity with the required formula

You can give the formula like this


This can be done inside a for each file in folder


1 Like

Hi Anil -

In this situation - what would I put for the ‘Where to write’ as i don’t have a specific excel sheet to reference?


I beleive you know the cell number where to write…if yes then you can use like this


Also if the cell is changing then we have to look at some logic or should save the cell numbers of each file some where…I hope that is not the case.

As I hope there would only be one sheet in each excel…we can use selectedsheet which as only one sheet is there would be the target sheet itself

Hope this helps


Hello mwells-

In Studio X you should be able to use a for each file in folder activity to open the files and inside have a for each row in excel activity to write the formulas. You may need a template file to reference the cells if you are adding new columns for the pricing data and the calculation for the difference.


Hi all - I think I still need help with this. The first file I open is the master file that i will be referencing all my xlookups to. I added a template file that is an example of one of the 100 excel sheets i need to write formulas into. I’m then having it attempt to write the xlookups in each row in those sheets, either until it hits a blank or for rows 22-90 of the sheet. Please see attached and let me know where I’m going wrong.


To start with you need not do that in a loop…instead use write cell once and write the formula in first cell…then use auto fill range activity and pass the range till where to write the formula and the formula would be filled automatically

Hope this helps


thanks - but if i want this to happen for each file in a folder, how would i go about that? I’m having trouble getting it to open each of the files to do that.


You have an activity for each file in folder use it and pass the currentfile.FullName as the file path…


Anil - I can’t get this to work either way I tried.

I intend step 2.1 to signify the source excel file i’m referencing my Xlookup from. I then want 2.1.1 - the end to write the formula referencing that in each file in the folder. It doesn’t seem to be able to understand what the files are that i’m having it reference.

I also have the ‘template file’ in step 2.1 as an example of one of the files i’m trying to loop through, but i’m not sure if that is helping or hurting, or doing anything at all.


Few questions here.

Where you want to write the formula?

If it is in currentfile then May I know why are you oepning the March Ship &… Excel file?

and if you want to write to current file then use a use excel inside the for loop …and then in the Excel file field pass currentfile.FullName

and in the write cell you will pass Excel.ShelectedSheet.Cell("Q22")

And in Auto fill range you ahve to pass from which cell to which cell it needs to fill…Excel.SelectedSheet.Range("Q22:Q48") - 48 here is an example

If you don’t know number of rows then count=Excel.SelectedSheet.RowCount will give you the count of rows in excel can use that number to populate Excel.SelectedSheet.Range("Q22:Q" + Count)

If you are using two excels at a time that is the excel outside the for loop and one more inside loop then make sure to modify Reference as in the Use Excel properties to Excel2 or something

Hope this helps