2

I am wondering to use xlsxwriter to control my excel sheet instead of using old fashioned way by using VBA. I need a button to trigger some actions. What I read from the official documentation is

workbook.add_vba_project('./vbaProject.bin')
worksheet.insert_button('B3', {'macro':   'say_hello',
                               'caption': 'Press Me'})

I don't know how to define my own function as a macro and I don't know how to generate "vbaProject.bin". Is there anyway to write a macro in format like python function and directly assign it to the button?

If I must include the macro in vbaProject.bin, how can I do that? Hope it is not something like vba.

Jieke Wei
  • 173
  • 2
  • 13

1 Answers1

1

So you have many ways to control and automate your files:

  1. You can use vba

  2. You can use xlsxwriter which uses python syntax

  3. You can use a library called PyXLL https://www.pyxll.com/docs/userguide/macros.html . It seems you are able to write a macro using python syntax but i haven't tried it, you could have a look as it might be what you are looking for.

About your question on how to inject a macro to your excel using xlsxwriter have a look at my answer here, i think it is quite detailed:

Add dataframe and button to same sheet with XlsxWriter

By the way vba is not hard to learn and there is a lot of information online. If you don;t want to write the code manually you can record the macro by pressing record, do your thing and stop the recording. Then you will have the code which you can inject it to your file (look at my tutorial above)

Dimitris Thomas
  • 1,363
  • 9
  • 14
  • I used VBA later indeed. just I don't like to use this ancient language. – Jieke Wei Apr 07 '21 at 14:13
  • You have a point. Personally i was using pandas for the data manipulation and xlsxwriter for the formatting, that was the best way i was aware of for 98% of the tasks i was assigned to automate. I would use vba only in the rare case that i wanted to implement native pivot tables and xlsxwriter wasn't supporting them back then. In any case the most overpowered combo for automating your files is pandas+xlsxwriter, none other library would come close to those 2. – Dimitris Thomas Apr 07 '21 at 14:47
  • I actually tried xlsxwritter before I asked the question and already generate entire excel file. The problem I got was I cannot create a button in this library, which is necessary in my case, and ask the button to trigger one action. Do you have any idea about that? – Jieke Wei Apr 08 '21 at 08:09
  • Afaik you can achieve this with VBA. Create firstly the code for your button in a xlsm file with vba, extract it and when you are creating the excel file with xlsxwriter inject it in the file. Basically you want to link the vba code with the button. Haven't my instructions helped you? – Dimitris Thomas Apr 08 '21 at 09:49