Click "Edit Workbook" for READ-ONLY file

[Mark_ChristianD]

Hi,

Does anyone know how I can click the below button which appears for read-only Excel files:


I can't seem to find the correct VBO that will allow me to do so.

Thank you in advance!

Cheers,
Mark

------------------------------
Mark Christian Dela Cruz
------------------------------

[michaeloneil]

Hi Mark

I think on the create instance if you enter True for events enabled this should fix that problem.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[PRASANTHM]

Hi Mark,

Can you plz let me know what is the source dir (local or SharePoint or network path) is? We can deny this from a few configs

Hi Michael, I believe that event enabled will disable the execution of macros for activated workbooks

------------------------------
Thanks
Prasanth
Software Engineer | RPA Developer
------------------------------

[Mark_ChristianD]

Hi Michael,

Thank you for taking the time to respond!

I entered "True" for Events Enabled on Create Instance:


Unfortunately, the pop-up still appears:

To give a bit more context, once the Excel file has been opened, I need to write some data into the file. However, due to the Read-Only pop-up, I am unable to do so.

Cheers,
Mark

------------------------------
Mark Christian Dela Cruz
------------------------------

[Mark_ChristianD]

Hi Prasanth,

Thank you for taking the time to respond!

The file is being pulled from a SharePoint folder. Is there a way to configure in such a way that the file is not opened in Read-Only mode? Looking forward to your suggestion/s.

Cheers,
Mark

------------------------------
Mark Christian Dela Cruz
------------------------------

[michaeloneil]

Hi @Mark Christian Dela Cruz

I think for this you would need to create a new action to open the read only file within the excel vbo. The below code should be able to do this for you. Inputs are handle and filename. Filename should be the path and name of the file e.g. C:\mydocuments\myexcelfile.xlsx


Dim wb as Object = GetInstance(handle).Workbooks.Open(fileName, 0, False, , , , True)

name = wb.Name
wb.Activate()
​

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[Mark_ChristianD]

Hi @Michael ONeil,

Thank you for sharing the above code.

I created a new action using the "Open Workbook" VBO as the template - however, the file is still being opened in Read-Only mode and cannot be edited, unfortunately.

Cheers,
Mark​​

------------------------------
Mark Christian Dela Cruz
------------------------------

[PRASANTHM]

Hi Mark,

Sorry for the delayed response, I was swamped with my work.

I guess you're using the 2016 version of excel and your file is hosted on SharePoint, mostly we're seeing this alert (READ-ONLY We opened this workbook read-only from the server). If you try to open that file it'll show the below alert and it won't allow saving (you can able to save in a different name)

This alert is used to prevent locking out other users from editing the file



Always loop your IT Team

1. Try to update the excel version/build => check the fixes https://learn.microsoft.com/en-us/officeupdates/beta-channel and https://learn.microsoft.com/en-us/sharepoint/troubleshoot/lists-and-libraries/files-open-as-read-only-and-cannot-check-in-or-out

2. 



3. Check the file access, Is USER or BOT has elevated right to write

https://support.microsoft.com/en-us/office/why-did-my-file-open-read-only-3ab4b792-da50-4b38-8628-14c64e1f1d15

4. Delete cached files when closed.
5. GPO to disable Preview Pane in File Explorer.

6. Try to enable Co-Authoring Feature

7. Disable the checkout/in option




------------------------------
Thanks
Prasanth
Software Engineer | RPA Developer
------------------------------

[Mark_ChristianD]

Hi @PRASANTHM, 

Thank you very much for such a detailed response, and my apologies for my delayed feedback.

Unfortunately, our IT team does not allow the changes that you have shared above. As a workaround, we have instead created the automation in such a way that the file is first saved locally before being copied to the SharePoint directory.

Again, thank you so much for the help!

Cheers,

Mark

------------------------------
Mark Christian Dela Cruz
------------------------------