Использовать Python для вставки макросов в таблицы

У меня есть макрос, который я бы хотел использовать в составе существующих электронных таблиц. Единственная проблема заключается в том, что существует так много таблиц, что было бы слишком много времени для этого вручную!

Я написал Python script для доступа к необходимым файлам с помощью pyWin32, но я не могу понять, как использовать его для добавления макроса.

Аналогичный вопрос здесь дал этот ответ (это не Python, но похоже, что он все еще использует COM), но мой COM-объект, похоже, не имеет члена, называемого VBProject:

Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True 
objExcel.DisplayAlerts = False 
Set  objWorkbook = objExcel.Workbooks.Open("C:\scripts\test.xls") 
   Set xlmodule = objworkbook.VBProject.VBComponents.Add(1)  
   strCode = _ 
   "sub test()" & vbCr & _ 
   "   msgbox ""Inside the macro"" " & vbCr & _ 
   "end sub" 
   xlmodule.CodeModule.AddFromString strCode 
objWorkbook.SaveAs "c:\scripts\test.xls" 
objExcel.Quit

EDIT: ссылка на аналогичный вопрос: Внедрить и выполнить код Excel VBA в электронную таблицу, полученную из внешнего источника

Я также забыл упомянуть, что, хотя это не Python, я надеялся, что подобные объекты будут доступны мне через COM-объекты.

Ответ 1

Это код, преобразованный. Вы можете использовать либо win32com, либо comtypes.

import os
import sys

# Import System libraries
import glob
import random
import re

sys.coinit_flags = 0 # comtypes.COINIT_MULTITHREADED

# USE COMTYPES OR WIN32COM
#import comtypes
#from comtypes.client import CreateObject

# USE COMTYPES OR WIN32COM
import win32com
from win32com.client import Dispatch

scripts_dir = "C:\\scripts"
conv_scripts_dir = "C:\\converted_scripts"
strcode = \
'''
sub test()
   msgbox "Inside the macro"
end sub
'''

#com_instance = CreateObject("Excel.Application", dynamic = True) # USING COMTYPES
com_instance = Dispatch("Excel.Application") # USING WIN32COM
com_instance.Visible = True 
com_instance.DisplayAlerts = False 

for script_file in glob.glob(os.path.join(scripts_dir, "*.xls")):
    print "Processing: %s" % script_file
    (file_path, file_name) = os.path.split(script_file)
    objworkbook = com_instance.Workbooks.Open(script_file)
    xlmodule = objworkbook.VBProject.VBComponents.Add(1)
    xlmodule.CodeModule.AddFromString(strcode.strip())
    objworkbook.SaveAs(os.path.join(conv_scripts_dir, file_name))

com_instance.Quit()

Ответ 2

Поскольку я также изо всех сил пытался понять это правильно, я xlsm еще один пример, который должен работать в формате Excel 2007/2010/2013 xlsm. В приведенном выше примере нет большой разницы, он немного проще, без циклического перебора разных файлов и с дополнительными комментариями. Кроме того, исходный код макроса загружается из текстового файла вместо жесткого его кодирования в скрипте Python.

Не забудьте адаптировать пути к файлам в верхней части скрипта под свои нужды.

Кроме того, помните, что Excel 2007/2010/2013 позволяет хранить xlsm книги только с макросами в формате xlsm, а не в xlsx. При вставке макроса в файл xlsx вам будет предложено сохранить его в другом формате, иначе макрос не будет включен в файл.

И наконец, что не менее важно, убедитесь, что опция Excel для выполнения кода VBA извне приложения активирована (которая отключена по умолчанию из соображений безопасности), в противном случае вы получите сообщение об ошибке. Для этого откройте Excel и перейдите к

Файл → Параметры → Центр управления безопасностью → Настройки центра управления безопасностью → Настройки макроса → Активировать галочку на Trust access to the VBA project object model.

# necessary imports
import os, sys
import win32com.client


# get directory where the script is located
_file = os.path.abspath(sys.argv[0])
path = os.path.dirname(_file)

# set file paths and macro name accordingly - here we assume that the files are located in the same folder as the Python script
pathToExcelFile = path + '/myExcelFile.xlsm'
pathToMacro = path + '/myMacro.txt'
myMacroName = 'UsefulMacro'

# read the textfile holding the excel macro into a string
with open (pathToMacro, "r") as myfile:
    print('reading macro into string from: ' + str(myfile))
    macro=myfile.read()

# open up an instance of Excel with the win32com driver
excel = win32com.client.Dispatch("Excel.Application")

# do the operation in background without actually opening Excel
excel.Visible = False

# open the excel workbook from the specified file
workbook = excel.Workbooks.Open(Filename=pathToExcelFile)

# insert the macro-string into the excel file
excelModule = workbook.VBProject.VBComponents.Add(1)
excelModule.CodeModule.AddFromString(macro)

# run the macro
excel.Application.Run(myMacroName)

# save the workbook and close
excel.Workbooks(1).Close(SaveChanges=1)
excel.Application.Quit()

# garbage collection
del excel

Ответ 3

То, что упоминали @Dirk и @dilbert, прекрасно работает. Вы также можете добавить этот фрагмент кода, который программно разрешит доступ к "объектному модулю VBA".

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)

Ответ 4

Если я правильно понимаю вашу проблему, есть простое решение без Python. Откройте файл Excel и сохраните его как "personal.xlsb". Затем сохраните макро для использования во всех файлах, и они должны работать со всеми файлами, если открыт файл personal.xlsb.

Ответ 5

Я хотел бы добавить к этому. У меня были проблемы с попыткой разобрать каждый файл .xlsm в моей папке. Я понял, как это сделать, используя glob и отформатировав оператор with внутри оператора for. Я новичок в программировании, поэтому, пожалуйста, извините, если я использую неправильную терминологию. Надеюсь, что это помогает другим. Благодаря.

import glob
import os, sys
import win32com.client


_file = os.path.abspath(sys.argv[0])
path = os.path.dirname(_file)
pathToMacro = path + r'\macro2.txt'
myMacroName = 'TestMacro'

for fname in glob.glob(path + "\*.xlsm"):
    with open (pathToMacro, "r") as myfile:
        print('reading macro into string from: ' + str(myfile))
        macro=myfile.read()
        excel = win32com.client.Dispatch("Excel.Application")
        excel.Visible = False
        workbook = excel.Workbooks.Open(Filename=fname)
        excelModule = workbook.VBProject.VBComponents.Add(1)
        excelModule.CodeModule.AddFromString(macro)
        excel.Application.Run('Module1.CleanDataPivot')
        excel.Workbooks(1).Close(SaveChanges=1)
        excel.Application.Quit()
        del excel

Теперь я могу запустить один макрос через каждый файл .xlsm в моей папке, не открывая Excel.