1

I don't want to know WHAT are the differences, I just want to know "Y/N Are these sheets identical?"

Unfortunately, superficially Hashing the file doesn't answer that :(

Specifically ...

  • I took an .XLSX file, and file-copied it.
  • Compared hashes ... Hashes were the same. Yay!
  • Opened one file, clicked on a cell, saved & closed file.
  • Compared hashes ... Hashes were different. Boo!
  • Opened both files, selected the same cell in each file. Saved & closed files.
  • Compared hashes ... Hashes were still different. Boooooo!
  • Deleted one file and re-copied the remaining file.
  • Compared hashes ... Hashes were the same. Yay!
  • Opened one file, didn't touch anythign at all!, saved & closed file.
  • Compared hashes ... Hashes were still different. Boooooooooooooooo!

So, evidently Excel just doesn't save the file in a stable manner. (or the has includes the last-saved date?)

Is there any way to get a stable hash, based on the cell contents of a sheet?

Brondahl
  • 7,402
  • 5
  • 45
  • 74
  • For reference, I was using PS `Get-FileHash` to do my hashing: https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/get-filehash?view=powershell-6 – Brondahl Sep 22 '19 at 08:33
  • As soon as you save the file, you are changing the modification time, so the hash is changing. As the Hash is working, as soon as you change anything on the file (even modification time), the Hash will change. It is working as predicted. This utility is useful just to check copies of the same document, so you can not falsify it – David García Bodego Sep 22 '19 at 09:54
  • Check `https://www.ablebits.com/office-addins-blog/2016/02/25/compare-two-excel-files-sheets/` – David García Bodego Sep 22 '19 at 09:55

1 Answers1

0

One option would be to use a macro to check the sheets. If the result of the counter is greater than 0 then the sheets are different.

'~~~> Set the counter to 0.
i = 0

'~~~ Begin a double loop.
For r = 1 To SourceR

    For c = 1 To SourceC

        '~~~> Compare each cell in the two sheets.
        If SourceWS.Cells(r, c) <> TargetWS.Cells(r, c) Then

            If SourceWS.Cells(r, c) <> SourceWS.Range("CheckForUpdates") Then

                '~~~> Increase the counter by 1.
                i = i + 1

            End If

        End If

    Next c

Next r
Jenn
  • 612
  • 1
  • 4
  • 7