0
Mark Hurd
Posted 2015-05-30T10:37:02.487
Reputation: 379
Merge multiple Personal.xlsb
Answers
1
One roundabout way would be to save all macros for each spreadsheet to corresponding text files and compare the same using diff/merge utilities such as WinMerge.
If you have a large number of spreadsheets then this VBA code or this VBScript will help reduce manual labour.
There's also a purpose-built VBA diff utility called VbaDiff you can try that "will mark modules that have code changes in red and modules that are not in both projects in blue":
Karan
Posted 2015-05-30T10:37:02.487
Reputation: 51 857
Yeah, I was afraid of that. That was the approach I saw, but I've accumulated 16 of them :-( I do expect only two or three have different details, but which ones is still a bit hard to do manually. – Mark Hurd – 2015-05-31T03:27:57.767
1
Unless there's some sort of diff/merge utility that understands the XLSB format (and I don't think MS has ever described the binary format properly) and can compare the text directly, you might not have a choice. One way I can think of to reduce effort would be to write yet another macro or a stand-alone VBScript that iterates through all the files and does the work of extracting the macro code for you to diff/merge. Something like this or this for example.
– Karan – 2015-05-31T03:40:30.957FYI I've used the second link from your comment, with WinMerge, if you want to add that to your answer. I just had to "Trust access to the VBA project object model" in Trust Center > Macro Settings (and perhaps "Enable all macros", which I did just in case). Of course, I have remembered to restore these settings afterwards :-) – Mark Hurd – 2015-05-31T09:17:53.370