Manage VBA codes of Excel Macro-Enabled workbook.
Add a VBA Module in Python
The following sample code creates a new workbook and adds a new VBA Module and Macro Code and saves the output in the XLSM format. Once, you will open the output XLSM file in Microsoft Excel and click the Developer > Visual Basic menu commands, you will see a module named “TestModule” and inside it, you will see the following macro code.
Sub ShowMessage()
MsgBox "Welcome to Aspose!"
End Sub
Here is the sample code to generate the output XLSM file with VBA Module and Macro Code.
from aspose.cells import SaveFormat, Workbook | |
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Create new workbook | |
workbook = Workbook() | |
# Access first worksheet | |
worksheet = workbook.worksheets[0] | |
# Add VBA Module | |
idx = workbook.vba_project.modules.add(worksheet) | |
# Access the VBA Module, set its name and codes | |
module = workbook.vba_project.modules[idx] | |
module.name = "TestModule" | |
module.codes = "Sub ShowMessage()" + "\r\n" + " MsgBox \"Welcome to Aspose!\"" + "\r\n" + "End Sub" | |
# Save the workbook | |
workbook.save(dataDir + "output_out.xlsm", SaveFormat.XLSM) |
Modify VBA or Macro in Python
You can modify VBA or Macro Code using Aspose.Cells for Python via .NET. Aspose.Cells for Python via .NET has added the following namespace and classes to read and modify the VBA project in the Excel file.
- Aspose.Cells.Vba
- VbaProject
- VbaModuleCollection
- VbaModule
This article will show you how to change the VBA or Macro Code inside the source Excel file using Aspose.Cells for Python via .NET.
The following sample code loads the source Excel file which has a following VBA or Macro code inside it
Sub Button1_Click()
MsgBox "This is test message."
End Sub
After the execution of Aspose.Cells for Python via .NET sample code, the VBA or Macro code will be modified like this
Sub Button1_Click()
MsgBox "This is Aspose.Cells message."
End Sub
You can download the source Excel file and the output Excel file from the given links.
from aspose.cells import Workbook | |
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Create workbook object from source Excel file | |
workbook = Workbook(dataDir + "sample.xlsm") | |
# Change the VBA Module Code | |
for module in workbook.vba_project.modules: | |
code = module.codes | |
# Replace the original message with the modified message | |
code = code.replace("This is test message.", "This is Aspose.Cells message.") | |
module.codes = code | |
# Save the output Excel file | |
workbook.save(dataDir + "output_out.xlsm") |
Advance topics
- Add a library reference to VBA project in workbook
- Check if Digital Signature of VBA Code is Valid
- Check if VBA Code is Signed
- Check if VBA project in a Workbook is Signed
- Check if VBA Project is Protected and Locked for Viewing
- Digitally Sign a VBA Code Project with Certificate
- Export VBA Certificate to File or Stream
- Filter VBA Project while loading a workbook
- Find out if VBA Project is Protected
- Password Protect the VBA Project of Excel Workbook