CheckOut/CheckIn from/to SharePoint 2010

[Nick H]

Has anyone else come across this and know of a fix?

Here's the CheckOut code, as it comes from Excel 2010 Help...

Sub UseCheckOut(docCheckOut As String)

' Determine if workbook can be checked out.
If Workbooks.CanCheckOut(docCheckOut) = True Then
Workbooks.CheckOut docCheckOut
Else
MsgBox "Unable to check out this document at this time."
End If

End Sub

....For me this 'almost' works. As the requested file opens I get the
Enable Macros prompt, I click Enable and then I see the workbook open
briefly and immediately close.

The only way I've had any degree of success is to use the Open method
aswell...

Sub UseCheckOut(docCheckOut As String)

' Determine if workbook can be checked out.
If Workbooks.CanCheckOut(docCheckOut) = True Then
Workbooks.CheckOut docCheckOut
Workbooks.Open docCheckOut
Else
MsgBox "Unable to check out this document at this time."
End If

End Sub

However, there are a couple of problems with this. Firstly, I get the
Enable macros prompt twice - I don't want users to see that. Secondly,
I want the file to check itself back in when the Before_Close event
fires. If the file closes immediately after being checked out it will
obviously check itself back in. When the code then opens the workbook
with the Open method the file is in the wrong state according to
SharePoint.

 

[GS]

Is there anything in the code that would cause the workbook to close on
some conditional criteria?

<FYI>The only way to lose the macro security (& associated warnings) is
to use an automated instance of Excel.

 

[Nick H]

Ah! I just needed to think a bit more logically.

I managed to solve the problem simply by swapping the .CheckOut
and .Open lines like so...

Sub UseCheckOut(docCheckOut As String)


' Determine if workbook can be checked out.
If Workbooks.CanCheckOut(docCheckOut) = True Then
Workbooks.Open docCheckOut
Workbooks.CheckOut docCheckOut
Else
MsgBox "Unable to check out this document at this time."
End If


End Sub


Maybe I didn't look hard enough but I haven't seen this documented
anywhere. But plenty of comments saying "you can't do it from VBA"
followed by mind boggling workarounds using .Net code.

Anyway I hope this helps someone else.

Btw, Something else I found useful - the docCheckOut string is the
FullName of the file you want to open. To get an example of what it
should look like, Open a workbook in your SharePoint Library, Open the
VBE and in the Immediate Window type ?ThisWorkbook.FullName and hit
return. Copying the path from your browser window won't work.

When you check the file back in you can use ThisWorkbook.CheckIn or,
if closing remotely, Workbooks(docCheckIn).CheckIn where docCheckIn is
a string containing the filename only, without the path.

Br, Nick.

 

[Nick H]

Thanks for your response Garry.

I've managed to solve the problem by swapping the methods (see my
reply to myself). However I did try using an automated instance of
Excel but although it passed the CanCheckOut test the code then failed
at the CheckOut method with an error saying the workbook couldn't be
Checked out. Hmmm, that was before I swapped the .Open and .CheckOut
methods though - maybe I should try it again. One less click for the
user if I can get it to work.

 

[Nick H]

Yes, this works...

Private mXlApp As Excel.Application

Sub SPCheckOut(docCheckOut As String)
Set mXlApp = CreateObject("Excel.Application")
' Determine if workbook can be checked out.
If mXlApp.Workbooks.CanCheckOut(docCheckOut) = True Then
mXlApp.Workbooks.Open Filename:=docCheckOut
mXlApp.Workbooks.CheckOut docCheckOut
mXlApp.Visible = True
Else
MsgBox "Unable to check out this document at this time."
End If
End Sub

Sub SPCheckIn(docCheckIn As String)
' Determine if workbook can be checked in.
If mXlApp.Workbooks(docCheckIn).CanCheckIn = True Then
mXlApp.Workbooks(docCheckIn).CheckIn
Else
MsgBox "This file cannot be checked in at this time. Please
try again later."
End If
End Sub

Obvioulsy mXlApp should be disposed of properly elsewhere when
finished with.

Br, Nick.

 

[GS]

Nick H submitted this idea :

Yes, this works...

Private mXlApp As Excel.Application

Sub SPCheckOut(docCheckOut As String)
Set mXlApp = CreateObject("Excel.Application")
' Determine if workbook can be checked out.
If mXlApp.Workbooks.CanCheckOut(docCheckOut) = True Then
mXlApp.Workbooks.Open Filename:=docCheckOut
mXlApp.Workbooks.CheckOut docCheckOut
mXlApp.Visible = True
Else
MsgBox "Unable to check out this document at this time."
End If
End Sub

Sub SPCheckIn(docCheckIn As String)
' Determine if workbook can be checked in.
If mXlApp.Workbooks(docCheckIn).CanCheckIn = True Then
mXlApp.Workbooks(docCheckIn).CheckIn
Else
MsgBox "This file cannot be checked in at this time. Please
try again later."
End If
End Sub

Obvioulsy mXlApp should be disposed of properly elsewhere when
finished with.

Br, Nick.

mXlApp will be implicitly destroyed when the file containing the code
that created it terminates. However, it would be best to explicitly
destroy it BEFORE the file closes because anything we leave to VBA to
do implicitly requires extra processing on VBA's part. IMO, it's just
good programming practice to explicitly destroy objects we create when
we no longer need them.

Example:
Set mXlApp = Nothing