I have a "busy" file (sharepoint hosted) that, at particulary times of the
months, many folks converge upon it to provide monthly updates of specific
worksheets.

Sometimes the clobber each other by overwritting each other, etc, because
they fail to check the file out. I would like to write some code that would
automatically check the file out immediately upon open, and the check it in,
upon close. And if I had this code, where would it be placed? Is there a
location where you can place code you want executed at open (or close)?

Thanks! Patk

1) Set up the Document Library to require checkout on Edit.
Settings - Document Library Setting - Versioning Settings

Select Require documents to be checked out before they can be edited.

2) Train the users to EDIT the document in Microsoft Excel,Powerpoint, etc.

On my system, if I open a document and don't check it out to edit, it's
opened as READ ONLY.

Here is some code I use when I want to check out or check in a document
programmatically. I'm not sure it'll do what you want to do though, but it's
a starting point.

Sub UseCheckOut(PPTApp As PowerPoint.Application, doccheckout As String)

' Determine if workbook can be checked out.
'Test for file existance first
If FileExists(doccheckout) Then
If PPTApp.Presentations.CanCheckOut(doccheckout) = True Then
PPTApp.Presentations.CheckOut doccheckout
Else
'MsgBox "Unable to check out this document at this time."
End If
End If
End Sub

Sub CheckIn(aPPS As PowerPoint.Presentation, myComment As String)
aPPS.CheckIn SaveChanges:=msoTrue, Comments:=myComment, MakePublic:=msoFalse
End Sub

Function FileExists(fname) As Boolean
' Returns TRUE if the file exists
Dim x As String
x = Dir(fname)
If x < "" Then FileExists = True _
Else FileExists = False
End Function



--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"PatK" wrote:

I have a "busy" file (sharepoint hosted) that, at particulary times of the
months, many folks converge upon it to provide monthly updates of specific
worksheets.

Sometimes the clobber each other by overwritting each other, etc, because
they fail to check the file out. I would like to write some code that would
automatically check the file out immediately upon open, and the check it in,
upon close. And if I had this code, where would it be placed? Is there a
location where you can place code you want executed at open (or close)?

Thanks! Patk

Thanks! I shall give that a try. Good points about the sharepoint side.
Did not realize you could "require" checkout. I shall let you know how it
turns out!

Patk

"Barb Reinhardt" wrote:

1) Set up the Document Library to require checkout on Edit.
Settings - Document Library Setting - Versioning Settings

Select Require documents to be checked out before they can be edited.

2) Train the users to EDIT the document in Microsoft Excel,Powerpoint, etc.

On my system, if I open a document and don't check it out to edit, it's
opened as READ ONLY.

Here is some code I use when I want to check out or check in a document
programmatically. I'm not sure it'll do what you want to do though, but it's
a starting point.

Sub UseCheckOut(PPTApp As PowerPoint.Application, doccheckout As String)

' Determine if workbook can be checked out.
'Test for file existance first
If FileExists(doccheckout) Then
If PPTApp.Presentations.CanCheckOut(doccheckout) = True Then
PPTApp.Presentations.CheckOut doccheckout
Else
'MsgBox "Unable to check out this document at this time."
End If
End If
End Sub

Sub CheckIn(aPPS As PowerPoint.Presentation, myComment As String)
aPPS.CheckIn SaveChanges:=msoTrue, Comments:=myComment, MakePublic:=msoFalse
End Sub

Function FileExists(fname) As Boolean
' Returns TRUE if the file exists
Dim x As String
x = Dir(fname)
If x < "" Then FileExists = True _
Else FileExists = False
End Function



--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"PatK" wrote:

I have a "busy" file (sharepoint hosted) that, at particulary times of the
months, many folks converge upon it to provide monthly updates of specific
worksheets.

Sometimes the clobber each other by overwritting each other, etc, because
they fail to check the file out. I would like to write some code that would
automatically check the file out immediately upon open, and the check it in,
upon close. And if I had this code, where would it be placed? Is there a
location where you can place code you want executed at open (or close)?

Thanks! Patk