Verifying File Hashes?

Hello all,

I need to verify files MD5, SHA1 ,SHA256, etc. hashes using Excel/VBA. I will have all files in a given folder and need to loop each file and get its hash. I will compare this value to the hash the site the file came from claims it should be. In greater context I download a bunch of offline installers for various programs and need to verify their hashes against the hash given by the website. I am not interested in getting Excel/VBA to compare for me, only in getting the hash from the file.

Outside of VBA, I could use the following cmd command to do this:

Please Login or Register  to view this content.

IE:

Please Login or Register  to view this content.

the resulting string returned by the above command is what I need.

Bonus points if we can do this as a function I can use in cell, something like 'GetHashSHA256(Path, Filename)' or 'GetHash(Path, Filename, Type)' where 'Type' = SHA256, MD5, etc.

Double bonus for including links to documentation for the method you use.

What I have so far is this: https://en.wikibooks.org/wiki/Visual...Hashing_in_VBA

I get the automation error as it seems to require a specific version of .NET which isnt an option for me. I have Win 7 and Excel 2016 (not O365). For what its worth I am a fan of late binding and prefer not to have to reference libraries if possible to avoid.

I am not including a sample as whats on sheet doesn't really matter, its just VBA and getting the result. Feel free to hard code the filename and path, place it in a cell, prompt, etc. The result can be a msgbox, debug.print, in a cell, etc. However its done, as long as the returned hashes are correct, I can re-purpose it to work the way I need.

Thanks

All my attempts at doing this direct in VBA, VB script and even PowerShell failed. I would get errors seemingly linked to not having the right .NET version or issues related to security policy enforced by Group Policy.

I finally started working on a way to do it using a batch file. I was able to get a proof of concept working to give me the result using CertUtil. I then figured, instead of a batch file maybe I could execute the command from Excel and store the result.

I now have a proof of concept working for this. I haven't scaled it out yet to loop multiple files. The only real challenge with that would be attempting to hide the cmd prompt that flashes onto the screen for a split second, as in a loop it would do so a bunch of times.

I will post the code back here once I have ironed it out and commented the code. I wanted to mark this solved and post this to ensure no one else wastes time on this. Thanks

I have a routine to calculate CRC32 for files, which I find convenient because it can be matched to that shown in .zip files. I can post if you're interested.

@shg Absolutely, anything could help. I would be interested to see it.

Here is the sample code for my proof of concept using CertUtil -hashfile from shell. Change constants at top for desired path, filename and algorithm. Output is shown in the Immediate window of VBE (Ctrl+G or View | Immediate Window). Just run the Test() sub and it will spit out the same hash as the CertUtil command in cmd.exe.

I have included a function to test that the file at its full path exists prior to running. This will display a command prompt window briefly (a split second). From what I understand this isnt easy to avoid as using .run instead of exec results in not being able to get stdout. As is, this code is meant only to prove it can be done and likely needs some modification to make it useful on a set of files.

Code could not be posted direct in the post so I am attaching a txt file with the code.

Here 'tis.

Please Login or Register  to view this content.

See my thread here

It only does CRC32 checksums though.

(I did consider adding MD5 to this (by researching the algorithm on the net then retyping it in VBA) but I dropped the idea as there are free stand-alone executables out there that can calc hashes quickly and easily. For a batch of files, I now use a free program HashMyFiles)

You could try the API code here: https://gist.github.com/jermity/b12e26bc1adb38840099

I believe those were recently deprecated, but should still be available.

@rorya
Thanks for the link
Can you give us example of how to use such functions ..?

Just call whichever function you want to get back the hash - for example:

Please Login or Register  to view this content.

Thanks a lot Mr. Rory
I can get the hash for string and files .. What about this UDF? What is it for?

Please Login or Register  to view this content.

It creates a hash for an array of Bytes. (you can load strings into byte arrays)

Thanks a lot
Do you mean array of bytes like that (variable x)

Please Login or Register  to view this content.

If so how to use the function .. just refer to x ?!!
Is that right?

Please Login or Register  to view this content.

But when comparing using both methods I got different results for both for the same string "Hello"

Please Login or Register  to view this content.

Thanks .. I solved the last point by changing 'vbUnicode' to 'vbFromUnicode'
Thank you very much for great help

Originally Posted by rorya

You could try the API code here: https://gist.github.com/jermity/b12e26bc1adb38840099

This seems promising. If its deprecated did some other dll/API replace it in Windows?

For now I have found a viable solution for my use.

I modified my approach from post #4. The essence is the same, I am simply using Windows shell to run the CertUtil -hashfile command and then parsing its output into Excel.

What I have changed is doing it in such a way that the command prompt does not appear. I am now using .run instead of .exec with 'cmd /c CertUtil -hashfile path algorithm' I am piping the result into a temporary text file in the temp directory, then opening the text file and putting the results into an array (then deleting the txt file) and splitting just the hash out as the result.

I am using it as a sheet function/UDF as I dont know what hash algorithm will be needed until I manually enter it in another cell. Otherwise I would likely not implement this as a function and instead a sub that puts all the hashes into an array. As CertUtil has consistent output it would be better at scale to process all hashes, appending them into a single text file, then parse out the single text file to an array.

This may not be the most elegant approach, but I am dealing with relatively small sets of files (~20) and I need it to work in my environment. Group policy seemed to prevent Powershell from working as an option (due to GP settings regarding crypto) which could possibly affect attempting to use Windows API calls too.

Thanks for all the input.

The Cryptography Next Generation (CNG) API functions have replaced it - see here for more info: https://docs.microsoft.com/en-gb/win.../cng-reference

To the best of my knowledge, you can't block API calls using Group Policy. If you could, it would possibly stop Windows from functioning.

Originally Posted by rorya

To the best of my knowledge, you can't block API calls using Group Policy. If you could, it would possibly stop Windows from functioning.

True but it can prevent aspects of them from working. For example I cannot use "Security.Cryptography.HashAlgorithm" in Powershell due to a GP settings that I cannot change (I am able technically, but cannot as I shouldnt change it). Maybe its not the same thing, but if the API is using built in Windows functions/methods/etc. then its possible this GP could block it as well.

In any case, someday I will have to read over the API and try it out. Thanks

That's not an API call. It's a .Net framework wrapper (although that almost certainly calls the API under the hood).