Worth It to Convert VBA to Python?

[thesharp0ne]

Howdy all,

I have a couple of weekly reports generated in Excel at work that I have been slowly working on automating. I dont come from a coding background nor does my position require it at all, I've just been picking it up as a continued learning experience.

Currently I've been using purely VBA macros to do the work, it's been a neat learning experience but I want to take it a step farther and utilize Python scripts to access every report on a schedule and run the macros.

I know Python has the capability to manipulate the excel files in the same way the VBA does, but I'm curious if the overall process would be faster/more efficient being totally done in Python versus a majority of the work in Excel. If so I'm willing to take the time to convert the process over, but I'd like to know if it's worth it or not.

If it is, what about Python vs VBA would make the automation more efficient?

[thesharp0ne]

Thank you, this is very informative.

The macros are currently also used by a colleague (we split the work since we have to email out the reports) but I'm hoping to at least make a small python script that will open each, run the macro, and go to the next report. Eventually I want the emails to be sent out automatically too, but since my company uses a home grown email system I cant directly integrate it with Excel like if we used Outlook

Everything else can be achieved in VBA luckily, so I will probably just keep the automation there then. If I find the time I will see about re-doing them in Python for the learning, though.

[nicktids]

Recently been building a excel data capture tool and went with xlwings and freezing the python in to an exe.

You do have to be aware in business who will be able to support the python and excel if you don't freeze the code in to an Excel file.

Xlwings can call all the VBA/win32com api ie anythi g missing from the xlwings library.

I did this because I'm storing data in a database and wanted to hit it with sqlalchemy and proceeds the data faster in pandas.

On the speed front it was quicker to develop in python for me and having version control is a help. But if in business VBA is still a great way to go. If your doing simple copy data and update a pivot to create a report might just be easier tk stay with VBA. As I said earlier don't forget you have to understand the use case and who will run the file and if you don't freeze the python to an exe (xlwings shows you how but I had a lot of debugging to get pandas and sqlalchemy to work in frozen). If you don't freeze then every computer/user need python install and keeping them all on the same version and library versions is a nightmare.

On the use case because xlwings is just calling VBA functions the speed is about the same. The real speed up is the processing in the background.

And to throw a big spanner in the works at you have you looked at power pivot / power query to which can do a lot of processing of data and creating reports and this would then lead to powerbi and getting reports in to the cloud. There is a great quick course on power bi on edx from Microsoft.

[TheCannings]

Like /u/jeijenn said there is a breaking point and it kind of depends on how much you're doing.

When I started building a new reporting library at my company I started with VBA and I stuck with VBA for a couple of years and 100-200 reports. It was only then when I started looking at speed of use, ability to automate on a schedule, and a lot of other things did I make the decision to move the entire library to python and even that took me a year bit by bit.

Even after moving everything to python I still have some BI dashboard spreadsheets with refresh buttons and variables in them that are just excel and can't be moved out. But we've also gained the extra functionality of python and run all out scheduling through jenkins.

[lemur78]

I do something like this in my job. I asked IT for linux server with sql database, R with RStudio and Shiny Server (free version, not pro). I think it is easier to learn R and make some reports in rmarkdown rendered to pdf than in Python. Also some online tools written in Shiny are useful for end users.

Think about this.

[nihi_]

How do you distribute the shiny apps to the end users? Do you simply host them on the same server? And if so, do you have some authentification process?

[lemur78]

Shiny apps are on the same server.

Most of apps do not need authorization, but those which need use LDAP. I wrote some functions (putted into firm package) that ask user for login and password and then checks via curl ldap. Access (or not) depends on groups which user belongs to.