SharePoint: SHAREPOINT_WIN issue with xlwings versions after 0.30.13

[timo-stein]

System: Windows 10

Software versions: xlwings-0.30.14 / xlwings-0.30.15, Office 365 (version 2312), Python 3.12.2

Describe your issue (incl. Traceback!)

Using xlwings-0.30.13 works fine. Version xlwings-0.30.14 and xlwings 0.30.15 will yield the following error message while running xw.Book(self.xlsmpath) with self.xlsmpath being an absolute path:

raceback (most recent call last):
  File "c:\Users\MYUSERNAME\Nexperia\Waste Management - Documents\scripts\regelbestellung.py", line 17, in <module>
    befpap.setPaper(
  File "c:\Users\MYUSERNAME\Nexperia\Waste Management - Documents\scripts\befpap_test.py", line 137, in setPaper
    workbook = xw.Book(self.xlsmpath)
               ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\MYUSERNAME\AppData\Local\Programs\Python\Python312\Lib\site-packages\xlwings\main.py", line 919, in __init__
    wb.fullname.lower() == fullname.lower()
    ^^^^^^^^^^^
  File "C:\Users\MYUSERNAME\AppData\Local\Programs\Python\Python312\Lib\site-packages\xlwings\main.py", line 1173, in fullname
    return self.impl.fullname
           ^^^^^^^^^^^^^^^^^^
  File "C:\Users\MYUSERNAME\AppData\Local\Programs\Python\Python312\Lib\site-packages\xlwings\_xlwindows.py", line 903, in fullname
    return fullname_url_to_local_path(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\MYUSERNAME\AppData\Local\Programs\Python\Python312\Lib\site-packages\xlwings\utils.py", line 591, in fullname_url_to_local_path
    return search_local_sharepoint_path(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\MYUSERNAME\AppData\Local\Programs\Python\Python312\Lib\site-packages\xlwings\utils.py", line 752, in search_local_sharepoint_path
    raise xlwings.XlwingsError(
xlwings.XlwingsError: Your SharePoint configuration either requires your workbook name to be unique across all synced SharePoint folders or you need to edit the SHAREPOINT_WIN setting including one or more folder levels, see: xlwings.org/error.

I've tried the following steps to mitigate:

1. Insert the _xlwings.conf generated from "xlwings quickstart", renamed it to xlwings.conf, inserted a row with SHAREPOINT_WIN (column A) and %USERPROFILE%\MYCOMPANYNAME\SITENAME (column B)
2. Create a user environment variable SHAREPOINT_WIN : %USERPROFILE%\MYCOMPANYNAME\SITENAME
3. Create a folder .xlwings below %USERPROFILE% folder, create an xlwings.conf file therein with contents from https://docs.xlwings.org/en/stable/addin.html#user-config-ribbon-config-file, adapting SHAREPOINT_WIN to be %USERPROFILE%\MYCOMPANYNAME\SITENAME

Everything works fine when downgrading to xlwings-0.30.13.

[fzumstein]

This is a regression caused by #2396

For now, I'd just downgrade to xlwings 0.30.13 again (pip install xlwings==0.30.13).

How are you running xlwings? via script or via button/udf in Excel?

[timo-stein]

Hi Felix, I'm always running xlwings via .py script.

[fzumstein]

Ok, I am thinking about refactoring this completely. For your use case, would it be an issue if the files have to be closed before running the script?

[timo-stein]

Not really. There are three scenarios in my applications:
a) Read data from an XLSX and use it. For this one, I'm usually making use of a try: pd.read_excel(...) except PermissionError: wb = xw.Book(...) logic.
b) Create an XLSX instance and write stuff to it using xlwings.
c) Read data from an XLSX, make use of it, and write back to XLSX. Two scenarios can arise: i) XLSX is not opened yet, so xlwings will create an Excel instance. ii) XLSX is already open. You're thinking of closing this one and reopen it? As long as other active Excel instances are not affected, I would only perceive this as a cosmetic drawback.

[fzumstein]

I'd probably show an error rather than closing files automatically, but will have to look into it more. One more question: with 0.30.13, did you have to configure anything?

[timo-stein]

Just tested it with 0.30.13 after disabling all "SHAREPOINT_WIN" sources (xlwings.conf sheet, user environment vars, %USERPROFILE%.xlwings\xlwings.conf) and restarting all applications to re-read env vars. It works without specification of any of the three for me.

