Compare two excel workbooks - locate missing worksheets

Hi all,
I have two excel workbooks.
As part of a bigger automation there is a step whereby I want to get all the worksheets in Workbook1 and compare them against a list of all the worksheets in Workbook2. If a sheet from 1 doesn’t exist in 2 then I want to write that sheet to an output file.

How would I go about comparing the two worksheet lists?

When you use excel scope, you have an optional output variable called Workbook, out of that variable to retrieve all the sheet names you can use myWorkBookVariable.GetSheets

2 Likes

Good morning Tony,

You can use the “Get Workbook Sheets” activity to return a list of worksheets in each Excel workbook.

Once you have both lists, you can use a “For Each” activity to loop through one of the lists to identify which values are missing.

If a record if missing, you can write the sheet name to your output file while still in the loop.

1 Like

Thanks for that.
This is the area where I am having a little trouble.
What activity or expression would I use to compare those two sheets?
How will it determine what is missing?

1 Like

My apologies Tony, it looks like it may be even simpler than the solution I suggested.

Please check out this solution and let me know if it works for you:

1 Like

the above would work if your are sure array1 is always bigger than array 2, so make sure you get this part right :slight_smile:

1 Like

Hi,
No the arrays can vary in size.
Either workbook could have more sheets than the other.

At the moment I can do comparisons between the the contents of cells in either workbook but struggling to figure how to just compare the actual worksheet names.

I’ve been trying the attached suggestion which doing it by trying a read range and if the sheet doesn’t exist in workbook2 then it will write it out, but I’m getting a validation error.

Check for missing spreadsheets.zip (29.1 KB)

It’s ok, I’ve resolved this, in the file I attached above I needed to move the If statement up in to the Catch.

Thanks to those who contributed.

1 Like