Python Programmatic access to VBA in Excel

Question

I'm trying to write a python script that will insert VBA code into workbooks that are output by a different program to ease the data analysis. Right now I have the user go into Excel, open the VBA editor and add a module from file then run the macro. I have the python script worked out to do what I want but I'm still troubled by the Excel trust setting. The idea is to take as much user "work" out of the process as possible, and according to this post: Programmatic Access To Visual Basic Project Is Not Trusted, my code should work to allow access but I'm still getting a com_error. Of course I can't put a comment on that question about my problem, so here I am writing a whole new question. My company takes cyber security very seriously so I've also considered that maybe this is just somehow not allowed, but I'm not sure if that's a valid possibility. Any help or advice is greatly appreciated.

Requisite parts of my code:

import win32com
import win32com.client
import pythoncom
import tkinter as tk
from tkinter import filedialog
import ctypes
import win32api
import win32con

excel = win32com.client.Dispatch("Excel.Application")
workbook = excel.Workbooks.Open(Filename=fs)

key = win32api.RegOpenKeyEx(win32con.HKEY_CURRENT_USER,
                                    "Software\\Microsoft\\Office\\16.0\\Excel"
                                    + "\\Security", 0, win32con.KEY_ALL_ACCESS)
win32api.RegSetValueEx(key, "AccessVBOM", 0, win32con.REG_DWORD, 1)

excelModule=workbook.VBProject.VBComponents.Add(1)
excelModule.CodeModule.AddFromString(macro)

I have also tried putting the key modification before opening the file and that didn't change anything.

score 0 · Accepted Answer · answered Jan 13 '20 at 17:54

Well, I kept playing around and figured it out myself. Turns out that the key modification needs to happen before you even open the Excel application. So like this:

import win32com
import win32com.client
import pythoncom
import tkinter as tk
from tkinter import filedialog
import ctypes
import win32api
import win32con

key = win32api.RegOpenKeyEx(win32con.HKEY_CURRENT_USER,
                            "Software\\Microsoft\\Office\\16.0\\Excel"
                            + "\\Security", 0, win32con.KEY_ALL_ACCESS)
win32api.RegSetValueEx(key, "AccessVBOM", 0, win32con.REG_DWORD, 1)

excel = win32com.client.Dispatch("Excel.Application")
workbook = excel.Workbooks.Open(Filename=fs)
excelModule=workbook.VBProject.VBComponents.Add(1)
excelModule.CodeModule.AddFromString(macro)

Python Programmatic access to VBA in Excel

1 Answers1