Embedding VB macro in python code

Question

I am new to use of win32com module . Following code i found on web for generating excel :

import win32com.client as win32
class generate_excel:
    def excel(self):
        excel = win32.gencache.EnsureDispatch('Excel.Application')
        excel.Visible = True
        wb = excel.Workbooks.Add()
        ws = wb.Worksheets('Sheet1')
        ws.Name = 'Share'
        ws.Range(ws.Cells(2,2),ws.Cells(2,3)).Value = [1,70]
        ws.Range(ws.Cells(3,2),ws.Cells(3,3)).Value = [2,90]
        ws.Range(ws.Cells(4,2),ws.Cells(4,3)).Value = [3,92]
        ws.Range(ws.Cells(5,2),ws.Cells(5,3)).Value = [4,95]
        ws.Range(ws.Cells(6,2),ws.Cells(6,3)).Value = [5,98]
        wb.SaveAs('hi.xlsx')
        excel.Application.Quit()            
d=generate_excel()
d.excel()

In this code i want to add a VB Script to draw a pie chart before the excel is closed . The VB Script is as follows :

Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlPie
    ActiveChart.SetSourceData Source:=Range("C2:C6")
End Sub

Please tell how to embed this in my Python Script .

Can you mark the answer if it solved your problem please?

ryansin
– ryansin

2016-11-21 17:12:56 +00:00
Commented Nov 21, 2016 at 17:12 — ryansin
– ryansin, Commented Nov 21, 2016 at 17:12

ryansin · Accepted Answer · 2016-09-09 10:09:00Z

2

Bit late and you've probably already found a solution but hopefully this helps someone else.

from win32com.client import DispatchEx

# Store vba code in string
vbaCode = """
Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlPie
    ActiveChart.SetSourceData Source:=Range("C2:C6")
End Sub
"""

# Init Excel and open workbook
xl = DispatchEx("Excel.Application")
wb = xl.Workbooks.Add(path_to_workbook)

# Create a new Module and insert the macro code
mod = wb.VBProject.VBComponents.Add(1)
mod.CodeModule.AddFromString(vbaCode)

# Run the new Macro
xl.Run("Macro2")

# Save the workbook and close Excel
wb.SaveAs(path_to_file, FileFormat=52)
xl.Quit()

edited Sep 9, 2016 at 10:09

answered Sep 9, 2016 at 9:07

ryansin

1,8573 gold badges28 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Embedding VB macro in python code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related