VBA and sharepoint

[laurawr]

Hi
I have an excel document stored on sharepoint that will be used by all of my colleagues.

However even though people have had the sufficient training on sharepoint people still manage to open an excel document on sharepoint as read only and do a few hours of work then cannot save the data due to it being in read only mode.

How can i bring message box up if the document opened via sharepoint is read only and close the application for them to re open it as the correct format... currently i am using this code and it does not work.

Private Sub Workbook_Open()
If Me.ReadOnly = True Then
       MsgBox "This is a read only document. Please open this document as "Checkout and Edit'"
    End If
End Sub

Thanks in advance

L

 

[laurawr]

Hi, i have eventuallly got my workbook checking in and out of sharepoint.


My solution was different to the one that I posted about..

Instead of a message box on open of the Userform to check if it has been opened read only, the code checks if the workbook can be checked in.

If it can't then obviously it has been opened as a read only document and I have set it to disable some features of my Userform to prevent people from doing any work that can't be saved.
If it can be checked in, then it obviously means it is already checked out and I have allowed all features to be active.

Here is my code. I have commented some stuff for clarification.

In the workbook module-

' if the document has been checked out
 
If ThisWorkbook.CanCheckIn Then 
 
Allocate.Show vbModeless
 
Else
 
' if the document is read only
 
Search.CreateBtn4.Enabled = False
Search.ModifyBtn4.Enabled = False
Search.Show vbModeless
 
End If
End Sub

In the userform, I have an exit button. When this is pressed the code checks if it can be checked in. If so, then it checks the document in. If it is read only, it only closes the document without any prompts to save.

Sub ExitBtn_Click()
'if the document has been checked out then check it in and close
 
If ThisWorkbook.CanCheckIn Then
ThisWorkbook.CheckIn
ThisWorkbook.Close
 
Else
 
'if it is read only close the workbook with no prompts
 
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
 
End If
End Sub

Thanks
L

 

[p0ldi]

I found this post when searching for a way to verify whether someone opened the file as "server-side" read-only from Sharepoint.

This is what I ended up using:

If Workbooks.CanCheckOut(filename:=ActiveWorkbook.FullName) = False Then
    MsgBox ("The file is opened as read-only from the server.")
Else
    [code to do stuff]
End If

When you hit the "edit" button in the yellow sharepoint-bar to switch into editing mode (which you only see when you have the necessary authorization), the code correctly executes the [stuff] part

 

[unbrokenrock]

Thanks,p0ldi, this saved my day.

 

[kjdulin]

I've been searching for a solution for this for hours. The "If ActiveWorkbook.ReadOnly" behaves differently if opened from Sharepoint and it was causing us to lose data.

Thank you pOldi for this excellent solution,,, seems to be working!

 

[NewOrderFac33]

Checkinwithversion also solved my checking-in issue within 2010.
Thanks to kjdulin for bumping it!

 

[OTOTO]

This code does not work for me:

If Workbooks.CanCheckOut(filename:=ActiveWorkbook.FullName) = False Then     MsgBox ("The file is opened as read-only from the server.") Else     [code to do stuff] End If</pre>

When I check the file out of sharepoint the messagebox still appears. I don't believe this is supposed to happen.

You are coding this into the workbook code per a Private Sub Workbook_Open() event?

 

[OTOTO]

I tried to copy the code for use with an exit button. It's telling me that another file with the same name exists and asks if I want to save it anyway. I didn't have this issue before with simply exiting out of excel. The program was saving itself and then our system recognizes that it is a document draft from sharepoint and it asks the user if they want to check the document back in. For me, it makes no sense to add in this code given the popup for the check-in reminder as well as a popup for a check-out reminder re: saving one's edits. What is the application for this coding to self-checkout and self-checkin?

Hi, i have eventuallly got my workbook checking in and out of sharepoint.


My solution was different to the one that I posted about..

Instead of a message box on open of the Userform to check if it has been opened read only, the code checks if the workbook can be checked in.

If it can't then obviously it has been opened as a read only document and I have set it to disable some features of my Userform to prevent people from doing any work that can't be saved.
If it can be checked in, then it obviously means it is already checked out and I have allowed all features to be active.

Here is my code. I have commented some stuff for clarification.

In the workbook module-

' if the document has been checked out
 
If ThisWorkbook.CanCheckIn Then 
 
Allocate.Show vbModeless
 
Else
 
' if the document is read only
 
Search.CreateBtn4.Enabled = False
Search.ModifyBtn4.Enabled = False
Search.Show vbModeless
 
End If
End Sub

In the userform, I have an exit button. When this is pressed the code checks if it can be checked in. If so, then it checks the document in. If it is read only, it only closes the document without any prompts to save.

Sub ExitBtn_Click()
'if the document has been checked out then check it in and close
 
If ThisWorkbook.CanCheckIn Then
ThisWorkbook.CheckIn
ThisWorkbook.Close
 
Else
 
'if it is read only close the workbook with no prompts
 
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
 
End If
End Sub

Thanks
L

 

[OTOTO]

I have solved the problem.
The issue was where and how I accessed the file as well as how I phrased the code.

Thank you ThisWorkbook.CanCheckIn

I tried to copy the code for use with an exit button. It's telling me that another file with the same name exists and asks if I want to save it anyway. I didn't have this issue before with simply exiting out of excel. The program was saving itself and then our system recognizes that it is a document draft from sharepoint and it asks the user if they want to check the document back in. For me, it makes no sense to add in this code given the popup for the check-in reminder as well as a popup for a check-out reminder re: saving one's edits. What is the application for this coding to self-checkout and self-checkin?