One more comment: There are two ways for me to start xlwings, one is from VSCode, the other one is by clicking an ActiveX command button in Excel. That would probably not work anymore with your current idea of restarting Excel somewhere in the process, I guess.

[fzumstein]

I wouldn't restart Excel but rather show an error, and no that would't be done anyways for the RunPython case. Thanks for reporting your outcome with 0.30.13. Maybe @robertschulze has an idea what's going on...

[robertschulze]

Yes, let's try to figure out the issue. So if I understand correctly, in case you have the SHAREPOINT_WIN in xlwings.conf sheet configured an error is shown that you need to configure SHAREPOINT_WIN but in case you don't specify it everything works fine?

Please keep in mind that in 0.30.13 there were cases in which SHAREPOINT_WIN was actually ignored. So this regression could actually be a progression in the sense that a wrong SHAREPOINT_WIN path could previously have been ignored rather than raising an error (and now is). Can you confirm that the SHAREPOINT_WIN path is correct?

[timo-stein]

If no SHAREPOINT_WIN is configured at all, everything works fine using 0.30.13. Using 0.30.14 or 0.30.15, things won't work anymore, independent of whether SHAREPOINT_WIN is set (using either of the three methods) or not.

And to be very honest with you: I'm using xlwings for around two years now and went through every release during that time. Though the connection to SharePoint docs was troublesome with certain releases, I've never had the feeling that SHAREPOINT_WIN helped me out to fix things. Usually, downgrading to an older version was the way to go according to my memory.

This is how the folder structure looks like:
C:\Users\MYUSERNAME\MYCOMPANYNAME\SITENAME1
C:\Users\MYUSERNAME\MYCOMPANYNAME\SITENAME2
C:\Users\MYUSERNAME\MYCOMPANYNAME\SITENAME3
...

When trying to use SHAREPOINT_WIN, I've always used %USERPROFILE%\MYCOMPANYNAME or %USERPROFILE%\MYCOMPANYNAME\SITENAME1. I'm pretty confident that the path should be fine.

[robertschulze]

@timo-stein
Thanks for clarifying. So what I changed in #2397 is to prefer SHAREPOINT_WIN (if it is set) to the logic seeking through directories (cf. here). So if it was working for you previously, a naive guess would be that previously your SHAREPOINT_WIN variable (regardless of the way it was specified) was ignored and the file was found via the seeking logic. Can you confirm that it previously worked without SHAREPOINT_WIN variable?

In any case, if I read your error message correctly, your SharePoint is identified to be of "SharePoint Online & On-Premises (default top level mapping)" kind (code line 591). And from the look of the paths you wrote in the last post, this is appears to be correct. However, when trying to open the file (591 calling search_local_sharepoint_path) it fails to line 752, i.e. len(local_book_paths) is not 0 or 1 but instead the filename you are trying to open exists at least two times in your SHAREPOINT_WIN directories.

Can you try to copy the file to an individual subdirectory and set SHAREPOINT_WIN to this individual subdirectory?
Looking forward to see your results.

[vintzvintz]

Just posting into this thread to say that i also had to revert to 0.30.13, and share experience and opinions.

I am on a corporate onedrive folder, with sharepoints (a.k.a. "Teams channels") mounted at arbitraty points of a "main" onedrive folder. Since ~2021,xlwings found workbooks consistently, with automatic opening when not already open, (which is nice), all without any SHAREPOINT_WIN parameter.

I stepped into xlwings code on 30.13 and found out that on-disk path is discovered succesfully through a Registry scan. Other discovery techniques would probably be unreliable, or always fail, on a setup like mine.
0.30.14/15 changed the discovery logic/sequence, and an exception is now raised early, before even trying the registry to find local path. (can provide more details if requested)

In my specific use case :

• the need to hardwire sharepoint paths somewhere would not be considered as an improvement, and maybe not flexible enough to cover non-root level or nested sharepoint mounts.

• Mandatory closing/reopening workbooks would also be a showstopper with the large files i have to deal with, taking sometimes tenths of seconds to open/close on a regular corp. laptop.

Anyways, thanks for providing a free-tier version of xlwings, and shame to MS for its user-hostile, full-cloud, nothing-local "vision".