Running macro in PERSONAL.XLSB using python

Question

I am trying to run a macro in the Personal.XLSB using python.

When I run the macro PERSONAL.XLSB!PIVOTS myself from the excel workbook it works. Also, if I copy and paste the vba code into "This Workbook" and run xlApp.Run('Pivots') it works.

However, when I use xlApp.Run('C:\\Users\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART\\PERSONAL.XLSB!Pivots') it won't work. I need it to run in 'PERSONAL.XLSB' as I will be using the same macro in several files.

from __future__ import print_function
import unittest
import os.path
import win32com.client

class ExcelMacro(unittest.TestCase):
    def test_excel_macro(self):
        try:
            xlApp = win32com.client.DispatchEx('Excel.Application')
            xlsPath = os.path.expanduser('C:\\Users\\colm_mcsweeney\\Documents\\Attachments\\Dash.09.05.19.xlsm')
            wb = xlApp.Workbooks.Open(Filename=xlsPath)
            xlApp.Visible = True
            xlApp.Run("C:\\Users\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART\\PERSONAL.XLSB!Pivots")
            wb.Save()
            xlApp.Quit()
            print("Macro ran successfully!")
        except:
            print("Error found while running the excel macro!")
            xlApp.Quit()
if __name__ == "__main__":
    unittest.main()

Why do you pass a path instead of the macro name as you did in the example that works? VBA uses COM (OLE) Automation. That means the API it calls is the same API you call through COM. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 10, 2019 at 8:25
I have also tried xlApp.Run('PERSONAL.XLSB!Pivots'). I cannot use xlApp.Run('Pivots') because it would mean I would have to recreate the macro in every new workbook. — Colm McSweeney
– Colm McSweeney, Commented May 10, 2019 at 8:27
And yet you used Run('Pivots') in VBA. The objects, functions, are exactly the same in both cases — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 10, 2019 at 8:28
The filename will change each day to Dash.10.05.19, Dash.11.05.19 etc. — Colm McSweeney
– Colm McSweeney, Commented May 10, 2019 at 8:34
If you want to call a macro on a second workbook, that workbook must be opened first. Run only runs macros, it doesn't load the files. You should add another call to xlApp.Workbooks.Open to load the second workbook before you use .Run("PERSONAL.XLSB"!PIVOTS") — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 10, 2019 at 8:38

Mofongo · Accepted Answer · 2020-10-15 16:00:53Z

The trick is to open your PERSONAL.XLSB file first and then you can open / activate any other Excel wb and run the macros

import win32com.client
from pathlib import Path

# Folder I want to save the file to
folder = Path(r"C:\Users\user_name\folder")

# Your excel file you want to run the macro on
filename = excel.xlxs

save_path = folder / filename

# Sets up Excel to run Macro
try:
    xl = win32com.client.Dispatch('Excel.Application')
    xl.visible = False
    personal_wb = xl.workbooks.Open(
        Filename=r"C:\Users\user_name\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB")
    wb = xl.workbooks.Open(Filename=save_path)
    xl.Run("'PERSONAL.XLSB'!MyMacro")
    wb.Close(SaveChanges=1)
    xl.Quit()
    print("Macro ran successfully")
except:
    print("Error found while running the excel macro")
    xl.Quit()

I looked online to get the path to XLSTART. Some people had it under local, mine is under Roaming.

In excel you can run this macro from PERSONAL.XLSB to get the filepath to yours

Sub Find_Personal_Macro_Workbook()

    Dim path As String

    path = Application.StartupPath

    MsgBox path

End Sub

Collectives™ on Stack Overflow

Running macro in PERSONAL.XLSB using python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related