0

I am using Python and PyCharm Community and am trying to automate some of my Excel work. I'm very new to Python. I have created a test of sorts to figure out before I run the code using my actual excel files.

I want to:

  1. Open a specific excel file, in this case: "MarcTest.xlsm". This is an xlsm file with NO saved macros.

  2. Open my PERSONAL.XLSB file which contains ALL of my saved macros.

  3. Run a specific Macro (Format_Blank) from my PERSONAL.XLSB file on my MarcTest.xlsm file.

The problem I have is that my code only executes the Macro in the PERSONAL.XLSB file and I can't find a solution on how to do it otherwise.

I have tried to do the guide located here: https://redoakstrategic.com/pythonexcelmacro/ But since I already have the excel file created half of the guide is useless to me. Either way, the process of zipping the file etc. is cumbersome, and in the end, it doesn't work for me.

Here is my current code:

import win32com.client

xl = win32com.client.Dispatch('Excel.Application')

xl.Visible = True

File = xl.Workbooks.Open(Filename=r'Filepath to MarcTest.xlsm')

MacroFile = xl.Workbooks.Open('Filepath to PERSONAL.xlsb')

xl.Application.Run('PERSONAL.XLSB!Format_Blank')

The result is the macro runs in PERSONAL.XLSB and not MarcTest.xlsm. I do not get any errors.

If I do:

xl.Application.Run('Format_Blank')

I get the same result, also with no errors.

If I do:

xl.Application.Run('MarcTest.xlsm!Format_Blank')

I get the error: 

Traceback (most recent call last):
  File "C:/Users/ichoc/PycharmProjects/mongodb/Editing the Excel Doc.py", line 46, in <module>
    xl.Application.Run('MarcTest.xlsm!Format_Blank')
  File "<COMObject <unknown>>", line 14, in Run
  File "C:\Users\ichoc\Anaconda3\lib\site-packages\win32com\client\dynamic.py", line 287, in _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', "Cannot run the macro 'MarcTest.xlsm!Format_Blank'. The macro may not be available in this workbook or all macros may be disabled.", 'xlmain11.chm', 0, -2146827284), None)

This leads me to believe that I have to somehow migrate the Macro from PERSONAL.XLSB to MarcTest.xlsm. I have no idea how this would be done or even if that is what's needed to be done.

Any help would be much appreciated.

Marc Bright-Chochlekov
  • 23
  • 2
  • This is an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). Please provide more background of your fuller process, namely a sample of macro code, to show your *x* problem and not proposed *y* solution of two workbooks. If you are splitting code and data across workbooks, why not just have macro read in the MarcTest file without launching it in Python? – Parfait Sep 30 '19 at 19:39
  • There is no "2 workbook" solution. The python code is supposed to remove the step of me having to manually go into an excel file and execute the macro. The process from start to finish should be: 1) Open "MarcTest.xlsm" using python 2) Find the macro: Format_Blank 3) execute the "Format_Blank" Macro And that's it. Generically, I need to open an excel file, and execute a macro not contained in that file. – Marc Bright-Chochlekov Oct 01 '19 at 20:16

0 Answers0