mac: cannot handle sheet names with spaces inside

[zogzog]

More precisely, given two sheets:

<Name 'rc_past': =through_time!$E$5:$F$11>
<Name 'rw_digit': ='space bar'!$E$8:$E$17>

when asking .refers_to_range.sheet.name on the second, one gets a nasty

E                   appscript.reference.CommandError: Command failed:
E                   		OSERROR: -1728
E                   		MESSAGE: The object you are trying to access does not exist
E                   		COMMAND: app(pid=75015).workbooks['proto_validator.xlsx'].worksheets["'space bar'"].name.get()

[zogzog]

The followin diff seems to "fix" it:

@@ -1033,6 +1035,9 @@ class Name(object):
     @property
     def refers_to_range(self):
         ref = self.refers_to[1:].split('!')
+        sheetname = ref[0]
+        if sheetname.startswith("'") and sheetname.endswith("'"):
+            ref[0] = sheetname[1:-1]
         book = self.parent if isinstance(self.parent, Book) else self.parent.book
         return Range(Sheet(book, ref[0]), ref[1])

[fzumstein]

Can you please include a self contained example? I can't replicated with the following code:

import xlwings as xw

wb = xw.Book()
wb.sheets.add('space bar')
wb.sheets['space bar'].names.add('test1', '=Sheet1!$A$1:$B$3')
print(wb.sheets['space bar'].names['test1'].refers_to_range.sheet.name)

neither with

import xlwings as xw

wb = xw.Book()
wb.sheets.add('space bar')
wb.names.add('test1', '=Sheet1!$A$1:$B$3')
print(wb.names['test1'].refers_to_range.sheet.name)

maybe you're mixing workbook and sheet scope?

[zogzog]

That was done with a real xl file. I don't understand the "mixing workbook and sheet scope" part.

What's happening is an iteration on the names of a workbook, like that:

for xlname in wb.names:
    sheet_name = xlname.refers_to_range.sheet.name

[fzumstein]

Can you please upload a sample spreadsheet with code to replicate the issue. xw.Book() produces a pretty real xl file.

[zogzog]

book.xlsx

Within this book we have a sheet named "ba bar" and a named cell within.

While iterating on the names, we get to the issue:

b = xlwings.Book('book.xlsx')
for n in b.names: 
    print(n.refers_to_range)

=>

Traceback (most recent call last):
File "/Users/vq5355/miniconda3/lib/python3.6/site-packages/aeosa/appscript/reference.py", line 460, in call
return self.AS_appdata.target().event(self._code, params, atts, codecs=self.AS_appdata).send(timeout, sendflags)
File "/Users/vq5355/miniconda3/lib/python3.6/site-packages/aeosa/aem/aemsend.py", line 92, in send
raise EventError(errornum, errormsg, eventresult)
aem.aemsend.EventError: Command failed: The object you are trying to access does not exist (-1728)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "", line 1, in
File "/Users/vq5355/miniconda3/lib/python3.6/site-packages/xlwings/main.py", line 1590, in repr
return "<Range [{1}]{0}!{2}>".format(self.sheet.name, self.sheet.book.name, self.address)
File "/Users/vq5355/miniconda3/lib/python3.6/site-packages/xlwings/main.py", line 779, in name
return self.impl.name
File "/Users/vq5355/miniconda3/lib/python3.6/site-packages/xlwings/_xlmac.py", line 300, in name
return self.xl.name.get()
File "/Users/vq5355/miniconda3/lib/python3.6/site-packages/aeosa/appscript/reference.py", line 496, in call
raise CommandError(self, (args, kargs), e, self.AS_appdata) from e
appscript.reference.CommandError: Command failed:
OSERROR: -1728
MESSAGE: The object you are trying to access does not exist
COMMAND: app('/Applications/Microsoft Excel.app', newinstance=(0, 2888385)).workbooks['book.xlsx'].worksheets["'ba bar'"].name.get()

[fzumstein]

thanks! will look into it