VBA to compare revision number between 2 excel files - 1 open & 1 closed

**kiki1989sb** · 11-30-2015, 11:00 AM

Hi everyone!

We are using templates on the network that user copies to their local computer, but every now and then we change to original file on the network, but the user forgets to copy it back to local computer.

So is it possible (vba) that when the document is opened, it checks for the original file on the network (That has Revision number 00) and if it is the same, just proceed dok do anything, but if the revision number on the template is higher (example revision number 03) then warn the user to copy the original document again to their local computer??

I only need it to check to revision number in document properties..

Thank you in advance!

**kiki1989sb** · 12-01-2015, 03:57 AM

Is it possbile that no one knows? I also searched Google, and havent found very useful links....

http://www.mrexcel.com/forum/excel-q...-1-closed.html

**LJMetzger** · 12-01-2015, 09:39 AM

Hi kiki1989sb,

I only need it to check to revision number in document properties.

There is no 'Revision' document property. I included two sample User files, one that uses a 'Built In' Document property and one that uses a 'Custom' Document Property. See the attached .zip file which contains the following files:

a. ExcelForumMasterRevisionNumberFile.xlsm - Simulates your master file. This file uses the 'Built In' Document Property 'Category' to store the Revision Number. This file also uses the 'Custom' Document Property 'Revision' to store the Revision Number. The 'Category' and 'Revision' values are different for demonstration purposes. If this file is run standalone, a message will be displayed showing the value of both properties.

b. ExcelForumCompareDocumentRevisionNumbers.xlsm - This simulates the User File and uses 'Built In' Document Property 'Category' to store the Revision Number.

c. ExcelForumCompareDocumentRevisionNumbersA.xlsm - This simulates the User File and uses 'Custom' Document Property 'Revision' to store the Revision Number.

-------------------
Both these files use Workbook_Open() to:
a. Read the Revision Number from their own file.
b. Open the Master File with Events disabled to prevent Workbook_Open() from runnning in the Master file.
c. Read the Revision Number from the Master file.
d. Close the Master file and enable Excel Events.
e. Compare the Revision Numbers and display a message if the User File is out of date.
f. If any errors occur (e.g. Master File is NOT AVAILABLE), it is assumed that the User file is current.

------------------------
Workbook_Open() in ThisWorkbook module - common to both User Files:

Please Login or Register  to view this content.

Built In Document Proerrty 'Category'
Contents of ExcelForumMasterRevisionNumberFile.xlsm ordinary code module ModCompareVersions:

Please Login or Register  to view this content.

Custom Document Property 'Revision'
Contents of ExcelForumMasterRevisionNumberFileA.xlsm ordinary code module ModCompareVersions:

Please Login or Register  to view this content.

------------------------
The 'Master Folder Name' must be changed in the above macros to reflect the location of your Master Folder. If you use an Environment variable to locate the Master Folder, the code can be changed to use the Environment variable.

To access 'Built In Document Properties':
Excel 2003: File > Properties > Summary
Excel 2010: File > Info > Properties > Advanced Properties > Summary

To access 'Custom Document Properties':
Excel 2003: File > Properties > Custom
Excel 2010: File > Info > Properties > Advanced Properties > Custom

Lewis