Comparing two workbooks

[gheyman]

Looking for recommendations on the best path forward.

I have a workbook template that is sent out to the customer to complete (note that the template cannot contain any macros).

The template will have data in it when we send it to the customer. The customer will then complete certain fields in the template and return the completed template. (also not we cannot put anything on the cloud, website...) we need to send a template and have them send it back.

I want to compare the workbook that I sent to the one that is completed and sent back. I am not only looking to see if they completed the necessary fields (which are on more then one tab) but also looking to see that they did not change certain fields (which I cannot lock).

One of the things I also want to check is that they did not change the tab names and I want to know if they added any tabs.

The "Validation" workbook I am looking to create can use VBA.

I was thinking that in the beginning I get a pop-up asking me to identify the two workbooks to compare. Something simular to:
FileName = Application.GetOpenFilename _
(filefilter:="Excel files (*.xl*),*.xl*", MultiSelect:=False)
If FileName = False Then Exit Sub
Set MaterialEstBook = Workbooks.Open(FileName, ReadOnly:=True)

' *** This will put the address of the opened file in Tracker sheet cell C4 ***
ThisWorkbook.Sheets("Tracker").Range("C4") = Application.ActiveWorkbook.FullName

Obviously since I am comparing two workbooks, I would need to be able to do this twice to get each workbook.

then potentionally using indirect formulas (using the workbooks names/location) to compare whats in certain cells. Also worth noting that some of the data I am comparing is in tables on certain tabs. So since the table size is an unknow, I dont know if I should copy in each table from each book and then do a comparison.

I have never created a workbook to do a comparison like this, so I am looking for best practice / suggestions.

Thank you for your time.

 

[MountainFog]

You can enable the Microsoft Add-in "Inquire" and it will do exactly what you want.

Go to File->Options->Add-ins->Manage COM Add-ins->Go Then check Inquire

 

[gheyman]

Thanks, that's one consideration. Hoping for another since this may be too complicated for some user (unfortunately).

 

[MountainFog]

If you use inquire, you can click the "compare workbooks" button in the ribbon. It will be much less complicated than a VBA solution.