Best approach (or suggestion) to open multiple excel files from two different folders?

Help
, , ,
1

Hello,

I have 2 different folders, each of them usually with the same Excel files inside it. However, depending on the folder where the Excel files are saved I need to perform different actions like formating cells to copy them to an Outlook eMail.

My question is regarding to what is the best logical approach you can suggest for this sort of task?

I mean, doesn’t make sense to have an Excel application scope to each file so… maybe a For each with an Excel application scope where the workbook path would change? Basically, what’s the suggested approach for anyone who faced that already?

Regards.

2

I’m not entirely sure if you want to open the Excel files simultaneously, in that case make sure you properly use the Output Workbook to ensure you perform tasks on the right workbook.

In any case, if you want to perform activities such as Set range color, you have to use these within an Excel Application Scope.

When you want to determine which files are in your directory, use the Invoke Code activity and use this VB.NET Code to retrieve an array with all the files in that path: arrayFiles = IO.Directory.GetFiles(PATH)

PATH should be an In Argument and arrayFiles should be an Out variable. Then, use a For each loop to loop through the files. In case there are also non-Excel files, make sure you evaluate the file type. Hope this helped!

1 Like
3

See example.

Demo_Open_Excelfiles.zip (15.5 KB)

1 Like
4

Hi @basvanderpol,

Thanks for the tips and many thanks for taking the time to make such an example. I think your idea will be helpful.

While waiting for an answer on the forum, I thought about making two “For each” activities for each folders where I would then do a Directory.GetFiles(path) and then inside, I would do a bunch of Ifs or a Switch regarding the needeed operations on a certain Excel file. I think I will have to do many Excel application scopes for each files because of different cells to format.

Also,one little question on your example: Why using Array and not a List?

Obs: I don’t think I would need to open the Excel files all at the same time. I think it will be something like, opening the file, make the formats, copying the stuff to the eMail and closing the file. And so on so on…

1 Like
5

Hi @moreirasa1

No problem, great to hear that it benefits your automation journey!

Your strategy seems to be fine in my opinion. Just note that when you start nesting if’s in a sequence, you’ll quickly lose the overview. In case of nesting if’s, I’d advise to replicate a For each function on flowchart level.

Regarding array instead of list. I would indeed use a List<T> instead of an Array. Especially as it offers more functionality than an array. Great point @moreirasa1.

6

Hi again @basvanderpol,

Yeah, I guess you are right. Thanks for the suggestion in relation to the If statements.

As for the List instead of Array, indeed I prefer a List because it is more dynamic. However, if I change my example inspired by yours to a List of Strings instead of an Array of Strings I get an error. Would it be because Directory.GetFiles only work with array types?

Cheers.