1

I am trying to write to a macro enabled excel file (xlsm) and then run a macro that is dependent on the cell my script is writing to. For clarity I am on a windows machine, I am not the admin and I am working with python3.8.0

I have a file called write.py, in this file it writes yesterdays date to the A1 cell unless it is Monday then it writes Fridays date. After it writes the date I would like to run a macro that uses that cell/date to do something.

So far I can write yesterdays date and I can run a macro but I cannot do them in the same file. I tried separating the files and executing the macro from the write.py file but that gave me the same error.

Here is my code for the write.py file that writes yesterdays date to the A1 cell of an xlsm file:

from datetime import date
from datetime import timedelta
import openpyxl

path = "C:/Users/project/file.xlsm"

# loads the workbook into a var
book = openpyxl.load_workbook(path)
sheet = book["Sheet2"] # loads a specific sheet by name into a var
a1 = sheet['A1'] # loads a cell into a var

# testing
print(book.sheetnames)
print(a1.value)

# Get yesterdays date unless its a Monday then get Fridays date
today = date.today() 
if date.today().weekday() == 0: 
    yesterday = today - timedelta(days=today.weekday()) + timedelta(days=4, weeks=-1)
else:
    yesterday = today - timedelta(days = 1)

yestFormat = (yesterday.strftime("%x")) # formatting the date 

# setting the value of the A1 cell to the yesterFormat var
sheet.cell(row=1,column=1,value=yestFormat)
book.save(path)

# This file is supposed to run the macro
exec(open('runMacro.py').read())

Here is the code for the second file that is supposed to run the macro, I have also had this code in the write.py file and I run into the same problems

import xlwings as xw

# loads the workbook into workbook var then loads a specific sheet into worksheet var
wb = xw.Book(r'C:/Users/project/file.xlsm')
wb.macro('test_macro')()
wb.save(r'C:/Users/project/file.xlsm')

When I run the write.py file with the exec command commented out it works and writes the date to the cell. So there is something wrong with the way I am executing the macro. Something I have noticed is if the excel file is open then the write.py file will not work, and the runMacro.py file opens the excel doc when I run it. I think it has something to do with how it is saving or opening but I have read through xlwings documentation and I am doing what it says on how to save, so I am not sure. Hoping there is a better way, thanks for any feedback.

Edit: Sorry I forgot to add the error I am getting. I have changed the file paths for privacy reasons so there might be some inconsistencies there.

Traceback (most recent call last):
  File "C:\Users\project\venv\lib\site-packages\xlwings\_xlwindows.py", line 466, in __call__
    return Book(xl=self.xl(name_or_index))
  File "C:\Users\project\lib\site-packages\xlwings\_xlwindows.py", line 156, in __call__  
    v = self._inner(*args, **kwargs)
  File "C:\Users\username\AppData\Local\Temp\gen_py\3.8\00020813-0000-0000-C000-000000000046x0x1x8.py", line 
39136, in __call__
    ret = self._oleobj_.InvokeTypes(0, LCID, 2, (13, 0), ((12, 1),),Index
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\project\code\venv\lib\site-packages\xlwings\main.py", line 3873, in open
    impl = self.impl(name)
  File "C:\Users\project\venv\lib\site-packages\xlwings\_xlwindows.py", line 468, in __call__
    raise KeyError(name_or_index)
KeyError: 'sample.xlsm'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "c:/Users/project/test.py", line 35, in <module>
    exec(open('test2.py').read())
  File "<string>", line 4, in <module>
  File "C:\Users\username\venv\lib\site-packages\xlwings\main.py", line 547, in __init__        
    impl = app.books.open(fullname, update_links, read_only, format, password, write_res_password,
  File "C:\Users\username\venv\lib\site-packages\xlwings\main.py", line 3879, in open
    impl = self.impl.open(fullname, update_links, read_only, format, password, write_res_password,
  File "C:\Users\username\venv\lib\site-packages\xlwings\_xlwindows.py", line 484, in open      
    return Book(xl=self.xl.Open(fullname, update_links, read_only, format, password, write_res_password,    
  File "C:\Users\username\venv\lib\site-packages\xlwings\_xlwindows.py", line 66, in __call__   
    v = self.__method(*args, **kwargs)
  File "C:\Users\username\AppData\Local\Temp\gen_py\3.8\00020813-0000-0000-C000-000000000046x0x1x8.py", line 
39013, in Open
    ret = self._oleobj_.InvokeTypes(1923, LCID, 1, (13, 0), ((8, 1), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17)),Filename
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', "Excel cannot open the file 'sample.xlsm' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.", 'xlmain11.chm', 0, -2146827284), None)
ssumme
  • 45
  • 5

0 Answers0