0

I'm using win32com to run macros in excel and openpyxl to modify cell values. In the process of debugging, I attempted to create a simplified version of existing code but still ran into the same

[Errno 13] Permission denied:'C:\\Users\\NAME\\Desktop\\old\\Book1.xlsx'.

I believe that the error is caused by the two packages (win32com and openpyxl) opening the same file and, when attempting to save/close, cannot close the instance open in the other package. When I attempt to save/close with openpyxl before saving/closing with win32com, I run into the permission denied error. This makes sense; Openpyxl probably does not have permission to close the excel instance open through win32com. Code is below:

wb.save(r"C:\Users\NAME\Desktop\old\Book1.xlsx")
xel.Workbooks(1).Close(SaveChanges=True)

However, when I switch the order:

xel.Workbooks(1).Close(SaveChanges=True)
wb.save(r"C:\Users\NAME\Desktop\old\Book1.xlsx")

Excel attempts to save a backup file (randomly named "522FED10" or "35C0ED10", etc.) and when I press save, Excel crashes.

What's the workaround? I was thinking that you could use win32com to run the macros, save under a different filename, then use openpyxl to access that file and edit values. However, this is extremely inefficient (I'm dealing with excel files that have hundreds of thousands of rows of data). I could consider just using win32com, but that would require a revamp of a system.

Simple code:

import openpyxl as xl
import win32com.client

xel=win32com.client.Dispatch("Excel.Application")
xel.Workbooks.Open(Filename=r"C:\Users\NAME\Desktop\old\Book1.xlsx")
wb = xl.load_workbook(r"C:\Users\NAME\Desktop\old\Book1.xlsx")
ws = wb.active
xel.visible = False
xel.Cells(1,1).Value = 'Hello Excel'
ws.cell(row = 1,column = 2).value = "test"
xel.Workbooks(1).Close(SaveChanges=True)
wb.save(r"C:\Users\NAME\Desktop\old\Book1.xlsx")
Anshu
  • 1,277
  • 2
  • 13
  • 28
Daniel Wang
  • 1
  • 2

1 Answers1

1

Current issue

You should definitely not mix win32com and openpyxl operations.

The win32com statement xel.Workbooks.Open() loads the workbook contents into a memory space controlled by an Excel process. The openpyxl xl.load_workbook() statement on the other hand loads the workbook contents into a completely separate memory space controlled by a Python process.

Hence any subsequent win32com commands will do nothing to affect the workbook that's living inside the python-process-controlled memory, and vice versa any openpxyl commands will do nothing to affect the workbook that's living inside the Excel-process-controlled memory.

Solution

You mentioned that you have to run some excel macros. This rules out an openpyxl-only solution. My suggestion would be to use xlwings, which is in essence a powerful and user-friendly wrapper around the win32com API.

Here is a simple example of how you can execute Excel macros and manually update cell values within a single python script:

import xlwings as xw

# Start Excel app (invisibly in the background)
app = xw.App(visible=False)

# Load excel file into active Excel app
book = app.books.open(r"Book1.xlsm")

# Instruct Excel to execute the pre-existing excel macro named "CleanUpMacro"
book.macro("CleanUpMacro")()

# Instruct Excel to write a cell value in the first sheet
book.sheets["Sheet1"].range('A1').value = 42

# Save workbook and terminate Excel application
book.save()
book.close()
app.kill()
Xukrao
  • 8,003
  • 5
  • 26
  • 52
  • @DanielWang You're welcome. If this answer helped you, then please [upvote it and/or mark it as the accepted answer](https://stackoverflow.com/help/someone-answers). – Xukrao Aug 22 '19 at 14:40