Copy Yesterdays date into column in excel

#1

Hello,

I’m collecting an excel attachment from an email and want to add yesterdays date next to every value in a column then stop. Thanks

#2

@sparkplug93

Could you please tell more details about the issue. Where you got stuck ?

#3

For yesterday u can use
Date.Now.AddDays(-1).toString(""dd-MM-yyyy)
If you want to write it in any column then By using read range you can read all data in excel and save it in variable as Datatable
then by using for each row assign Date.Now.AddDays(-1).toString(""dd-MM-yyyy)
in that column say
Row(“Yesdate”)=Date.Now.AddDays(-1).toString(""dd-MM-yyyy)
note : here “Yesdate” is column name in which u want to write datwe

#4

Fine
hope this step would help you resolve this
–once after getting the attachment of excel file use EXCEL APPLICATION SCOPE activity and pass the filepath of excel and inside the scope use READ RANGE activity and get the output with a variable of type datatable named dt
–now use a FOR EACH ROW activity and mention the input as dt
–inside the loop use a ASSIGN activity like this
row(“yourcolumnname”) = Now.AddDays(-1).Date.ToString

Cheers @sparkplug93

#5

What if the column header does not exist? is that what date

1 Like
#6

we can mention the column index
but i dont get that buddy ,…can you elaborate a bit more pls
Cheers @sparkplug93

#7

Sorry, one more question. I have a .xlsx and .xls file but only want to open .xls, how do I do that

#8

Fine
use a assign activity like this
arr_filepath = Directory.GetFiles(“yourfolderpath”,"*.xls")
where arr_filepath is a array of string variable which will be having only xls file path in it

Cheers @sparkplug93

#9

It’s also opening .xlsx

1 Like
#10

here you go
xls.zip (20.8 KB)

Cheers @sparkplug93

#11

I am looking to read each file, add a date column and append it to a sum log but nothing is happeningappend Data Pull.xaml (8.4 KB)

1 Like
#12

here you go
spark.zip (2.8 KB)

Cheers @sparkplug93

#13

Will this append the data after every report is collected

#14

It isn’t doing anything

#15

Fine
can i have a sample excel file
and are we trying to create a new column date or it will be there already and we are trying to insert yesterdays date alone for all rows

Cheers @sparkplug93

#16

New date column that at the end of each row to insert yesterday’s date

1 Like
#17

Fine
i got it
hope this would help you
spark.zip (20.6 KB)

Cheers @sparkplug93

#18

Nothing is happening when it runs. I did change the path and the file is .xls not .xlsx

#19

Hmm it’s been long time, I forgot
Where these steps included and ensure that if so we need to have a xls file in that folder

—use a assign activity like this
arr_filepath = Directory.GetFiles(“yourfolderpath”.””*.xlsx“)
Where arr_file path is a variable of type array of string
—this array will contain both xls and xlsx file
—now use a FOR EACH activity and pass the above arr_filepath variable as input and change the type argument as string in the property panel
—inside the loop use a IF condition like this
NOT item.ToString.Contains(“xlsx”)
If this condition is true it will go to THEN part where we will be getting only xls files which can be process with EXCEL APPLICATION SCOPE
Or
goes to ELSE part where we can leave it empty

Cheers @sparkplug93

#20

Perfect, thanks. Now when I run the workflow, it adds a column for every time it’s run. IE there is a column titled Column 1 and Column 2. How do i not let it do this