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