title: How to add "Microsoft Excel Object" VBA code to Excel file using Python
body: <p>I have a long script written in VBA, in Excel. I'm using Python to create another Excel file, and I want to embed the script in that file. I have been able to add a macro as "Module1" in Excel, but not as a "Microsoft Excel Object" associated with a specific worksheet. It has to be associated with one named worksheet, because it highlights particular cells when you click on input cells. </p>
  
  <p>I've extracted the vbaProject.bin into the same folder as the Excel file I want to add it to. I have also remembered to set the correct permissions in Excel (File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> Trust Access to the VBA Project Object Model). </p>
  
  <p>I've just generated a file called output.xlsm that opens without issue, contains no VBA, and has multiple worksheets, before starting this code. </p>
  
  <p>I hope this is clear - I am a newbie and this is my first question. </p>
  
  <pre class="lang-py prettyprint-override"><code>
  import os
  import win32com.client
  
  mydir = os.getcwd()
  
  with open ('vbaProject.bin', 'rb') as myfile:
      print('Reading macro into string from vbaProject.bin')
      macro = myfile.read()
  
  excel = win32com.client.Dispatch('Excel.Application')
  excel.Visible = False
  workbook = excel.Workbooks.Open(Filename = mydir + '/output.xlsm')
  excelModule = workbook.VBProject.VBComponents.Add(1)
  excelModule.CodeModule.AddFromString(macro)
  excel.Workbooks(1).Close(SaveChanges = 1)
  excel.Application.Quit()
  del excel
  
  </code></pre>
  
  <p>This code adds "Module1" rather than a Microsoft Excel Object. </p>
  
  <p>Also, when I try to close the new Excel file, the macro that I've added as "Module1" has not formed properly. It's a few gobbledegook characters, instead of a long script. </p>
  
  <p>Thanks for any help you can offer.</p>