Need help with the following steps to be automated

[Atanasov_Antoan]

Hello Guys,

I’m new at the Automations with StudioX and I’ve tried to automate the following steps with different activities and all I’ve tried wasn’t successful. Could you be able to suggest me which activities from StudioX can be used to automate these steps?

1. Step: Open a “Test1” Excel file.
2. Step: Switch to the “Promo1” sheet in the “Test1” Excel file.
3. Step: Extend the formula in column “Bing” to fill the remaining empty cells.
4. Step: Filter the “Remus” column in the “Test1” sheet to keep only the cells that are empty (indicating that this card has no access).
5. Step: Copy the cells below the “Bing” column after filtering.
6. Step: Create a new CSV file named “SafeQ_Import_June_2023” and save it.

[adiijaiin (Aditya Jain)]

Hi @Atanasov_Antoan

Welcome to the Community!

1. Use Excel File,
2. Use Write Cell like this
   
3. Use AutoFill as given above

Replace Your cell here with your starting cell.
4. Read the whole sheet “Promo1” using Read Range.
5. Filter Data Table: Remus is empty
6. Read Column Bing.
7. Write CSV, pass Column Bing Variable. Save as SafeQ…

Follow this and you should be able to do it.

Thanks

[argin.lerit]

@Atanasov_Antoan

Welcome to the UiPath Forum!

@Atanasov_Antoan

Step: Open a “Test1” Excel file.

Use Excel File activity

@Atanasov_Antoan

• Step: Switch to the “Promo1” sheet in the “Test1” Excel file.
• Step: Extend the formula in column “Bing” to fill the remaining empty cells.

@Atanasov_Antoan

Step: Filter the “Remus” column in the “Test1” sheet to keep only the cells that are empty (indicating that this card has no access).

@Atanasov_Antoan

Step: Copy the cells below the “Bing” column after filtering.

@Atanasov_Antoan

Step: Create a new CSV file named “SafeQ_Import_June_2023” and save it.

See attached project for reference.

StudioX-Demo.zip (58.2 KB)

Hope this helps!

[Atanasov_Antoan]

I’m unable to use Read Range activity in step 1.3, because it’s unavailable in the StudioX version 2020.10.6.
Probably “Copy Range” activity will be the alternative method?

[Atanasov_Antoan]

Hello Adiijaiin,

Read Range activity is unavailable in UiPath StudioX 2020.10.6. which is installed on my corporate device.
I’m not sure did Copy Range will help?

[argin.lerit]

@Atanasov_Antoan

That could work. Can you show the properties of the Copy Range activity?

Also, please get in touch with your IT to get your StudioX version updated as you’d be missing out on the new features

Thanks!

[Atanasov_Antoan]

Hello @argin.lerit

Today I’ve managed to update StudioX to version 2022.4.6.

The Read Range activity is now available, I’ve managed to reconfigure the Flow for my case, but there is some error with the “Auto Fill” activity. Actually, it fills the formula in the cells below but it returns this error:

What can be the reason for that?

[argin.lerit]

@Atanasov_Antoan Thank you for the update and congrats on the upgrade. Can you show the properties of the Auto Fill Activity?

Thanks!

[Atanasov_Antoan]

Sorry for the delay @argin.lerit

Actually, I’ve managed to update the process and the Autofill activity will no longer be required for the automation. But now I’ve faced difficulties with the Excel Filter activity.

I’l explain the newest steps that must be automated for this process:

1. Step: Open an “Onboarding new process - Version 2023 RPA” Excel file.

2. Step: Switch to the “Access cards” sheet in the “Onboarding new process - Version 2023 RPA” Excel file.

3. Step: Filter the “Printer” column in the “Access cards” sheet to keep only the cells that are empty (indicating that this card has no access). As it’s shown in the screenshot.
   

   

4. Step: Copy the cells below the “Script” column after filtering paste it into a new .CSV file.

5. Step: Create a new CSV file named “SafeQ_Import_June_2023” and paste the copied information from column “Script” (!!! It is important to paste the copied information as a Values&Number Formatting to paste only the values instead of the formula shown in the screenshot!!)
   

   

6. Save the .CSV file

The Automation:

1. Step: Open an “Onboarding new process - Version 2023 RPA” Excel file.

2. Step: Switch to the “Access cards” sheet in the “Onboarding new process - Version 2023 RPA” Excel file.

3. Step: Filter the “Printer” column in the “Access cards” sheet to keep only the cells that are empty (indicating that this card has no access).
   

   

4. Step: Copy the cells below the “Script” column after filtering paste it into a new .CSV file.

5. Step: Create a new CSV file named “SafeQ_Import_June_2023” and paste the copied information from column “Script” (!!! It is important to paste the copied information as a Values&Number Formatting to paste only the values instead of the formula shown in the screenshot!!)
   

   

6. Save the .CSV file
   

These are the properties for the “Filter activity”

And this is the error message:

Do you have any idea what is the reason for this error and I’ll be thankful if you can suggest to me what steps should be used to complete Step 5

[argin.lerit]

@Atanasov_Antoan - Can you confirm that the headers of the Access cards sheet occupy only one row instead of merged?

Thanks!!

[Atanasov_Antoan]

@argin.lerit

Can you confirm that the headers of the Access cards sheet occupies only one row instead of merged?

Confirming

[argin.lerit]

@Atanasov_Antoan Try to add one more Filter activity before the existing one and this time choose the Clear any existing filter option like this:

Hope this helps

[Atanasov_Antoan]

The reason for this error was found.
The filter can be implemented only on Excel range, but I’ve tried to run it try a excel table …
I’ll try to use “Filter Data Table” activity in my flow instead of “Excel Filter”

[argin.lerit]

@Atanasov_Antoan - Well done! Please update how it works out for you.

Thanks!

[Atanasov_Antoan]

Hello @argin.lerit

I’m happy to inform you that the automation is done and is working fine. I’ll share the workflow or the robot here if someone else will face the same difficulties as me in the future.

As I mentioned, the “Filter” activity doesn’t work for my case because I’m working with an Excel table, not a range.

So the “Filter Data Table” activity was the best option for my case:

I’ve successfully configured a formula in the Excel table using CONCATENATE to extract the desired information below the “Script” column. Consequently, I have implemented a filter to exclusively display the data positioned beneath this column.
Furthermore, I utilized the “click,” “type into,” and “delay” activities to complete the final steps of the automation process. These steps involve importing the filtered data into the web page of the SafeQ portal. Unfortunately, I am unable to provide screenshots of these steps due to corporate restrictions.

Best Regards,
Antoan

[argin.lerit]

@Atanasov_Antoan Congratulations! Happy you were able to make it work, thanks for sharing.