0

Users are working on Excel files in Excel Online saved in a SharePoint library. I need to be able to refresh some of the data in the files in full desktop Excel so need to know if anyone is in the file preventing me from opening the file in full desktop and updating the relevant tables.

All the code I have seen seems to be relevant to working on mapped or shared drives and full desktop Excel which I have used successfully before but the code will not worked on a file saved on SharePoint and open in Excel Online.

The code at the end of this link looked promising but it is not working - returning object required errors at beginning of code or if I take out the first three lines of code to get the code to run it returns a read only file even when the file is available.

Is there a way to do this?

Check if workbook in SharePoint 365 is locked for editing

Chris
  • 43
  • 1
  • 4
  • 13
  • I really think you [need this](https://learn.microsoft.com/en-us/office/vba/api/excel.workbooks.cancheckout) instead of that code you pointed to. I use the .CanCheckOut method all the time for SharePoint files. – ScottyJ Jan 01 '22 at 15:51

1 Answers1

0

I just went and grabbed a snippet of code from one of my projects that deals with SharePoint Check-in/Check-out of files so you can see it. Obviously not a full procedure, but you can see how it works.

                If Workbooks.CanCheckOut(FullPath) Then
                        Workbooks.CheckOut (FullPath)

                        Application.EnableEvents = False
                        Set WB_trgt = Application.Workbooks.Open(FullPath)

                        Call Fix_210124_ClearOutTestData(WB_trgt)
                        
                        If i_ReplaceModules = vbYes Then
                                Call Replace_Modules(WB_trgt)
                                'Call Replace_1_Module(WB_trgt)
                        Else
                                Call Remove_OLD_Modules(WB_trgt)

                        End If


                        WB_trgt.Save

                        With WB_trgt
                                .CheckIn savechanges:=True
                        End With

                End If
ScottyJ
  • 945
  • 11
  • 16
  • I am not using check in/check out (multiple users update the file in Excel Online - check in/out would cause the process to grind to a halt) so I am afraid this approach is not an option. – Chris Jan 02 '22 at 21:45