check in/ out an excel doc on sharepoint

[ADAMC]

Hi all,
I have done some background on this but cant seem to get it working
can someone help me?

I have a doc on sharepoint called adamc.xlsx
when I open the doc i would like the workbook open event to check the document out for editing

on the workbook close event i would like it to be checked back in...

Been playing arond with Workbooks.CancheckOut but ive completely lost the plot.....

Any help greatly appreciated.

Thank you!!

 

[Andrew Poulsom]

This might help you:

http://www.mrexcel.com/forum/showthread.php?t=538092

 

[ADAMC]

Hi Andrew,
thanks for the link not so sure I have my syntax correct, based on the example you provided:

Sub UseCanCheckOut(docCheckOut As String)

' Determine if workbook can be checked out.
If Workbooks.CanCheckOut(Filename:=adamc.xlsm) = True Then
Workbooks.CheckOut (Filename:=adamc.xlsm)
Else
MsgBox "You are unable to check out this document at this time."
End If

End Sub

Would the filename be the URL from sharepoint rather than the filename itself?
The above doesnt seem to do anything.

Sorry if thats a silly question.....Thanks.

 

[Andrew Poulsom]

I would have thought you could use ThisWorkbook.FullName (or Me.FullName if the code is in the ThisWorkbook module).

 

[ADAMC]

Suprising even myself that actually makes sense to me. Thanks Andrew.

Based on the links you have provided and various googling I have cobbled together this code:

Sub test()
Dim docCheckOut As String
docCheckOut = "//office.bt.com/sites/Training/Design%20Admin/Training%20Plan/adamsmacro.xlsm"

Call UseCheckOut(docCheckOut)
End Sub

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

Unfortunately although I can clearly see the excel workbook is NOT checked out the macro above always returns the Msgbox "unable to check out this document at this time"

Anyone have any idea why?

When you first open a doc from sharepoint it always shows as readonly.....
Could this be the reason why it doesnt work? can a macro run on a file which is read only?

If anyone could shed any light id be really grateful.

Thanks

 

[Andrew Poulsom]

Have you tried?

docCheckOut = ThisWorkbook.FullName

 

[ADAMC]

Hi Andrew,

I tried your suggestion, again the macro runs but only returns the Msgbox "unable to check out the document at this time"

Is it possible to post a screenshot, might be easier if I show a screenshot of what Im looking at?

Thanks

 

[Andrew Poulsom]

What does ThisWorkbook.FullName return? The filename should be as in the example at the bottom of this:

http://msdn.microsoft.com/en-us/library/aa140312(v=office.10).aspx

 

[ADAMC]

Hi Andew,

Apologies, I completely forgot to respond.

Thisworkbook.fullname actually returned the URL of the excel file stored in sharepoint as I had in my original code.

In case anyone is interested, I dont know why or how but the cancheckout method only seems to work when triggered from a workbook other than the work book you are checking out.
I.E = A workbook can not check itself out. When I run the code from a seperate workbook, it actually works fine.

Thanks for your efforts in helping me.

P.S If anyone knows why the cancheckout method cant run from the same workbook it is checking out id really like to know thanks

 

[OTOTO]

Hi Andew,

Apologies, I completely forgot to respond.

Thisworkbook.fullname actually returned the URL of the excel file stored in sharepoint as I had in my original code.

In case anyone is interested, I dont know why or how but the cancheckout method only seems to work when triggered from a workbook other than the work book you are checking out.
I.E = A workbook can not check itself out. When I run the code from a seperate workbook, it actually works fine.

Thanks for your efforts in helping me.

P.S If anyone knows why the cancheckout method cant run from the same workbook it is checking out id really like to know thanks

Anyone vouge for this? is this actually the case? Any thoughts on forced checkout and forced checkin upon opening from Sharepoint and closing the file?