Untitled

!pip install xlwings

import xlwings as xw

# Path to your Excel workbook in Colab
file_path = '/content/workbook.xlsm'  # Update this path as necessary

# VBA macro code to be added
vba_code = """
Sub ExtendFormulasToRowN(targetRow As Long)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim lastCol As Long
    lastCol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column

    Dim col As Long
    For col = 1 To lastCol
        Dim formula As String
        formula = ws.Cells(2, col).Formula
        
        If formula <> "" Then
            ws.Cells(targetRow, col).Formula = formula
        End If
    Next col
End Sub
"""

# Function to run VBA macro
def run_vba_macro(file_path, vba_code, macro_name, *args):
    # Open the workbook
    wb = xw.Book(file_path)

    # Add VBA code to the workbook
    vb_module = wb.api.VBProject.VBComponents.Add(1)  # 1 = Standard Module
    vb_module.CodeModule.AddFromString(vba_code)

    # Run the VBA macro
    wb.macro(macro_name)(*args)

    # Save the workbook
    wb.save()

    # Close the workbook
    wb.close()

# Set up the xlwings connection (connect to local server)
xw.Book.caller()
run_vba_macro(file_path, vba_code, 'ExtendFormulasToRowN', 10)

# Download the modified workbook
from google.colab import files
files.download(file_path)