|
These Visual Basic for Applications (VBA) macros are intended to help users
on PCs and Macs avoid conflicted copies when accessing shared files using
Dropbox. Conflicted copies occur when Dropbox detects that a shared file
has been simultaneously edited by more than one user.
These macros check-out a file to a user by recording information in
the file's built-in properties and the file is then promptly saved and
synchronized through Dropbox. The user's name is stored in the file's Manager
built-in property. The name of the user's computer is stored in the Company
field built-in property. Check-out/check-in dates and times are stored in the
Comments field built-in property.
If another user attempts to open a file that has been checked-out to someone
else or to that user on a different computer, the macros will display a message
that the file is currently being edited and the name of the user and computer
will be displayed.
When the first user completes editing the file and closes it saving changes
(i.e., checks it in), the Manager and Company built-in
properties are set empty and the file is promptly saved and synchronized through
Dropbox thereby allowing someone else to check it out.
If a user checks-out a file and then closes it without saving changes, the
changes are discarded but the file remains checked-out to that user. The user
must reopen the file and close saving changes to check-in the file. Similarly,
if a user checks-out a file and then uses File Save As to create a copy
of the checked-out file, the original file will remain checked-out to that user
until that user reopens the file and closes it saving changes.
The requirements for this to work are that the collaboration macros must be
added to each shared file, all the users must enable macros and must be on-line
whenever they are accessing shared files, and the Manager, Company,
and Comments fields in the files' built-in properties must not be
modified by users.
A detailed file listing in which the Comments column is included will
show in that column which files are currently checked-out, when they were
checked-out, and when the other files were last checked-out and who checked them
out. This works properly for .XLSM and .DOCM files. File listings for .XLS and
.DOC files show the date and time of latest check-out and who checked them out
but often don’t show any check-out information for files that are currently
checked-out. Below is part of a Windows XP folder listing showing the
Comments column. In this case, all the spreadsheets are checked-in except
content1302.xlsm. Spreadsheets that don't contain the macros or that haven't yet
been checked-out since the macros were installed have a blank Comments
field.
System Requirements
Office 2010 on a PC or Office 2011 on a Mac. May work with earlier
or later versions of Office on PCs. Office 2008 for the Mac does not support VBA,
which is
required to run the macros.
Remaining issues
- Off-line use. If someone is not connected to the Internet, The
macros will not be able to keep track of which files are currently
checked-out and so a user may start editing a file only to discover later
that someone else has edited the same file.
- Near-Simultaneous Opens. In the event two users open the same
file at just about the same time, both may be able to check-out the file.
When a user checks-out a file, there is a delay of several seconds while
Dropbox synchronizes the file across all the users that are on-line. During
that time, another user may be able to check-out the same file. In that
case, a conflicted copy of the file will be created by Dropbox within a few
seconds and hopefully that will be noticed before much editing is done by
either user.
- Works only for Microsoft Excel spreadsheets and Microsoft Word
documents. Files of type .XLSM or .DOCM are preferred as discussed
above.
- File Save As confuses the macros. If a user check-out a
file and then use File Save As to save it under another name, the
file under the new name will be checked-out to that user but the original
file will not automatically be checked-in. To check-in the file, the user
must reopen it and then close it saving changes. If someone has
inadvertently left a file checked-out (e.g., and then gone on vacation), any
user can force it to be checked-in by opening it and deleting the contents
of the Manager field in the file’s built-in properties.
- Mac computer name. When running on a Mac, this version of the
macros always shows the computer name as "Mac."
The Macros
Disclaimer: This software is provided as-is with no
explicit or implicit warranty of fitness for any purpose. Use it at your own
risk.
The macros below are for shared Excel spreadsheets and Word documents.
For full functionality, the files should be .XLSM or .DOCM although most
features work with files of type .XLS or .DOC (see comments above about file
listings).
Excel spreadsheets
 XLSCO2.0.TXT
