0

I am trying to write a macro that iterates through excel files located in a SharePoint 2010 site and modify their data. Unfortunately I run into the issue where a file may be already opened for editing by another user and throws a popup that isn't disabled by .DisplayAlerts being False. This becomes a problem because this macro is set to run automatically, so I'm trying to avoid any manual interactions.

I've tried using code such as this:

`Public Function FileInUse(sFileName) As Boolean
    On Error Resume Next
    Open sFileName For Binary Access Read Lock Read As #1
    Close #1
    FileInUse = IIf(Err.Number > 0, True, False)
    On Error GoTo 0
End Function`

to detect and skip files. The problem I run into is that this works too well, and returns True for files that are not open (specifically the error # is 75). I have also tried using the .CanCheck Out/In functions but those fail to detect if the file is already open for editing, even if they are checked out.

If anyone has any ideas to make this code more specific or another way of checking if the files are already opened for editing it would be appreciated.

TL;DR: Trying to detect if SharePoint files are already open to be edited, all of the solutions that I've tried haven't worked or work too well.

Community
  • 1
  • 1
Danthepianoman
  • 11
  • 3
  • Have not worked with sharepoint but see if [this](https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open) applies to it? – Siddharth Rout Jun 02 '17 at 13:33
  • @SiddharthRout I have already looked at that post and run into same problems in the code above where it flags all of the files as being open/in use. – Danthepianoman Jun 02 '17 at 14:02

0 Answers0