ExcelHelp

Help Studio
1

What activities can I use to edit a column on an excel sheet? I am trying to change a entrie column from displaying date and time to just displaying the date .

2

  1. Use Read Range to read the column into a datatable. You could optionally read the entire sheet, which will make defining your range simpler.

  2. You can change the date in a For Each Row loop and using an assign activity to set row("Date") to Convert.ToDatetime(row("Date")).ToString("yyyy-MM-dd"). Feel free to rearrange the format of yyyy-MM-dd to suit the format for the date that you want to see. "Date" is whatever you’ve named the column with the date in Excel.

  3. Use Write Range to write the data back from where you read it. The range you specified in step 1 will be the same here.

1 Like
3

is this just for display purposes only?

If do, simple do as you would in the front end of Excel.

Use Excep Application Scope to open excel
Select the column you want to amend
Set Format Type to Date - click on format list and click short date.
(if you want to apply a specific date format then you can go to Custom and enter your specific format there)

4

Thanks, I am only trying to edit 1 column. If I use the For Each Row I don’t think that will work. Is that accurate?

5

It will still work. You just need to specify the range of the column you want to change. It will also work if you read all of the data in and write it all back to the workbook, since you’re only changing one column and the rest just gets put back where it was.

6

So if the column is Column F my range for the Read Range would be $F:$F? Correct or what is the proper formatting.

7

You could use that, but make sure to use a Filter Datatable activity after the Read Range activity and remove the blanks. That way your For Each Row loop will only deal with the non-blank data in the column. However, if the amount of data is exact (say 50 rows), you can specify $F1:$F50 and not need to use Filter Datatable.

8

I am getting an error when setting up the “Assign” activity

image
image1498Ă—782 56 KB
. Can you please advise?

9

It is a process that is ran daily. what activities would I need to follow your steps.?

10

The expression needs to be on the right side. Put row("Termination Date") on the left and Convert.ToDatetime(row("Termination Date")).ToString("yyyy-MM-dd") on the right.

11

image
image1489Ă—740 48.1 KB

I have Termination Date on the left hand side however I am still getting an error. Does it need to be a variable ??

12

Check my previous post again. Put exactly row("Termination Date") as I have written it.

13

Excuse the mistake, I corrected it. Now I am getting this error. Does Termination Date need to be a variable.

image
image1176Ă—543 21.8 KB

14

Hi @bagayoko.isaac.b,

here is an activity called change cell type and you can specify the range as like “C:C” .

Video :

Regards
Balamurugan.S

1 Like
15

I’m glad to know more about this proceedings. Thank you all!

1 Like