Solved

I have two challenges with Excel spreadsheets and I need help.

  • 18 February 2023
  • 9 replies
  • 186 views
interplan
Userlevel 1
Badge +7

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

icon

Best answer by Padmakumar 27 February 2023, 07:26

View original
Mauro Guimarães

9 replies

Padmakumar
Userlevel 7
Badge +13

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. 

Padmakumar
interplan
Userlevel 1
Badge +7

​
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 

Mauro Guimarães
interplan
Userlevel 1
Badge +7

Hello, @Padmakumar 

 

Your tip worked perfectly, thank you.

Best regards

Mauro Guimarães

Mauro Guimarães
interplan
Userlevel 1
Badge +7

@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?

Mauro Guimarães
rbkadiyam
Rank
Userlevel 5
Badge +14

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

Ramesh B Kadiyam
interplan
Userlevel 1
Badge +7

 

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

Mauro Guimarães
Padmakumar
Userlevel 7
Badge +13

Hi @interplan ,

 

To make it less complicated, I would suggest two methods.

or

  • Use any inline scripts. E.g.: VBScript
Padmakumar
interplan
Userlevel 1
Badge +7

Thank you, @Padmakumar 

 

It worked well, as a package.

Best Regards

Mauro Guimarães

Mauro Guimarães
P
Badge +4

I don´t understand why i get this error:

Excel format PKG (Bot Store)

anyone can help me??

Thanks,

 

Reply


Terms & ConditionsCookie settings