Opening an Excel workbook from SharePoint in edit mode from a Word VBA macro

Question

I am trying to open an Excel workbook in edit mode from my company's SharePoint site through a VBA macro from Word. I am able to open the workbook but it always opens in read-only mode with no way to even enter edit mode. Weird thing is that the ribbon that usually allows me to enter edit mode in such cases does not appear at all.

I tried adding ActiveWorkbook.LockServerFile to the created object (returns an error) and to the workbook I'm trying to open and it did not change a thing.

I also tried opening it using : Workbooks.Open(Filename:="\\filepath\file.xlsm", UpdateLinks:=False, ReadOnly:=True), also did not help. Argument False also does not work.

I have a user form in a Word document that automatically fills input data into necessary places in the document, calculates payments and saves it as pdf. Afterwards I want to add a record to tot he bottom of the Excel file I use to keep track of the generated documents. So basically I want to open the Excel workbook and add a new line after the last row based on the data I input to the Word's userform.

No matter what I do, it always opens in read-only mode with no way to even manually enter edit mode.

I suspect that it's an issue with my company's trust center settings as everything is blocked and grayed out and I can't mess with those to see if it's the issue. Is that possible?

you said ReadOnly:=True but this means that you want to open the workbook in Read Only. Is that a typo ? — TourEiffel
I tried both. I read somewhere that it might help for some reason and unfortunately it did not. — user10863706
I will put some code as answer please comment it and tel me if this work for you — TourEiffel

TourEiffel TourEiffel · Accepted Answer · 2019-09-03T15:41:50

Does this code from MSDN Forum open the file you just selected thanks to the dialog box ?

Please make sure that https://sharepoint.com/team/folder is the good url

sub tryme()
Dim SummaryWB As Workbook
Dim vrtSelectedItem As Variant

With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = "https://sharepoint.com/team/folder" & "\"
    .AllowMultiSelect = False
    .Show
    For Each vrtSelectedItem In .SelectedItems
        Set SummaryWB = Workbooks.Open(vrtSelectedItem)
    Next
End With

If SummaryWB Is Nothing then Exit Sub

end sub

If the MSDN Code work maybe you can try this :

Set MyWorkBook = Workbooks.open("https://sharepoint.com/team/folder" & "\" & FileName)

Please don't least that FileName Can be just the name of the workbook like test.xlsm or the workbook name + folder like foldername1\foldername2\test.xlsm

As I Said in comment, I am waiting for your feedbacks to build an acceptable answer, Kindest regards

Opening an Excel workbook from SharePoint in edit mode from a Word VBA macro

2 Answers