Excel says a file is locked by 'another user' and removes macro when opening

Question

I was hoping someone could kindly help me with this issue. I have never faced this before and I am not able to solve it.

We have an Excel file on the network, that up to 10 people daily use, with Excel 365. The file contains a macro that connect to SAP. Until 3 weeks ago, everything was ok, and has been ok for over 6 years.

Now, we hired a new team member, who was given a new PC.

When our new team member tries to open the file on her new machine, there is first a message mentioning there is a problem with the file and proposing to try to recover as much as we can (see picture 1).

Then when she chooses "Yes", she gets another error message mentioning that the file is locked by 'another user'. Oddly enough, it says 'another user', and not the name of that user, and we tested and are 100% nobody else is using the file.

If she then select "Notify" or "Read only", another message pops up, info message from SAP Analysis for Windows.

Finally after she clicks 'OK', the file gets finally opened, but all macro are removed from it. There is a message about the repair:

The log file is added at the end of this post.

We don't understand where the issue exactly is, this is what we tested:

• the same new users recently hired can use the file with the macro without any issue on another machine
• users that could use the file on their machine, can't on the new machine of our new colleague (for the test, they log in with their own credentials)
• other macro files can be opened without any problem on the computer of our new colleague
• we have fully reinstalled the computer, and still it bugs...

Log message:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error242560_01.xml</logFileName><summary>Errors were detected in file 'S:\MyDistriplus\DI SAP OPERATIONS\Création articles\Templates Création\TEst (2).xlsm'</summary><removedParts><removedPart>Removed Part: /xl/vbaProject.bin part.  (Visual Basic for Applications (VBA))</removedPart></removedParts></recoveryLog>

Answer 1

I had a similar issue, although not the locked by another user dialog. Losing the VBA made no sense to me. It turned out my issue was from duplicating a sheet in VBA that contained an image.

I think I was creating a duplicate ListObject entry for the picture, which would crash excel. My solution was to duplicate the image separately.

If your code is duplicating sheets, try removing any ListObjects and see if the problem persists.

Answer 2

The problem is with the machine (the new computer).

Although you've performed a clean installation (reinstalled), you probably installed the same (probably corrupted) version of Windows and Office.

Try to open OneDrive and change this setting:

Try to install the same version of Windows and Office that's installed on the rest of the computers.

Note: Make sure to include all needed apps, addons, and files to the problematic device.