You can create Excel macros in Python to replace tasks typically done with VBA. Macros function similarly to worksheet functions and can be registered with the xl_macro decorator.

These macros are beneficial because they can be triggered by GUI elements like buttons or checkboxes. They can also be invoked from VBA. Using the Excel COM API, accessible through win32com or comtypes, you can interact with Excel just like with the VBA Excel object model, obtaining the Excel. Application COM object with xl_app.

Let us Automate Excel Macros without using VBA (Visual Basic for Application)

Step 1:

Open Excel: Launch Microsoft Excel on your computer.

Note:

How to Enable Macros in Excel:

• Open your Excel file.
• Go to “File” > “Options” > “Trust Center” > “Trust Center Settings” > “Macro Settings”.
• Select “Enable all macros” or “Enable all macros (not recommended; potentially dangerous code can run)”.
• Click “OK” to save the settings.

Now you dont need the above as you can automate it from python directly.

Step 2:

Open the workbook: Open the Excel workbook where you want to add the macro. If you don’t have a workbook yet, create a new one.

Step 3:

after adding some data save the workbook as a macro workbook (you can do it by saving it with .xlsm extension) like i have done above

Now we dont have to open VBA and add code there to automate macros we will simply use Python Library which in win32com which will automate macros

Step 4:

Write code in python – let say in the existing workbook we want to add new data in the first coloumn giving codition like (when a new row is found in coloumn insert the new data

Code:

import win32com.client #pip install win32com

# Path to your Excel file
excel_file_path = 'C:/Users/Swarna Khushbu/Desktop/book2.xlsm' # replace this with your own path

# Create an instance of the Excel application
excel = win32com.client.Dispatch('Excel.Application')

# Make Excel visible
excel.Visible = True

# Open the Excel workbook
workbook = excel.Workbooks.Open(excel_file_path)

# Get the first sheet in the workbook
sheet = workbook.Sheets(1)

# Find the last used row in the first column
last_row = sheet.Cells(sheet.Rows.Count, 1).End(-4162).Row

# Find the first empty row in the first column
empty_row = last_row + 1

# Add data to the first column in the first empty row
sheet.Cells(empty_row, 1).Value = "New Data" #replace new data with data name

# Save and close the workbook
workbook.Save()
workbook.Close()

# Quit Excel
excel.Quit()

Explanation of the above code:

1. import win32com.client: Imports the win32com.client module, which allows Python to interact with COM (Component Object Model) objects, such as those in Microsoft Excel.
2. excel_file_path = 'C:/Users/Swarna Khushbu/Desktop/book2.xlsm': Defines the path to the Excel file you want to work with.
3. excel = win32com.client.Dispatch('Excel.Application'): Creates an instance of the Excel application, which allows Python to control Excel.
4. excel.Visible = True: Makes the Excel application visible. If set to False, Excel runs in the background without showing the user interface.
5. workbook = excel.Workbooks.Open(excel_file_path): Opens the Excel workbook specified by excel_file_path.
6. sheet = workbook.Sheets(1): Gets the first sheet in the workbook. Sheets are indexed starting from 1.
7. last_row = sheet.Cells(sheet.Rows.Count, 1).End(-4162).Row: Finds the last used row in the first column (1) of the sheet. The value -4162 corresponds to the Excel constant xlUp, which means the method End(-4162) searches from the bottom of the column to the top to find the last used cell.
8. empty_row = last_row + 1: Calculates the row number of the first empty row in the first column by adding 1 to the last_row value.
9. sheet.Cells(empty_row, 1).Value = "New Data": Adds the value "NEC" to the first column of the first empty row.
10. workbook.Save(): Saves the changes made to the workbook.
11. workbook.Close(): Closes the workbook.
12. excel.Quit(): Quits the Excel application.

Output:

More Examples:

You can also create a macros to add new coloumn in you excisting excel

import win32com.client

# Path to your Excel file
excel_file_path = 'path_to_your_excel_file.xlsm'

# Create an instance of the Excel application
excel = win32com.client.Dispatch('Excel.Application')

# Open the Excel workbook
workbook = excel.Workbooks.Open(excel_file_path)

# Get the first sheet in the workbook
sheet = workbook.Sheets(1)

# Find the last used column in the sheet
last_column = sheet.Cells(1, sheet.Columns.Count).End(-4161).Column

# Add a new column after the last used column
new_column = last_column + 1
sheet.Cells(1, new_column).Value = "New Column Header" # add column header

# Fill the new column with data (e.g., "New Data" in each row)
for i in range(2, sheet.Cells(sheet.Rows.Count, 1).End(-4162).Row + 1):
    sheet.Cells(i, new_column).Value = "New Data" #add your data under it

# Save and close the workbook
workbook.Save()
workbook.Close()

# Quit Excel
excel.Quit()

How Python Can and has replaced VBA for macros:

Python has become a powerful tool for automating tasks, including those traditionally done with VBA (Visual Basic for Applications) in Excel. While VBA has been the go-to language for Excel automation, Python offers several advantages that make it a compelling alternative.

Firstly, Python is known for its simplicity and readability, making it easier for users to write and understand code. This is especially beneficial for those who are not professional programmers but need to automate tasks in Excel.

Secondly, Python has a vast ecosystem of libraries and tools that extend its capabilities. For Excel automation, the xlwings library is particularly useful, as it allows Python to interact with Excel workbooks, including running macros.

Additionally, Python’s versatility allows it to be used for a wide range of tasks beyond Excel automation. This means that users can leverage their Python skills for other projects, making it a valuable investment in the long run.

In conclusion, Python’s ease of use, extensive libraries, and versatility make it a strong contender for replacing VBA in Excel macros. By learning Python, users can not only automate tasks in Excel more efficiently but also open up opportunities for other projects requiring automation.