Problem using variable in Python Script

[Aldemir]

I don’t know why but when I put the path in the code it works and when I use the variable it doesn’t works.
I’m using the library “xlwings” to open Excel macro into Python. Don’t ask me why because it is a experience I’m trying to run VBA macro into Knime using Python.
I’m using:
import xlwings as xw
and after:
wb = xw.Book(r"C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm")

The message:
No such file: ’ r"c:\users\efim\onedrive - petrobras\documents\custofiscal\cji3.xlsm"’
Traceback (most recent call last):
File “”, line 25, in
File “C:\ProgramData\Anaconda3\lib\site-packages\xlwings\main.py”, line 528, in init
impl = app.books.open(fullname, update_links, read_only, format, password, write_res_password,
File “C:\ProgramData\Anaconda3\lib\site-packages\xlwings\main.py”, line 2945, in open
raise FileNotFoundError(“No such file: ‘%s’” % fullname)
FileNotFoundError: No such file: ’ r"c:\users\efim\onedrive - petrobras\documents\custofiscal\cji3.xlsm"’

[Aldemir]

I tried this and it works. When I use the same path in the variable it doesn’t works

[Aldemir]

@Aldemir

variable it doesn’t work

Variable used:

[bruno29a]

Hi @Aldemir , I’m not familiar with using r"", however, you can see that the values you are comparing are different.

r"C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm" itself is going to convert the string that’s quoted into a raw string.

You can think of “r” as a function, and if I’m not mistaken, it’s the same as the function repr(), so it’s similar to calling repr("C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm")

So, when do:
macro = r"C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm"

macro actually has the raw representation of "C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm", which is a valid path that you can use in xw.Book().

Similarly of course when you are passing it directly like this:
xw.Book(r"C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm")

that value gets translated first, and then used in xw.Book().

To get to the point that I am trying to make, let’s replace r with repr().

So you understand why the 2 above cases work:

macro = repr("C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm")
xw.Book(macro)

and
xw.Book(repr("C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm"))

Now, if we look at the Flow Variables, it does not contain the results of r"C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm", but rather the string of r"C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm"

So, when you are using the Flow Variables, you are passing the whole thing with r as a string, including the r

In a sense, you are trying to call:
xw.Book("r\"C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm\"")

or, if using the function repr():
xw.Book("repr(\"C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm\")")

So, you are literally trying to access a path called:
r"C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm"

instead of what the results it should yield, and that’s why you can see it in the error message too
No such file: ’ r"c:\users\efim\onedrive - petrobras\documents\custofiscal\cji3.xlsm"’

The way it should be done is that your variable should just contain this:
C:\Users\efim\OneDrive - PETROBRAS\Documents\CustoFiscal\AbreSAP.xlsm

r or repr() are basically functions from Python, so you need to execute them in Python.
Assuming that you store only the string path in your flow variable macro, then you can do:

macro = repr(flow_variables['macro'])
wb = xw.Book(macro)

Alternatively, if I’m not mistaken, you can also run a Python code from a string within Python, using the exec() function.

So, leaving your flow variable as is, you could also try:
wb = xw.Book(exec(flow_variables['macro']))