CODEWITHSUNDEEP
excelvbasharepointms-wordoffice365
user10863706
user10863706

Reputation:

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

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?

Upvotes: 0

Views: 7649

Answers (2)

Yaroslav Zharkov
Yaroslav Zharkov

Reputation: 43

Tried a lot of solutions, the only one that successfully worked for me was the adaptation of your link to the file:

From this (as you copy it via "Copy link" in SharePoint"):

https://pmt.sharepoint.com/:x:/r/teams/ClearServiceRus/Teams%20Wiki%20Data/General/Book.xlsx?d=wc4cde03eae523df9a7082c18g4ege61b&csf=1&web=1&e=lnONME

To this (special attention to "/:x:/r" part) :

https://pmt.sharepoint.com/teams/ClearServiceRus/Teams%20Wiki%20Data/General/Book.xlsx

Code:

URL = "https://pmt.sharepoint.com/teams/ClearServiceRus/Teams%20Wiki%20Data/General/Book.xlsx"
Set wb = Workbooks.Open(URL)
Debug.Print wb.FullName

Upvotes: 2

TourEiffel
TourEiffel

Reputation: 4434

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

Upvotes: 0

Related Questions