Need help with dynamic process

Hi all! So far I have successfully attempted in getting the robot to check to ensure that when cell n11 is = to attendance sheet countif, it will indicate a tick symbol on the appendix excel document. My question is, how do I make it dynamic? I have several other attendance to check through and would like the robot to go through the docs in the folder.



@ShengChao_Soh - If you could share the sample sheet(after masking sensitive data) and explain your condition one more time - Would be great…

@prasath17 Thanks prasath17. Currently, the robot will first ensure that the Class ID matches with the attendance sheet ID, then it will proceed to check if cell N number is = to attendance sheet number through a countif in Attendance_template > Attendance Sheet L74 : L86. If it matches, it will indicate a tick symbol in the appendix excel row.

How do I proceed to make the bot dynamic whereby it goes through a lists of attendance sheet with the appendix class ID? Right now, my code is static.

Appendix_Template.xlsx (12.9 KB)
Attendance Template.xlsx (102.5 KB)

If I understand, you would like to perform your working subroutine over multiple documents in a folder?

I apologize for my lack of information (I haven’t used StudioX in a while)

Look for a “For Each File in Folder” activity, and surround your subroutine with it.

I hope that gets you on the right track. Also, try searching the forums for ideas to loop over files using StudioX.

Regards

@ClaytonM I tried For each file in folder. However, I have no idea how to define the path along with stating the if condition attendance sheet to be dynamic. Currently, it just checks for that specific excel file - cell B1.

Do you mean you want the Cell Range like L74:L86 and the Read Cell M86?
You want the cell address to be dynamic for different files?

@ClaytonM Same range, but when i execute the bot, it seems to only take the cell from template attendance rather than those in the folder.

I see, so you want to use the file being looped over using the ‘For each file’

Can you show your attempt to use that activity? You probably just need to replace the ‘Excel file’ textbox with the variable from the For Each

Looking at an example on the forums, looks like it be like this:

For Each File in Folder
  For each
  AppendixFiles
  in Folder
  SpecifiedDirectory


Use Excel File
  Excel file
  [AppendixFiles]

where AppendixFiles is your choice of name to represent the list of files you are looping over

Reference

Hi @ShengChao_Soh … I guess I made it dynamic…here is my workflow…

As you could see, I wrote the formula straight away in the Appendix sheet by including IF condition…I have also tried to include Count IF also here…but it was keep failing…So only thing you have to do build a separate loop beginning of the workflow to process all the Attendance file and write the Count if results to M86.

Attendance Files

My Output

Workflow xaml:
Attendance.zip (319.4 KB)

You can clear the values in Column J and try adding more attendance files in the folder and run it and check…

Hope this helps…

1 Like

@prasath17 Hi Prasath! Thanks so much for the help! It worked already. Should have thought of the if statement in the write cell lol.

Can I check, if let’s say each of the attendance file are password protected, what should I be using to bypass the password when excel prompts for it?

If you know the password you can provide here … OR i guess you have to try the UiAutomation to click ok or cancel to bypass it…

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.