CODEWITHSUNDEEP
pythonexcelvba
Colm McSweeney
Colm McSweeney

Reputation: 11

Running macro in PERSONAL.XLSB using python

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()

Upvotes: 1

Views: 1794

Answers (1)

Mofongo
Mofongo

Reputation: 131

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

Upvotes: 2

Related Questions