I have two challenges with Excel spreadsheets and I need help.
First challenge:
In a directory, I have "n" excel files. It could be dozens. Each excel file has the same name as a tab in a Central excel file. I managed to put all the names of these files in a List. Now I need to check if each file that is on the list is already a tab in the Excel Central file.
The second challenge is a continuation of the first challenge: Open each of these files that are also tabs in the central worksheet and compare the content of each of these files with the content of the tab that has the same name in the central worksheet.
If a line from the file is NOT in the tab content (which has the file name), I have to copy this line from the file into the tab.
Now, if inside the Tab, there is a line that is NOT in the file, I have to delete this line from the tab.
I would appreciate the help. Yours sincerely
Page 1 / 1
Hi @interplan ,
If you meant “Worksheet” by saying tab, you can use the Get Worksheet names action within the Excel Advanced package to get all Worksheet names from your master excel file which will save the result into a list variable. Later, you can check the same with the sub file name through the If → List condition to see whether the name exists in the list or not. If it is available, you can use the Switch to sheet action to immediately switch to that particular sheet.
If this is working then, I don’t think the second part will be that challenging.
Good Morning, @Padmakumar
I Will try your suggestion.
I'll get back to you as soon as possible. Thanks, a lot.
Best regards
Mauro Guimarães
@interplan
Hello, @Padmakumar
Your tip worked perfectly, thank you.
Best regards
Mauro Guimarães
@Padmakumar ,
Continuing this challenge, now I need to change the color of a cell or row in Excel. Explaining:
I'm developing a task where I compare two Excel spreadsheets. If a code in a column of worksheet "A" is the same as the code in another column of worksheet "B", I need to paint the entire row of worksheet "B", or at least the cell that is in worksheet "B" . Can I do this with A360?
@interplan
Enter the name of the session used to open the workbook with the Open action.
Select the Active cell, Specific cell, or Cell range option to specify the cell in which to set the color. If you have selected the Specific cell option, specify the address of the cell in the Cell name field.
Select the item to which you want to apply the color: Cell or Text within cell.
Enter a cell color by name or code.
Use either the color name or the hex value.
Hi, @rbkadiyam
Enter the name of the session used to open the workbook with the Open action.
Me: Ok, I have Opened the Workbook wiht “Default”
Select the Active cell, Specific cell, or Cell range option to specify the cell in which to set the color. If you have selected the Specific cell option, specify the address of the cell in the Cell name field.
Me: Here, I didn't understand: Once the "Open" action is used, what is the next action to select the Active Cell, or a specific Cell or a range of cells? What "Action" should I use here?
Select the item to which you want to apply the color: Cell or Text within cell.
Me: At this point, I think it's a continuation of the previous question: what was the "Action" used so that I can apply the color to the cell or cell text. I am not finding this option. Just a reminder: I'm using the "Community version of A360"
Enter a cell color by name or code. Use either the color name or the hex value.
Me: Well, using your instructions above (even not finding a specific "Action" for it), I selected a cell with the "Select Row/Cell/Column" action and then, I used the "Set cell" action and tried to put the Hexadecimal value . The task just writes the Hexadecimal value. Then I tried the word "Blue". It didn't work either. Then I tried another Action: "Set Cell Formula". It didn't work either, repeating the previous results.
Remembering: I am using Community version of A360.
Thanks.
Best Regards
Mauro Guimarães
Hi @interplan ,
To make it less complicated, I would suggest two methods.
Excel Formatting Operations - If it not available, you can get it from Bot store. Download it as ZIP and then import it to your CR.