- This text file contains the collaboration macros for Microsoft
Excel spreadsheets.
Word documents
DOCCO2.0.TXT
- This text file contains the collaboration macros for Microsoft Word
documents. Note that to maintain Mac compatibility, these macros lack a BeforeSave
event handler.
Installation
After downloading the files, install the macros by using the VBA Editor
to copy the entire text into the ThisWorkbook Excel Object in
the Excel spreadsheets you want to share and the ThisDocument Word
Object in the Word documents you want to share. To remove the macros, delete them
from the ThisWorkbook and ThisDocument objects in the files
where you
installed them.
Windows step-by-step spreadsheet macro installation
- Open the XLSCO2.0.TXT containing the spreadsheet macros and copy them to the
clipboard
The TXT file will typically open in Notepad.
- Use Excel to open the spreadsheet in which you want to install
the macros
- Open the VBA Editor (VBE)
The easiest way to do this is to type Alt-F11. If the
Developer ribbon is displayed, you can also get to the VBE by
clicking on the Visual Basic icon. If the Developer ribbon
is not showing in Excel, you can enable the Developer ribbon by
going to the File ribbon, selecting Options, selecting
Customize Ribbon, and putting a check mark next to Developer.
- Open ThisWorkbook for the spreadsheet
You may need to click on the "+" sign to the left of the
VBAProject item for the spreadsheet, then click on the "+" sign to
the left of the Microsoft Excel Objects folder, and finally
double-click on ThisWorkbook. That will open a code-editing
window to the right that has two dropdowns at the top showing
"(General)" and "(Declarations)."
- Paste the macros into ThisWorkbook
Click in the code window and paste in the macros.
- Save the spreadsheet
You can type Ctrl-S or click on the File menu in the
VBE and then select Save.
- Exit the Visual Basic Editor
You can type Ctrl-Q, click on the X on the upper right
corner of the VBE window, or click on the File menu in the VBE
and then select Close and Return to Microsoft Excel.
- Close Excel
If the message "Please close the spreadsheet without saving changes"
is displayed, ignore it, and, if asked, save the changes.
- Test the spreadsheet
Open the spreadsheet. If asked, click on Enable macros. A
dialog box should be displayed that asks if you want to check-out the
file.* Click Yes. If you have another computer with access to the
same spreadsheet in Dropbox, use that computer to open the spreadsheet.
You should get a message that the spreadsheet is being edited showing
your log-in name and the name for your other computer. Make some
insignificant change to the spreadsheet and close Excel. A dialog box
should be displayed that asks if you want to save changes. Click Yes.
Excel should exit and a folder listing that shows the Comments
column (see above) should show your log-in name
and computer name associated with the file. Open the file again,
preferably on your second computer if you have one, reverse the
insignificant change you made, and exit saving the file.
The sequence for Word documents and using Macintosh computers is very
similar to the above.
* If the check-out dialog box is not displayed, it is likely your copy of
Excel is set to disable macros without notification. On the Developer
ribbon (see step 3 above if that Ribbon is not available), click on Macro
Security. Select Disable all macros with notification or
Enable all macros, the latter being less desirable because running
macros from an unknown source can be a security risk. There is more
information from Microsoft posted
here.
Variants
It is likely that variants could be developed for Microsoft PowerPoint
presentations and Microsoft Publisher files (versions 2003 and later).
Alternate installation methods such as using templates, personal.xlsb, or
creating add-ins may be more appropriate in some circumstances. The present
method of copying and pasting the macros into the files to be shared has
advantages in that most users don't have to be involved in installing any
software, the effect of the macros is restricted to just those files in
which they are installed, and updating the macros can be done by anyone with
access to the Dropbox. On the other hand, creating an add-in offers the
potential of checking-in files even in the case where the user elects to
discard changes thereby reducing the burden on the user in those cases but
it would require all users to install the add-in and subsequently install
any updates to the add-in that are developed.
Variants of these macros might be usable with other synchronization
products such as Google Drive, SugarSync, or Box.com.
Questions or Comments:
johnheiser@ocn.me.
If this information was helpful to you, please consider making a
donation.
|
PrintFriendly