Iterate through files in multiple variable sub folders

[shelbypons]

Hi all,

I am trying to automate part of an invoicing process where I access specific excel files within multiple variable sub folders in order to save them as PDF.

We have one constant parent folder which contains multiple sub folders for each client (the list of clients may change month to month), and then we have the target files within those sub folders. (See pictures below.)

Please see attached process and advise on how to best iterate through the sub folders AND iterate through the files within each sub folder to then be able to access the “INVOICE” excel.

My guess is that I would need to use GetDirectory to save each of the file paths for the invoices within each client sub folder as a variable to be used to specify in the excel application scope.

I am currently having an issue specifically with assigning the files array variable to the GetDirectories of the sub folder array variable. I’m not sure how to go about translating the sub folder array variable into string (rather than array of string) to assign files array variable to it. . .

I would appreciate any and all ideas.

Thank you!

Shelby

Iterate Through Files.xaml (14.1 KB)

[chenderson (Cary Henderson)]

You can use a single assign stage to set a variable (array of strings) equal to the following:

Directory.GetFiles(targetDirectory, “*.txt”, SearchOption.AllDirectories)

Where targetDirectory is the variable containing your parent file path.

[Dave (David Wiebelhaus)]

A couple things here. First of all, I would caution against having the free-form input for the folder path. That’s a great way to get a bunch of errors. I would instead utilize the select folder action. However, instead of using the built-in activity I would create a FolderBrowserDialog variable and set the default value to new FolderBrowserDialog - I’ll call this variable folderSelect. Then in an assign activity assign folderSelect.SelectedPath = "C:\yourpath\whatever\Financials\Acc Rec - this will make the dialog box start here by default. Next, use an Invoke method activity. The TargetObject should be folerSelect and the MethodName should be ShowDialog - this will have a pop-up appear to the user so they can select the folder that they want (in your example the user would navigate to 08 - Aug’19 AR\Invoices Prepped). You’ll want to put some error handling in here to make sure they chose a fodler and clicked ok and not just ‘cancel’ After they click ok, you can access their selection with folderSelect.SelectedPath

So now you have the parent directory. You want to search the parent directory and all subdirectories for an excel file called “INVOICE.xlsx”. This can be done using the Directory.GetFiles method. Create a variable (of type string array) i’ll call arrFiles. Assign arrFiles = Directory.GetFiles(folderSelect.SelectedPath,"*INVOICE.xlsx",SearchOption.AllDirectories)

Now you have a string array with containing the full file path of all files ending in INVOICE.xlsx within the directory the user chose and all subdirectories. Use a for each activity to iterate through each of the files. Make sure to change the TypeArgument to string and supply arrFiles as the Values. You can name it whatever you want, but i’d call it file myself. So it would say For each file in arrFiles. Now the variable called file is a string variable containing the full file path. Do all your individual processing in this for each loop

[Dave (David Wiebelhaus)]

I attached a .xaml to make it more clear what I was talking about - let me know if you have any questions

GetAllInvoices.xaml (13.2 KB)

[shelbypons]

Awesome!! Yes, that one line helped to get the names of each invoice from each of the sub directories. (I checked by using a message box within a for each, which displayed the file paths for each of the Invoice documents within the various client sub folders.)

I knew there had to be a simpler way, but I’m not very well versed in the different functions you can use, so I was doing it the extra-long way with nested for eaches. Still learning!

Directory.GetFiles(parentFolder, “INVOICE*”, SearchOption.AllDirectories)

[shelbypons]

Thank you so much for helping with the parent folder determination! That was going to be a whole separate post after I figured out my sub folder iteration issue.

The SearchOption.AllDirectories line was definitely the key solution I needed to keep moving forward.

Thanks!

[chenderson (Cary Henderson)]

That’s great news!

I was doing for each + for each + for each on my first solution. I didn’t get to the one liner (or even know it was possible) until about 3-4 iterations later!

Thank you for the challenge Shelby!