0

I have a python script that is taking several CSV files and merging them into one workbook (in different worksheets).

I want to apply on one of the sheets a VBA Macro (.bas code that I wrote, the code supposes to create a button on the work sheet and to execute so style formatting on the data that is on the work sheet.

This is the VBA code that I want to execute (for start as a test).

Attribute VB_Name = "Module1"
Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.Buttons.Add(572.25, 54.75, 43.5, 36).Select
    Selection.OnAction = "macro"
End Sub
Sub macro()
Attribute macro.VB_ProcData.VB_Invoke_Func = " \n14"
'
' macro Macro
'

    Range("E:E").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

In my python script, i'm using openpyxl to create the workbook

I am using python 3.6

Community
  • 1
  • 1
Tomer Smadja
  • 46
  • 7
  • Can you summarize your problem statement? 1) You have an independent .BAS module which needs to be imported in to the Workbook created at runtime and 2) You would also like to use your python script to *execute* one or more procedures from this module, also at runtime? – David Zemens Aug 29 '17 at 14:18
  • Use win32com or comtypes per this answer to inject the .BAS module: https://stackoverflow.com/questions/17197259/use-python-to-inject-macros-into-spreadsheets . Via win32com you *should* be able to then execute that `"macro"` procedure, but I don't recall the specific syntax to call the procedure. – David Zemens Aug 29 '17 at 14:20
  • i leave you my GitHub, i have something similar, https://github.com/metrafull2/SAP-Remisiones/blob/master/main.py is in the function def macro() – Mauricio Cortazar Sep 02 '17 at 00:01
  • @DavidZemens 1) Yes 2) I can have all the functions I need in one BAS module, I will have separated modules for each worksheet I tried to use win32com but I didn't manage to figure it out MauricioCortazar Thanks, I will take a look. – Tomer Smadja Sep 03 '17 at 15:01
  • Can you show us what you have tried with win32com package? It will be best if you can show it via a [mcve] (i.e., just attempting to import a single module, etc.) I have used python 2.7 and win32com to automate the production of PPAM files and importing modules that way, but I no longer have that code. If you can show me what you've tried, I'm sure I can probably help piece it together. – David Zemens Sep 03 '17 at 15:35

0 Answers0