How to bypass SharePoint "Read-Only" alert when opening an Excel Workbook with VBA.

0
votes

I'm trying to check if the workbook file below is already open in "edit mode" when the code tries to run. I'm trying to plan for when this will error out because someone else is already in the file. I need the Workbook.Open to open as ReadOnly = False because if nobody is in it, I need to be able to save after the update.

The issue I'm having is that even with DisplayAlerts = False when the Workbook.Open line runs, I get an on screen prompt that says "The File is locked for editing by (some user). Do you want to: View a read-only copy or save and edit a copy of the file." There is also a checkbox that says "Receive a notification when the server file is available". The DisplayAlerts = False doesn't seem to cancel the SharePoint prompt. Any ideas on why that wouldn't cancel the promt? I want to get to the point in the code where it tries to open in edit-mode, and can't, and then goes to If Activeworkbook.Readonly Then line and exits out of the sub. Right now it stops and waits for a selection on the SharePoint prompt. 

Sub SendFCSpec()

MsgBox ("Please wait while your comments are sent to the database.")
ActiveSheet.Unprotect Password:="BAS1"
'Turn Screen Updating and Alerts off
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Disable Macros on AutoOpen of the Excel Workbook
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Workbooks.Open Filename:="http://Sharepoint.com/QA/FC%20QA%20Workshop/Databases/Dec/FC%20Spec%20Database.xlsm", _
UpdateLinks:=3, ReadOnly:=False
If ActiveWorkbook.ReadOnly Then
    ActiveWorkbook.Close
    MsgBox "Another user has the database open. Unable to submit comments at this time."
    Application.AutomationSecurity = msoAutomationSecurityLow
    Exit Sub
End If
Application.AutomationSecurity = msoAutomationSecurityLow

ActiveWorkbook.Save
ActiveWorkbook.Close

ActiveSheet.Select
ActiveSheet.Protect Password:="BAS1", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox ("Comments have been saved to the Database. Thanks")
End Sub
2
excelvbasharepoint

2 Answers

1
votes

Replace this line:

Workbooks.Open _
    fileName:="http://Sharepoint.com/QA/FC%20QA%20Workshop/Databases/Dec/FC%20Spec%20Database.xlsm", _
    UpdateLinks:=3, ReadOnly:=False

With this line:

Workbooks.Open _
    fileName:="http://Sharepoint.com/QA/FC%20QA%20Workshop/Databases/Dec/FC%20Spec%20Database.xlsm", _
    UpdateLinks:=3, ReadOnly:=False, Notify:=False
0
votes

I created a WB, saved it Read-only to my desktop as Book1. I tried the method as described above, but still got the popup. I changed 'ReadOnly:=' from False to True and it worked. Try it yourself.

Sub Test()
Dim File1 As String
File1 = Environ("USERPROFILE") + "\Desktop\Book1.xlsx"
Workbooks.Open Filename:=File1, ReadOnly:=True, Notify:=False
End Sub