What is the default directory of the code when using Xlwings pro (non-commercial)

Question

I am using non-commercial version of the XLWings Pro and having issues tracing the default directory of the code when it is run from a worksheet in the Excel file.

Just to be clear...say I have an Excel file named ABC.xlsx. It has 2 worksheets:

1. Sheet 1
2. ABC.py

what is the active directory for the code in ABC.PY

I tried

def test():

    wb = xw.books.active

    sheet = wb.sheets[0]

    sheet.range("A1").value = os.getcwd()

Answer 1

Calling a UDF through Excel

Testing with the following UDF, I found that no matter where the Excel and Python files were saved (even when in different directories from one another), the output returned was the "C:\Users\username\Documents" directory:

import os
import xlwings as xw

@xw.func
def cwd():
    wb = xw.Book.caller()
    x = os.getcwd()
    return x

Running the function outside of Excel/xlwings:

However, if you are running the python script itself outside of Excel/xlwings through Excel, then it will depend on where you are running the script from.

Using the below code (similar to yours), then the output to cell A1 will change by where I run the code from.

# python script saved in "C:\Users\username\Desktop"

import os
import xlwings as xw

file = r'C:\Users\username\Book1.xlsm'
ws = xw.Book(file).sheets("Sheet1")
ws.range("A1").value = os.getcwd()

1. By just opening the file in file explorer (thus running the script), the directory is the folder that the Python script is saved in (in this case the Desktop).
2. By opening the cmd and starting in a different directory, then running python "C:\Users\username\Desktop\pythonscript.py" the output to Excel is the current working directory in the cmd.

See the answers to this question for a more in-depth answer.