Xlwings: run python from Excel file

Question

First, I should say that this code was ok last wee (that means it was running and no problems).

I don't know why, but: 1 - I have a Excel file on OneDrive. This file have the xlwings imported and this function on VBA:

Sub CallSAP()
'Run the Python Script
RunPython ("import LoadSAP; LoadSAP.ScriptSAP()")    
End Sub

2 - The LoadSAP.py was placed on the same path of the Excel file.

This week, I have the issue:

---------------------------
Error
---------------------------
Traceback (most recent call last):

  File "<string>", line 1, in <module>

ModuleNotFoundError: No module named 'LoadSAP'

Press Ctrl+C to copy this message to the clipboard.
---------------------------
OK   
---------------------------

It's similar to xlwings module.py call python from excel

3 - So, I tried to execute the LoadSAP.py, I maked a copy on the C://Documents, I runned the function on Jupyter notebook file, and miraculously the Excel now recognize the LoadSAP. Ps.: I need to know how to fix this issue because I have the same to the loadMaps.py function.

4 - Now, running on the Excel, a new problem appears: the program doesn't recognize the path of OneDrive, and it suggest to change to the link https.

OSError: [WinError 123] The syntax of the file name, directory name, or volume label is incorrect: 'https://.../Documents/.../Excel file.xlsm'

5 - After I did the change like demanded on item 4, on this line code (this read na other Excel file to push some data)

gerder = read_excel(path_gerder + file_gerder, sheet_name='Base')

I have 2 issues:

Sometimes it returns:

'ascii' codec can't encode character '\xea' in position 56: ordinal not in range(128)

Sometimes it returns:

No such file: 'https://.../Documents/.../gerder file.xls'

Ps.: If I use the link to the file on the brownser, it's downloaded.

I have many singulars issues and it worked well just one week ago: the Excel recognized all the *.py, the program readed all the Excel file directly of OneDrive, etcs etcs. So if you have any idea or suggestion, I open to try.

OneDrive currently requires you to set the PYTHONPATH, see: https://github.com/xlwings/xlwings/issues/1275 — Felix Zumstein, Apr 07 '20 at 06:53
This is unconnected with xlwings, see e.g. https://stackoverflow.com/questions/9942594/unicodeencodeerror-ascii-codec-cant-encode-character-u-xa0-in-position-20 — Felix Zumstein, Apr 07 '20 at 19:33

score 0 · Accepted Answer · answered Apr 07 '20 at 20:12

The solution:

First, like Felix Zumstein commented:

1 - Set the PYTHONPATH on Excel file: How to set the PYTHONPATH on Excel by xlwings

2 - I don't know exactly why, but xlwings don't recognize the OneDrive path like 'C:/.../Documents/.../Excel file.xlsm'. So, this error appears:

OSError: [WinError 123] The syntax of the file name, directory name, or volume label is incorrect: 'https://.../Documents/.../Excel file.xlsm'

At the same time, the funcion read_excel of Pandas and others functions don't recognize the OneDrive path like 'https://.../Documents/.../Excel file.xlsm'. And this error should appear:

'ascii' codec can't encode character '\xea' in position 56: ordinal not in range(128)

Solution: I used the path like 'https://.../Documents/.../Excel file.xlsm' to any xlwings functions. For the others functions, I used the path: 'C:/.../Documents/.../Excel file.xlsm'.

Xlwings: run python from Excel file

1 Answers1