xlwings returning incorrect filepath than input file specified

[htalukdar]

OS (e.g. Windows 10 or macOS Sierra)

Windows 10

Versions of xlwings, Excel and Python (e.g. 0.11.8, Office 365, Python 3.7)

Python 3.11.4
xlwings 0.30.15

Describe your issue (incl. Traceback!)

When using the command

wb = xw.Book(filepath)

the filepath returned is not the one I have assigned to the variable filepath.

Even when passing in the full filepath (instead of assigning to the variable filepath), I receive an error with the script seemingly attempting to access a non-existent file (this 'other' file sits in another location and has previosly been deleted.

# Your traceback here

FileNotFoundError                         Traceback (most recent call last)
Cell In[24], line 1
----> 1 wb = xw.Book(filepath)

File ~\anaconda3\Lib\site-packages\xlwings\main.py:775, in Book.__init__(self, fullname, update_links, read_only, format, password, write_res_password, ignore_read_only_recommended, origin, delimiter, editable, notify, converter, add_to_mru, local, corrupt_load, impl, json)
    773 for app in apps:
    774     for wb in app.books:
--> 775         if wb.fullname.lower() == fullname or wb.name.lower() == fullname:
    776             candidates.append((app, wb))
    778 app = apps.active

File ~\anaconda3\Lib\site-packages\xlwings\main.py:995, in Book.fullname(self)
    989 @property
    990 def fullname(self):
    991     """
    992     Returns the name of the object, including its path on disk, as a string. Read-only String.
    993 
    994     """
--> 995     return self.impl.fullname

File ~\anaconda3\Lib\site-packages\xlwings\_xlwindows.py:715, in Book.fullname(self)
    713 if '://' in self.xl.FullName:
    714     config = read_config_sheet(xlwings.Book(impl=self))
--> 715     return fullname_url_to_local_path(url=self.xl.FullName,
    716                                       sheet_onedrive_consumer_config=config.get('ONEDRIVE_CONSUMER_WIN'),
    717                                       sheet_onedrive_commercial_config=config.get('ONEDRIVE_COMMERCIAL_WIN'),
    718                                       sheet_sharepoint_config=config.get('SHAREPOINT_WIN'))
    719 else:
    720     return self.xl.FullName

File ~\anaconda3\Lib\site-packages\xlwings\utils.py:478, in fullname_url_to_local_path(url, sheet_onedrive_consumer_config, sheet_onedrive_commercial_config, sheet_sharepoint_config)
    476 book_name = url.split('/')[-1]
    477 local_book_paths = []
--> 478 for path in Path(root).rglob('[!~$]*.xls*'):
    479     if path.name == book_name:
    480         local_book_paths.append(path)

File ~\anaconda3\Lib\pathlib.py:968, in Path.rglob(self, pattern)
    966     pattern_parts.append('')
    967 selector = _make_selector(("**",) + tuple(pattern_parts), self._flavour)
--> 968 for p in selector.select_from(self):
    969     yield p

File ~\anaconda3\Lib\pathlib.py:408, in _RecursiveWildcardSelector._select_from(self, parent_path, is_dir, exists, scandir)
    406 successor_select = self.successor._select_from
    407 for starting_point in self._iterate_directories(parent_path, is_dir, scandir):
--> 408     for p in successor_select(starting_point, is_dir, exists, scandir):
    409         if p not in yielded:
    410             yield p

File ~\anaconda3\Lib\pathlib.py:355, in _WildcardSelector._select_from(self, parent_path, is_dir, exists, scandir)
    353 def _select_from(self, parent_path, is_dir, exists, scandir):
    354     try:
--> 355         with scandir(parent_path) as scandir_it:
    356             entries = list(scandir_it)
    357         for entry in entries:

File ~\anaconda3\Lib\pathlib.py:938, in Path._scandir(self)
    934 def _scandir(self):
    935     # bpo-24132: a future version of pathlib will support subclassing of
    936     # pathlib.Path to customize how the filesystem is accessed. This
    937     # includes scandir(), which is used to implement glob().
--> 938     return os.scandir(self)

FileNotFoundError: [WinError 3] The system cannot find the path specified: 'different fielpath returned here'

#### Include a minimal code sample to reproduce the issue (and attach a sample workbook if required!)

```python
wb = xw.Book(filepath)

[htalukdar]

Further context:

I have found a fix by using xw.books.open(filepath) which does the trick only when the file is CLOSED.

If the file is open on running the script, the filepath returned is incorrect.

[fzumstein]

Can you give an example of the provided filepath and the one it tries to open? You may have to configure SharePoint/OneDrive according to this here: https://docs.xlwings.org/en/latest/onedrive_sharepoint.html

[htalukdar]

As an example, this will be the input file:

filepath = r'C:\Users\HasinaTalukdar\Comp Name\Customer Submitted Data - Documents\14. Granularity Review\Phase 9\TEST.xlsx'

wb = xw.Book(filepath)

will be called in this function:

def get_risk_codes(filepath,input_tab_name):
wb = xw.Book(filepath)
ws = wb.sheets[input_tab_name]
input_df = ws.used_range.options(pd.DataFrame,index=False, header=True).value

...

and this error message is returned:

FileNotFoundError: [WinError 3] The system cannot find the path specified: 'C:\Users\HasinaTalukdar\Comp Name\Data Operations - Documents\1. Archive\Historic Files\2. Data Journey - Failed Copy -KEEP\Rematching\Changes\DTS 1.3 Data remediation\Archive\Database Upload Files\For Shadow List - July 2020'

[fzumstein]

thanks, is this on SharePoint?