How to get a vb code from excel file

Help Studio
, , , ,
1

I have created a table in an excel file, and the table has a customer column, date column, and date code column.

Customer Date Date code
CustA Fri DateTime.Now.AddDays(-1*(7 + (DateTime.Now.DayOfWeek - DayOfWeek .Friday)) mod 7).Date.ToString(dd)
CustB Sat DateTime.Now.AddDays(-1*(7 + (DateTime.Now.DayOfWeek - DayOfWeek .Saturday)) mod 7).Date.ToString(dd)

The robot first reads the excel file, and then loops through the datatable.
I am trying to put the code from the Date code column to a field on a website so that I can get a date dynamically.
If CustA, put the previous Friday’s date . If CustB, put the previous Saturday’s date and so on.

I want to get a date like this:
image
But to access an item in the current row inside the loop, toString must be added to CurrentRow.Item("Date code") , and it makes the code a string, so doesnt work :frowning:

I can put an if activity inside the loop to determine a date, but I’ll have to put all the possibilities e.g. if custA, use this code, if custB, use this code… I want to avoid enter each customer when a new one is added.
I want to make it versatile.

How can I achieve this?

2

What does “doesn’t work” mean? That doesn’t tell us anything.

Web sites always take input as strings. Having to put .ToString on the end is probably not the issue.

3

Is this literally what you have in that column of the spreadsheet? That’s not going to work. You can’t process an expression like that on the fly. You need to do that calculation in your UiPath code, not as the value in the spreadsheet.

2 Likes
4

Hi @yesterday - So, is the bot not able to type the date in website. Does it showing any error message? Can you please print the date value in message box and see what it is giving

5

He is trying to store his vb expression (which calculates the date) in the Excel file, thinking that when UiPath reads the file it’ll get the actual date. It won’t.

6

Yes as my fellow community members tell you, you cant use the expression in excel and assume that the type into activity will input the result. You need to complete the logic of what you wrote in UiPath not excel.

2 Likes
7

I see. Got it.
Do I have to do “if statement” to assign the code??

8

Can you explain what the expression is trying to calculate?

9

The expression gets the last date. For instance

DateTime.Now.AddDays(-1*(7 + (DateTime.Now.DayOfWeek - DayOfWeek .Monday)) mod 7).Date.ToString(“MM/dd/yyyy”)

shows the last Monday.

What I’m trying to do is that when the robot executes the process, it reads the excel file that has the table above. Then it loops the datatable and, inputs a date in a field. That date is provided by the expression and is depending on the customer.
I assume customer will be added in the future, and the date will defer by customer. That is why I want to use the excel file.

I want to achieve this without hardcoding to a robot.

please let me know if you have any questions.

10

Got it. Thank you.
I do not want to put all the possibilities assigning the expression for all dates, but Is it the only way?

I feel like there is a smart way to do this.

11

It looks like the only difference in your expression is the DayOfWeek.day part. So create a variable to store it in, use a Switch based on the value of Date (ie Fri, Sat, etc) to populate the variable with the correct DayOfWeek value, then replace DayOfWeek.day with this variable.

That way the only thing you’re making a decision on is the Date (Fri, Sat, etc) and the rest of the expression doesn’t have to be repeated.