UI automation in excel

Help
, ,
1

Hi All

I have an excel where one column is a date column. The format of the date is long date, but requirement is to make it short date. The manual process that team is following is select the whole column and in Home-> Number section from the drop down select the Short Date.

I am doing the Home-> Number-> Short Date using Send Hot key or Type Into, Alt+hns+Enter.It used to work but now its not working. Not sure why.

What is the other way to do this ?

1 Like
2

HI @kkpatel

The other way without using UI automation would be to use a read range to read the data into a datatable and do the data conversion there in the datatable itself. You can use a for each row activity as well to loop through the rows and convert each row as needed. At the end, you can do a write range to write the new data back to the excel file.

3

Yah this would for sure
Hope these steps would help you resolve this
—use a START PROCESS activity and pass the filepath of excel as input to the property FileName

—this will open the excel file to foreground of the screen
—now use a GET ACTIVE WINDOW activity and place these hot keys with SEND HOT KEY ACTIVITY
But ensure that you have mentioned only the hot keys in send hot key activity and no element is chosen for selector, just the hot key alone

This would work for sure

Cheers @kkpatel

4

Hi @kkpatel,

Here is an activity called “Cell Type”. It is a single activity that to change the cell format…

For the short data you can specify the format as “M/dd/yyyy”

Regards
Balamurugan.S

1 Like
5

It will take a lot of time because the file contains more than 300K rows. Whereas if i do change through the above method its instant.

6

I didn’t get. Just the hot key alone means ?

7

Yes got it.

1 Like