python - Python Programmatic access to VBA in Excel
问题描述
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.
解决方案
好吧,我一直在玩,自己想通了。事实证明,密钥修改需要在您打开 Excel 应用程序之前进行。所以像这样:
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)
推荐阅读
- sql - 根据 SQL 中的日期从同一列的总和中减去
- c# - Azure 函数和 ASP.NET 成员资格
- shell - 删除包含存储在变量中的字符串的行在 sed 中不起作用
- vba - 用户表单在多个 Word 文档中填写书签?
- python - 无头 python 脚本需要 x-server 才能运行
- docker - Dockerize 非 Maven JEE 应用程序
- html - 从文件浏览器中选择多个文件时,HTML 多文件输入挂起
- asp.net-mvc - 如何在我的内容中添加新页面
- c - 自动化 C 程序的用户输入
- asp.net - 带有 OpenId Connect 和 Azure AD 的 ASPNET Core