How to open a file folder of current year and then look for another folder with specific date


#1

Hi,
I am needing my automation to run weekly, but there may not always be data/file to update. Therefore I am wanting the automation to:

  1. Look for the current year and open that folder (example: folder title “2018”)
  2. Then I want it to look for a folder to be dated 2 Mondays from the current date (example: if the automation runs on a Wednesday 10/8/2018, then it will then look for a file folder titled “10/22/2018”) then access the file within that folder titled “Routing Guide 10/22/2018”
  3. However, if there is not a folder dated for 2 Mondays from the current date then it will send an email “There is no data to update.”

I have tried several different options, but keep getting errors - specifically in my variable types. I can’t seem to figure out the correct one for what I am trying to accomplish. Step 2 in my above process is my main hold up. Still fairly new to UiPath and learning more and more everyday. Thank you!!


How to open Excel file when the file path is a variable
#2

Well you can do this:

  1. Get the current year[Int32] = DateAndTime.Today.Year
  2. If you are having a path where all your folders are placed, the you can use folder[Array{string}] =Directory.GetDirectories(“your_parent_folder”,year)

Now, you cannot create a folder by the name 10/22/2018 - as it dosent allow /

So, if another format say 10222018 , you still get that type using the other thread below for Next Monday after 2 weeks with little formatting.

  1. If you dont get the folder say count of array = 0 then use Send IMAP/Outlook Mail Message

If you do, use Directory.GetFiles(“folder of 10222018”, “Routing Guide”+folder".extension") to get that file.

Then you ca process that file accordingly.

Let me know if any point is confusing you.


#3

Thank you @nadim.warsi - I will be working on this in the upcoming days and will let you know if I have any other questions or issues! :grinning:


#4

Hi @nadim.warsi - I understand steps 1 and 2 along with assigning the future date from my previous post, but I am unsure of how to put it all together to execute it. Would I do an If activity?


#5

Can you tell me the folder name that you are having, 10/22/2018 is not allowed in folder name
give me an example what you have for folder name or what is expected.

Also let me know what is the file extension for “Routing Guide 10/22/2018”

Let me create a structure putting all the logic in place and ill explain it all


#6

The file will be on a network shared drive. The path is “H:\common\TRANSPORTATION\PROD Route Optimization <Monday date>” Would the format of “10.22.2018” be suitable? Or is “10222018” the only option? If so, then I will have the process owner change the folder naming format.

Also, I have a number of excel application scope activities that will need to be used to access the file within the workflow. Would the workbook path need to be “H:\common\TRANSPORTATION\PROD Route Optimization +currentYear+nextMonday” ?

Thank you again for all your help!! I truly appreciate it!


#7

In your first post you mentioned that you will look for 2018 folder and then within that 10.22.2018(this works by the way)
after that you are looking for a file Routing Guide 10.22.2018

Now i see a change in your requirement. Can you put down the steps, as im a little confused

yes you are right since your path is dynamic, you will have use variables but at the end the file_name.xlsx should be there
Actually the whole logic will give you the path which you can just use as a variable in the workbook path

What i have now as per initial logic:


#8

Sorry for the confusion…

To start the automation, accessing the network shared drive, the robot would use the path “H:\common\TRANSPORTATION\PROD Route Optimization” to then look for a folder with the current year, open that folder, then look for a folder named with the date of Monday, 2 weeks in advance. For example, if the Robot was running today it would need to look for a folder dated “10.29.2018”. It would then need to open that folder and access the file inside that would be named “Routing Guide 10.29.2018”, or the date of a particular Monday.

If the robot sees the file, then the file can be processed as needed, but if the folder with the Monday date does not exist then it will need to send an email saying “There is no routing guide to update.”

Let me know if you need any further explanation. Again, I appreciate all your help!


#9

Done :slight_smile: and i hope you are on v18.3.0 so that you can view the logic as well in the xamlfuturedate.xaml (12.3 KB)

So here is what i have done:

First the logic for next working monday is the same that we used in the other thread Future Date - Monday

  1. Get the next today date, days till next Monday, next Monday date, year.
    image

… a. Set the server root folder path (you can use config, variables, assets, whatever suits your business requirement)
… b. Do a get folders with filter ‘2018’

image

  1. Get the Monday file

  1. You can also add a if after mondayFile get fiter to check if the file is present (i have not added that here)

  2. Below you can see the values of the variable and how they all bind for your next steps:


#10

@nadim.warsi We are SOOOOO close. This is my fault, but the file name is actually “10.29.2018 Routing Guide” I thought I could switch it, but I keep getting an error. What does the Value (InArgument) need to be for the variable and variable type mondayFile? Below is what I tried, but still getting an error. :cry:

mondayFile = Directory.GetFiles(nextMondayFolder(0).ToString,+folderName+“Routing Guide.xlsx”)


#11

You just added a extra +

Directory.GetFiles(nextMondayFolder(0).ToString,folderName+“Routing Guide.xlsx”)
after comma starts the second argument so you dont need a concat :slight_smile:


#12

Got it updated, but it is still throwing an error. It is the same error that is in your xaml file, the type is not defined 'GenericValue"


#13

In my run i am not getting any error :roll_eyes:
Can you change the folderName variable type to String and try?


#14

Ok I got that error all straightened out - must of been a typo or something, reentered it and it worked just fine. But now I am getting this error message when I try to run the automation.
image

I have created folders that are dated 10.29.2018 and 11.05.2018. What exactly does this error message mean?


#15

Ok, that means we achieved what set out to do.

Now this error is due to the excel operation that you are doing after you are getting the file.
Can you tell me what you are doing with the file and how you are doing it?

Something to do with the way you are reading the file


#16

I have it opening the file using the path mondayFile(0).ToString then clicking on a tab within the file. I am using the Excel Application Scope to execute. Here is a screen shot.


#17

Hmm, not exactly.
But i need to understand what you want to do with the data in the sheet supplier?
Read and process it?

Lets do it this way. Please close this thread, for the better of the community members to relate if they have a similar question and create a fresh one with your new question.


#18

It looks like there are already some posts on what I am needing to do. I am going to read them and see if I can find the solution without having to create a new/duplicate post. :grinning:


#19

Thats the way to go :wink:

Any issues we are here :man_technologist:


#20