Setting up a shared workbook programmatically

[Stretchwickster]

Hi,

Does anyone have any code to setup a shared workbook in Excel i.e. to do the same thing as Tools->Shared Workbook->Editing->Allow changes by more than one user at the same time.

I tried recording a macro to show me how to do it - but it doesn't show any code to do it - I think maybe the macro recorder is disabled when you share the workbook.

I also tried a keyword search on here and a google search neither of which produced any useful information.
Your help would be much appreciated!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[xlbo]

Have a look at the ProtectSharing method and the MultiUserEditing Property - these both came up when I typed "Share" into excel VBA help

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Stretchwickster]

Cheers Geoff - I'll look into it. I often find the Excel VBA help quite unhelpful (!) so I didn't think to look there. I need more caffeine!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[xlbo]

I actually find that for most things, the VBA help file, in conjunction with the object model, is quite useful - although I have to say, I've been delving around in class modules and it is not very helpful for that

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Stretchwickster]

I just find the method of searching annoying i.e. in the Index of the help file it predicts what you're going to type and I hate that! I guess my problem is with how you search rather than the help content itself! Also, in this particular case none of the Topics have any information in the "See Also" link.

Ok - I've checked out these examples.
1) ProtectSharing seems to be concerned with setting passwords on shared workbooks to protect them from unauthorised use. I'm not really concerned about this at the moment.

2) MultiUserEditing is simply a read-only property to indicate whether this facility is turned on or not - you cannot set it.

The only bit of information I can find regarding the setting of a workbook to shared is in the AccessMode and ConflictResolution arguments of the SaveAs method.

Initially, I couldn't find information on programmatically setting the following areas of the Shared Workbook Advanced tab:
- Track Changes
- Update Changes
- Conflicting changes between users

But after a lot of digging (as these options are not given any kind of logical grouping in the help file) I found the following Workbook properties that can be set to bringabout the above Shared Workbook Advanced Tab Options:

Track Changes:
-KeepChangeHistory: used to enable/disable change tracking for a shared workbook
-ChangeHistoryDuration: defines the length of time (in days) that the change history is to be applied over

Update Changes:
-AutoUpdateSaveChanges: Setting this to true results in current changes to the shared workbook being posted to other users whenever the workbook is automatically updated. AutoUpdateFrequency must be set for this to take effect.
-AutoUpdateFrequency: defines the number of minutes between automatic updates to the shared workbook.

Conflicting changes between users:
-ConflictResolution: This can be set in the SaveAs method and controls what happens when there is a conflict
-ShowConflictHistory: Shows the Conflict History worksheet which logs all conflict details.

My main annoyance was that these related groups of properties were not linked together in the help file so it took quite a while to locate them all.

The reason I have posted them here is to make someone else's life easier when they want to programmatically have access to the share workbook properties.



Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)