Copy Yesterdays date into column in excel

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

@sparkplug93

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

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

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

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

1 Like

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

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

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

It’s also opening .xlsx

1 Like

here you go
xls.zip (20.8 KB)

Cheers @sparkplug93

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

here you go
spark.zip (2.8 KB)

Cheers @sparkplug93

Will this append the data after every report is collected

It isn’t doing anything

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

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

1 Like

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

Cheers @sparkplug93

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

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

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