제
https://pbpython.com/advanced-excel-workbooks.html?source=post_page---------------------------
Creating Advanced Excel Workbooks with Python - Practical Business Python
Mon 07 December 2015 Posted by Chris Moffitt in articles Introduction I have written several articles about using python and pandas to manipulate data and create useful Excel output. In my experience, no matter how strong the python tools are, there a
pbpython.com
https://xlsxwriter.readthedocs.io/working_with_macros.html
Working with VBA Macros — XlsxWriter Documentation
This section explains how to add a VBA file containing functions or macros to an XlsxWriter file. The Excel XLSM file format An Excel xlsm file is exactly the same as an xlsx file except that is contains an additional vbaProject.bin file which contains fun
xlsxwriter.readthedocs.io
제
https://stackoverflow.com/questions/17197259/use-python-to-inject-macros-into-spreadsheets
Use Python to Inject Macros into Spreadsheets
I've got a macro that I'd like a bunch of existing spreadsheets to use. The only problem is that there are so many spreadsheets that it would be too time consuming to do it by hand! I've written a
stackoverflow.com
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()
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
Appliying a VBA macro to an Excel file using win32com python library
Im triying to apply a VBA macro using python and the win32com library, the idea is creating a fixed excel file, storing a macro and then running it. I took the idea from here: https://redoakstrateg...
stackoverflow.com
# creating the dataframe
path = "C:\Users\John\Desktop\Python_scripts\Running VBA Macro"
filename = "normal_excel_file.xlsx"
filename_macro = "macros_excel_file.xlsm"
df = pd.read_excel(filename, index_col=0)
with pd.ExcelWriter(filename, engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='data', index=False)
workbook = writer.book
workbook.filename = filename_macro
workbook.add_vba_project('vbaProject.bin')
writer.save()
# RUN MACRO
if os.path.exists(os.path.join(path, filename_macro)):
wb = xl.Workbooks.Open(Filename = filename_macro, ReadOnly=1)
xl.Application.Run("ThisWorkbook.Macro1")
xl.Application.Quit()
_