VBA Macro to Open Read Only Excel from SharePoint and Enable Edit Workbook

[ak_254]

Hi all,


I am currently in the process of trying to create a macro to run from my current workbook (file1) and be able to open an excel file from SharePoint (file2) to refresh and update using the data from (file1).


The only problem I am running into so far is when opening (file2) it is opened up as a "Read Only" requires the prompts message "Enable Editing" to be enabled before edits can be made to (file2)


I am looking for a way to be able to automatically click enable editing when opening (file2)

Below I have included the code I currently have running to open (file2) from (file1)



Workbooks.Open _
Filename:="C:\Desktop\Automation Project\In_Stock.xlsx", _
UpdateLinks:=3, ReadOnly:=True, Notify:=False


Any help is much appreciated.

 

[KDxcel]

Hi ak_254,

I know your query was posted long ago but I have found the solution to break the Read-Only and update the excel document via Macro:

If you add "ActiveWorkbook.LockServerFile" after opening the file code, then it removes the Read-only and updates the excel as normal.


Regards,
KDxcel

Hi all,


I am currently in the process of trying to create a macro to run from my current workbook (file1) and be able to open an excel file from SharePoint (file2) to refresh and update using the data from (file1).


The only problem I am running into so far is when opening (file2) it is opened up as a "Read Only" requires the prompts message "Enable Editing" to be enabled before edits can be made to (file2)


I am looking for a way to be able to automatically click enable editing when opening (file2)

Below I have included the code I currently have running to open (file2) from (file1)



Workbooks.Open _
Filename:="C:\Desktop\Automation Project\In_Stock.xlsx", _
UpdateLinks:=3, ReadOnly:=True, Notify:=False


Any help is much appreciated.

 

[ak_254]

Works perfect thank you so much!!

 

[YaroslavZ]

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 part) :

 "/:x:/r"

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