CODEWITHSUNDEEP
excelvbasharepoint
arooney88
arooney88

Reputation: 305

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

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

Upvotes: 1

Views: 10715

Answers (3)

John Noonan
John Noonan

Reputation: 1

If the file is on Sharepoint, it will be opened as read-only by default no matter what you do. To change it to Edit after opening insert the following line of code after the open statement

ActiveWorkbook.LockServerFile

Upvotes: 0

SteveB
SteveB

Reputation: 1

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

Upvotes: 0

EEM
EEM

Reputation: 6659

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

Upvotes: 2

Related Questions