How to add a macro to an Excel document?-CodePudding

I recently began working with macros and VBA for work however my knowledge is currently very limited so please bear with me. I recently wrote a macro to apply a simple standardized format to all excel documents I send out to others. The macro has been working when I apply the macro from inside of excel, however I also have some automated reports I send out using python and I would like to use this macro on these reports as well to format them prior to sending.

My question is essentially, how do I add a macro to an existing excel document, how do I then execute that macro from python, and then save the resulting excel document?

I do not have much experience in using VBA code or interacting with this using python so I am not too sure where to start so any guidance would be much appreciated.

Below is the Macro I am looking to use

Sub Delphi_macro()

Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
ActiveSheet.ListObjects.Add(xlSrcRange, Range("a1").CurrentRegion, , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight9"
    ActiveWindow.DisplayGridlines = False
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    ActiveWindow.SmallScroll Down:=0
    Cells.Select
    Cells.EntireColumn.AutoFit
End Sub

CodePudding user response：

Add your Macro to a Module, and then Export the Module. After exporting the module you can use this python code to add it/import it and run it.

xl = win32com.client.Dispatch("Excel.Application")
xl.Workbooks.Add
wb = xl.ActiveWorkbook
wb.VBProject.VBComponents.Import "Full Path\VBA_Exported_Macro.bin"
xl.Application.Run("Module1.Delphi_macro")
xl.DisplayAlerts = False
wb.DoNotPromptForConvert = True
wb.CheckCompatibility = False
wb.SaveAs('final_outfile.xlsx', FileFormat=51, ConflictResolution=2) # Macro disapears here
xl.Application.Quit()
del xl
xl = None

In the code above this is creating a fresh Excel Workbook, adding the macro, running it and then Saves the file WITHOUT the macro. You can change that and instead open an existing workbook and save as Macro enabled excel to retain the macro.