xlwings not working with OneDrive when the modules full folder location has a '-' in it

[zecaclasher]

macOS Monterey / xlwings 0.27.5 / Excel Office 365 / Python 3.9.7

---

I managed to locally run a macro successfully but I am having issues to run the same macro from OneDrive. I just copied and pasted the excel file and the python files, nothing changed.

On troubleshooting, I noticed that if the local folder containing the python files includes a '-' in its name, I get a modulenotfound error even if it was normally working before. However, if I replace the '-' by a '_', it works normally.

It happens that OneDrive changed its location from /Users/abc/OneDrive (xlwings used to work fine) to /Users/abc/Library/CloudStorage/OneDrive-Personal/ which contains a '-' and now I'm getting this modulenotfound error.

I found a workaround which is to add the python folder to the PYTHONPATH in the addin - inside Excel- but it has some side effects as this environment variable is global and shared among all the workbooks. So, everytime I open a different workbook, I need to update PYTHONPATH.

Before, each workbook had its own xlwings.conf with its own PYTHONPATH and I had to set it up once instead of everytime I open another different workbook.

I have tried several solutions from previous issues in github and stackoverflow but had no luck.

Any ideas on how to troubleshoot it further?

Thanks in advance

I knew this time would come, I've updated all my workbooks removing formulas and vba macros and replacing them with Python code. I am now totally dependent on xlwings!

[zecaclasher]

@fzumstein Thanks for the quick update.

I have installed the new version 0.27.6 but I am still having a similar issue.

The Python code works fine when executed from VS Code and updating the workbook.

** All tests below were executed with the same working files.**

---

Test 1 - Local files - PYTHONPATH folder contains a '_' and local xlwings.conf

• Workbook location: /Users/cr/projects/yield_curve.xlsm
• Local (same directory) xlwings.conf file: "PYTHONPATH","/Users/cr/projects/yield_curve"
• Global xlwings.conf file (PYTHONPATH in the addin): blank

This setting works fine on clicking a button which calls a VBA function with a RunPython call.

---

Test 2 - Local files - PYTHONPATH folder contains a '-' and local xlwings.conf

• Workbook location: /Users/cr/projects/yield_curve.xlsm
• Local (same directory) xlwings.conf file: "PYTHONPATH","/Users/cr/projects/yield-curve"
• Global xlwings.conf file (PYTHONPATH in the addin): blank

This new setting - which only replaced '_' by '-' in the PYTHONPATH - raises an error:

Traceback (most recent call last):
File "<string>", line 1, in <module>
ModuleNotFoundError: No module named 'portfolio'.

---

Test 3 - Local files - PYTHONPATH folder contains a '-' and global xlwings.conf

• Workbook location: /Users/cr/projects/yield_curve.xlsm
• Local (same directory) xlwings.conf file: None
• Global xlwings.conf file (PYTHONPATH in the addin): "PYTHONPATH","/Users/cr/projects/yield-curve"

This setting works fine on clicking a button which calls a VBA function with a RunPython call.

---

Conclusion: The issue appears only when there is a '-' in the PYTHONPATH environment variable used in the local xlwings.conf. All works fine if the same PYTHONPATH is set in the global xlwings.conf.

---

Test 4 - OneDrive and local xlwings.conf

• Workbook location: /Users/cr/Library/CloudStorage/OneDrive-Personal/projects/yield_curve.xlsm
• Local (same directory) xlwings.conf file: "PYTHONPATH","/Users/cr/Library/CloudStorage/OneDrive-Personal/projects/yield_curve"
• Global xlwings.conf file (PYTHONPATH in the addin): blank

This setting raises no error message but it runs very quickly and nothing happens.

---

Test 5 - OneDrive and global xlwings.conf

• Workbook location: /Users/cr/Library/CloudStorage/OneDrive-Personal/projects/yield_curve.xlsm
• Local (same directory) xlwings.conf file: None
• Global xlwings.conf file (PYTHONPATH in the addin): "PYTHONPATH","/Users/cr/Library/CloudStorage/OneDrive-Personal/projects/yield_curve"

This setting works fine on clicking a button which calls a VBA function with a RunPython call.

---

Conclusion:

The issue with OneDrive is happening now because the OneDrive location has changed from /Users/cr/OneDrive to /Users/cr/Library/CloudStorage/OneDrive-Personal/. The latter has a '-' and hence the reason for the error. However, xlwings can work with any folder if using a global xlwings.conf but not if a local one is used.

---

Workaround

A quick workaround for the issue in question is to use a global xlwings.conf but the side effect is that everytime I open a different workbook, I need to update the PYTHONPATH env variable. Unfortunately, I have more than 10 workbooks with different frequencies of use but I use them all at least weekly, a few others daily and simultaneously opened.

---

Further troubleshooting

• Can the default OneDrive location be modified so it contains no '-'?
• Check xlwings open source code for the logic behind using the global or the local xlwings.conf. Why does this issue happen only with the local xlwings.conf?
• Is there any log file I can check for the errors in more detail?

[fzumstein]

So is your file structure the following?

/Users/cr/Library/CloudStorage/OneDrive-Personal/projects/
    |- yield_curve.xlsm
    |- yield_curve
         |- portfolio.py

[zecaclasher]

So is your file structure the following?

/Users/cr/Library/CloudStorage/OneDrive-Personal/projects/
    |- yield_curve.xlsm
    |- yield_curve
         |- portfolio.py

@fzumstein yes, that is correct

[fzumstein]

I can't replicate the issue with the dash in the folder name, so I think there's something else going on. I'd recommend you to set the configuration in the xlwings.conf sheet instead of using directory-based config files. You may want to also set the new ADD_WORKBOOK_TO_PYTHONPATH setting to FALSE if you're explicitly providing the PYTHONPATH anyway.

[zecaclasher]

ADD_WORKBOOK_TO_PYTHONPATH is already set to FALSE.

I am gonna check if I can modify the default OneDrive location otherwise gonna move to the sheet-based config files.

Thanks

[zecaclasher]

I have changed the OneDrive location back to /Users/cr/OneDrive and I am now again able to use directory-based config files from OneDrive.

[zecaclasher]

Just for the record to whoever happens to have the same issue.

OneDrive changed the location back to /Users/cr/Library/CloudStorage/OneDrive-Personal/ after I rebooted the laptop.

I ended up using the sheet-based config files for OneDrive as recommended.

Thanks to @fzumstein for the support.