1

I spent some time messing with openpyxl and had it doing more or less what I wanted until I introduced some real-world data (only as a test) and discovered it can't handle data validation in the Excel workbook (I think the data validation thing refers to a drop-down list):

UserWarning: Data Validation extension is not supported and will be removed

My code carries on and opens the Excel file but because the validation is removed it breaks it.

Okay, so back to square one, I go away and do some more reading and discover that xlwings can probably handle this. I get xlwings and run the shortest test I can think of:

from pathlib import Path
import xlwings as xw

my_documents = Path('/users/chris/documents')

xw.Book()
wb = xw.Book(my_documents / 'Test.xlsx')
ws = wb.sheets['Sheet1']

That gives a lot of errors for such a small piece of code!

Traceback (most recent call last):
  File "/Users/chris/Documents/Python Projects/venv/lib/python3.8/site-packages/aeosa/appscript/reference.py", line 482, in __call__
    return self.AS_appdata.target().event(self._code, params, atts, codecs=self.AS_appdata).send(timeout, sendflags)
  File "/Users/chris/Documents/Python Projects/venv/lib/python3.8/site-packages/aeosa/aem/aemsend.py", line 92, in send
    raise EventError(errornum, errormsg, eventresult)
aem.aemsend.EventError: Command failed: The user has declined permission. (-1743)

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

Traceback (most recent call last):
  File "/Users/chris/Documents/Python Projects/little_tests.py", line 6, in <module>
    xw.Book()
  File "/Users/chris/Documents/Python Projects/venv/lib/python3.8/site-packages/xlwings/main.py", line 540, in __init__
    app = App()
  File "/Users/chris/Documents/Python Projects/venv/lib/python3.8/site-packages/xlwings/main.py", line 210, in __init__
    self.impl = xlplatform.App(spec=spec, add_book=add_book)
  File "/Users/chris/Documents/Python Projects/venv/lib/python3.8/site-packages/xlwings/_xlmac.py", line 68, in __init__
    self.activate()  # Makes it behave like on Windows
  File "/Users/chris/Documents/Python Projects/venv/lib/python3.8/site-packages/xlwings/_xlmac.py", line 103, in activate
    appscript.app('System Events').processes[its.unix_id == self.pid].frontmost.set(True)
  File "/Users/chris/Documents/Python Projects/venv/lib/python3.8/site-packages/aeosa/appscript/reference.py", line 518, in __call__
    raise CommandError(self, (args, kargs), e, self.AS_appdata) from e
appscript.reference.CommandError: Command failed:
                OSERROR: -1743
                MESSAGE: The user has declined permission.
                COMMAND: app('/System/Library/CoreServices/System Events.app').processes[its.unix_id == 4976].frontmost.set(True)
(venv) chris@Chriss-MacBook-Pro Python Projects %

Unusually for this site, there aren't many references to this here and a wider Google search also doesn't shed any light on it. There seem to be a few people with a similar problem but not so many and nobody I've yet seen seems to have a solution.

Does anybody have any ideas?

tripleee
  • 175,061
  • 34
  • 275
  • 318
OuluChris
  • 41
  • 1
  • 7

3 Answers3

2

The application you are running this from needs to ask for permission on the more recent versions of macOS. So either the Terminal or your IDE like PyCharm or VSCode. Sometimes, the IDEs have bugs and don't properly ask for permissions, so it's a good idea to run your code first from the Terminal to verify that everything works. If the IDE is the issue, it's a good idea to start it via Terminal, too, see the three issues about that topic

Felix Zumstein
  • 6,737
  • 1
  • 30
  • 62
  • Thanks. I had read those three issues along with dozens more in a long and still fruitless search for how to fix this but I went back and re-read them carefully. I think you are right that VSCode didn't properly ask for permissions but at the moment I'd still stuck looking for how to fix that. – OuluChris Jul 21 '20 at 19:21
  • Actually, having thought about it a bit more, I'm not sure it is a VSCode permission issue. If that was the case why can openpyxl do exactly the same thing without throwing off any errors? – OuluChris Jul 21 '20 at 20:15
  • openpyxl doesn't automate applications, it writes files. Open VS code via Terminal and try again. – Felix Zumstein Jul 21 '20 at 21:59
  • Ah, got it, thanks. When run in terminal it's possible to run it without the error being generated although it won't run by itself as it causes pop up boxes (that look like they are part of Mac OS) that ask if I would like to grant access to the file. So, a permission thing as you said. – OuluChris Jul 22 '20 at 11:59
  • The pop up boxes should only come up 1 time - then it's registered and won't bother again. – Felix Zumstein Jul 22 '20 at 16:13
  • Yep, that was also true so I can now (almost) get them open). The last hurdle is either an MS Word or Mac issue which always asks "A file named "/Users/chris/Documents/filename.xlsx" already exists in this location. Do you want to replace it?" despite the XLWings documentation stating that wb.save results in "Existing files are overwritten without prompting" – OuluChris Jul 23 '20 at 18:58
  • That last bit is a bug I believe that creeped in with the latest versions of Excel on mac. Simply wrap it in `app.display_alerts = False` to get rid of it. – Felix Zumstein Jul 24 '20 at 07:54
1

I am using Mac OS too. This method worked for me!

It is generally not a good idea to run VS Code as sudo. Instead, change the permission for the directory.

You can change the ownership of the directory so that you can open it without needing root privileges."

sudo chown -R <user-name> <directory-name>

<directory-name> means that is the directory of your VS code/Pycharm/.... You can find it by dropping the icon from applications in Finder.

EACCES: permission denied in VS Code MAC

guzmonne
  • 2,490
  • 1
  • 16
  • 22
user15926761
  • 11
  • 1
0

I had the same problem on macOS. Launching Pycharm via terminal worked for me.

open /Applications/PyCharm\ CE.app/Contents/MacOS/pycharm

By launching via terminal the IDE will ask for Automation -> Excel Permission, once granted you will be able to execute the code.

yaKay
  • 115
  • 13