Python: xlwings and sub directories

Question

I have 2 functions. I want to check if a file exist in a sub directory.

def XlCall():
    wb = xw.Book.caller()
    from pathlib import Path
    entries = Path('./History/data.csv')
    wb.sheets('TA').range('A1').value = entries.exists()

and..

def main():
    wb = xw.Book("Test.xlsm")
    from pathlib import Path
    entries = Path('./History/data.csv')
    wb.sheets('TA').range('A1').value = entries.exists()

First function is called from Excel (xlwings) and the other is called from python.

When I run from python it works (entires.exists()=True) but when calling from Excel it will be False.

Why? I think it is about PYTHONPATH but just can't find a workaround.

Files on OneDrive (if it matter).

I also tried to use os.path & exists() but same result.

Answer 1

You are looking for a file on a relative path, which may be resolved differently in Excel and a separately run python script.

By default, the current working directory in Excel is a path, which you can find in options: File/Options/Save/Default local file location (usually it's kind of C:\Users\me_as_user\Documents). For a script, the initial working folder is the one from which it was launched. When you run the function from Excel, the full path derived from the relative one can look like C:\Users\you\Documents\History\.... While the script executed from OneDrire maybe returns something like C:\Users\you\OneDrive\History\...

IMO a reliable way to solve the problem would be to use os.environ["OneDrive"] instead of "./" as a starting point:

from os import environ
from pathlib import Path

OneDrive = Path(environ["OneDrive"])
entries = OneDrive / 'History/data.csv'
...