This problem is often caused by using a relative path when trying to open a file, eg "./myfile.txt".
When you run a Python script from your IDE or the command line, the "current working directory" will be set to the same folder as you are running the script from and relative paths are relative to this folder. A common mistake is to think that using a relative path, eg "./myfile.txt", will load a file relative to the Python file itself, but that is not correct - it is relative to the current working directory not the Python file.
When Excel runs, the current working directory will be the folder that your Excel application is installed in. This means that if you speicfy a relative path when loading a file, it will be relative to your Excel application folder and not your Python file. This will result in a FileNotFound error.
It is better to work with absolute paths as that removes the uncertainty of what the current working directory is. You can construct an absolute path using Python's "pathlib" module (https://docs.python.org/3/library/pathlib.html) or using os.path (https://docs.python.org/3/library/os.path.html).
For example, if you want to read file the "./myfile.txt" relative to the Python file, you could do the following:
from pathlib import Path
# Path relative to this Python file
relative_path = "./myfile.txt"
# Get the directory this Python file is in
dir = Path(__file__).parent
# Get the path relative to the Python file's directory
absolute_path = dir.joinpath(relative_path)
# Open the file using the absolute path
with open(absolute_path, "rt") as fh:
data = fh.read()
If you are using Python 2 or prefer not to use pathlib you can use os.path, for example:
import os
# Path relative to this Python file
relative_path = "./myfile.txt"
# Get the directory this Python file is in
dir = os.path.dirname(__file__)
# Get the path relative to the Python file's directory
absolute_path = os.path.join(dir, relative_path)
# Open the file using the absolute path
with open(absolute_path, "rt") as fh:
data = fh.read()