passing argument in xlwings RunPython VBA function

Question

I am getting some data from excel sheets using python. I'm using xlwings to accomplish this. I am trying to pass the path to the current workbook to my file, where it can use it to find the Excel book. I cannot use xlwings UDF's.

Here's my VBA code:

Sub Button1_Click()

    RunPython ("import exceltest; exceltest.excelTest('" & ThisWorkbook.FullName & "')")

End Sub

Here's my exceltest.py Python code:

def excelTest(path_of_file):
    print (path_of_file)

Here's the error I'm getting:

Error

  File "<string>", line 1

SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape

Any help would be greatly appreciated!

The path ThisWorkbook.FullName probably has a special slash character in it that Python is interpreting as something else. What characters follow the slashes in the path? — MBB70
– MBB70, Commented Jan 16, 2019 at 19:37
@MBB70 when I run Debug.Print ThisWorkbook.FullName it outputs C:\Users\gageb\Desktop\test.xlsm — FishMonkey
– FishMonkey, Commented Jan 16, 2019 at 19:48
Your error is telling you that there is a problem at character 2-3...so I would suspect \U...Can you hard code the workbook path, or will it vary? If you can, try the answer I suggested. r'''C:\Users\gageb\Desktop\test.xlsm''' — MBB70
– MBB70, Commented Jan 16, 2019 at 19:51

MBB70 · Accepted Answer · 2019-01-16 19:42:08Z

1

Use the full path of your workbook (the actual path instead of ThisWorkbook.FullName and structure it like this r'''myfullpath\mysubfolder\myExcelfile'''

answered Jan 16, 2019 at 19:42

MBB70

3673 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FishMonkey Over a year ago

Yes, this would work, but this macro will be running on an Excel template, I need to know where the user has saved the file.

MBB70 Over a year ago

Try ... myWBName = "r'''" & ThisWorkbook.FullName & "'''" then RunPython ("import exceltest; exceltest.excelTest('" & myWBName & "')")

FishMonkey · Accepted Answer · 2019-01-16 20:05:42Z

0

I needed to use RunPython ("import exceltest; exceltest.excelTest(r'" & ThisWorkbook.FullName & "')")

answered Jan 16, 2019 at 20:05

FishMonkey

1984 silver badges15 bronze badges

3 Comments

MBB70 Over a year ago

It would be good if you could tell me if the method I suggested worked also. I don't have xlwings installed. If the only difference between your answer and mine is slightly different quote structure, I'd like to know.

MBB70 Over a year ago

"Any help would be greatly appreciated!"

MBB70 Over a year ago

"Great appreciation" would be minimally expressed with at the very least an upvote of my comments and/or answer. It would take you less than a second to do this. I don't need a pat on the back here, but the fact is that my insight led you directly to your answer. It doesn't seem right for you to treat this community like an answer-mill, turning your back once you have what you need. I'm new too, but it's clear already to me that there is a culture of mutual respect here.

Collectives™ on Stack Overflow

passing argument in xlwings RunPython VBA function

Error

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Error

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related