Uncle SES modernizes VBA app with Python

Introduction

This is a story about a customer-resident SES uncle, who is often said to be similar to Wakusaku, who ported a seasoned ACCESS VBA application to Python.

Uncle SES specs

Financial field engineer. I've done everything from small EUC to backbone and packaging, but maybe I've been doing EUC for the longest time near the user. Age enough to worry about presbyopia.

How did you get started with Python?

There were two reasons why I started working on Python. First, the customer employee who was sitting next to the site was doing Python normally. This person seems to have majored in computer science at an overseas university, and I was looking at it from the side with a feeling of "amazing". In addition, other non-engineer employees are also studying Python as part of their training, and those people ask questions. So, of course, I couldn't answer. It's kind of like filling the moat around you, or you can feel the flow of the times. After that, go to PyCon and listen to stories such as Python usage example. did.

Then, I want to use it at work.

What i tried to do

The developer and administrator of a certain application at the customer's site will be changed jobs. So I wanted to rebuild this from scratch, so I asked if I could suggest it.

The application is an ACCESS / VBA application that outputs various reports (format is Excel / CSV) and sends them to various places by e-mail, and has been running for more than 10 years. A mechanism like a job scheduler is built in VBA, and it is automatically operated. By the way, I also helped with the operation of the application.

At first, I considered some platform-like alternatives to ACCESS, but none of them seemed to be heavy and long-fitting. As I mentioned earlier, the company I lived in had encouraged non-technical employees to use Python, so when I suggested that I use Python, I was easily accepted. The person who organizes the site pointed out the concern that ".NET is the main thing here, but I'm a little worried when other people maintain it", but "Rather, if Python can be used, the range of technology may be expanded. Isn't it? " There was also the aim of allowing non-technical people to perform maintenance in the future.

What happened to it

First of all, the people concerned gathered to take an inventory of the functions, and some of the functions were abolished or transferred. For other essential functions, we decided to modernize each element (Database / Forms / VBA) of the current platform ACCESS.

Instead of VBA

Python 3.7

The latest version at the start of development.

The main libraries are as follows. --OpenPyXl: Used for output processing of artifacts. --pandas: Used for aggregation processing. It may be more convenient than summarizing with SQL. -dataclasses: If you use DB! --mypy: code check

The editor is VS Code. I thought this was better than the original Visual Studio.

** A little trouble with handling Excel **

You cannot set a password when saving an Excel file from Python. Of course, Microsoft hasn't published the specifications for the security part, so you can't password-password Excel files from anything other than genuine applications. Since there is no help for this, I called an Excel instance from PowerShell and set a password on the target Excel file and saved it by overwriting. By the way, it is necessary to explicitly release each instance (Sheet, Workbook, Applicaton) when the Excel instance is terminated. Why?

Release instance after exiting Excel

$sheet, $wb, $excel | ForEach-Object {
    if ($_ -ne $null) {
        [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($_)
    }
}

Instead of Database (Jet DB)

SQLite3

In the field, Oracle was used as the core database, but the application (database) to be migrated is not referenced or updated by others, and there is no need to worry about the scale, so I chose this. The library is included in Python by default. Necessary and sufficient as an alternative to the ACCESS database (Jet DB).

Instead of MS Forms

DataTables

• This is a sample screen of the official website.

PyQt is heavy, so I chose the Web. So, after all, I thought that a spreadsheet-like UI would be good for data reference, so I decided to use a JavaScript library called DataTables. Much easier to use and view than the ACCESS table screen. There seems to be an update function, but I haven't used it this time.

However, I had a little trouble with JavaScript. I thought it would be difficult for the front-end engineer to struggle with JavaScript while designing.

** Web server **

Since there is no development server, the Web server borrows IIS that was in the department.

The bridge between SQLite and the Web (DataTables) is a REST API ~! I was enthusiastic, but apparently I couldn't use ASP in the first place. The data in SQLite is output to a JSON file at regular intervals and returned from IIS to the browser. It's a bit messy here, but there's no problem with usability. I want to improve here if I have time left (the one who never does it).

How was it

Python is fun.

You can simply write what you want to do. I almost never think it's a hassle. Most of the time I wish I had a library like this.

So productivity is good.

This time, at the beginning of development, I had almost grasped the business and functional specifications, and there was almost no design process, so I feel that way.

Therefore, the expression "doesn't bother" rather than "productivity is good" may be closer to the actual feeling.

I want to continue using Python if I have a chance!

Things to keep in mind

In this Requests-HTML blog post, "The library itself does not do much work, but concentrates on combining existing libraries." After reading a sentence, I tried to use the existing library as well as possible. The less you write, the shorter the test. This may not be limited to Python, though.

Compared to other languages, Python has too many libraries and it is difficult to grasp even the main ones. I think there are quite a few places where I haven't been able to make good use of the part such as "I wish I could use that library here" or the library itself. Especially around dataclasses, you may not be able to use it well.

trouble

After all, the type of variable cannot be explicitly declared. I used type annotations to check with mypy, but I still got type-related exceptions. I expect that the accuracy of mypy will improve further in the future. Until then, write unit tests. I hope someday there will be something like TypePython like TypeScript.

I also used SQLite to ROUND the numbers to get an error, but I tried to avoid numerical calculations and aggregations by using pandas as much as possible.

And, Why is Python so slow? I was a little worried about the performance, but it is rarely a problem in the EUC area. I think I'll do it, and in fact it didn't really matter much this time (rather, my writing style had a big impact on processing performance).

What happened

The implementation phase is over, and by this time it was supposed to be operating in parallel with the ACCESS version and entering the verification phase, but it is stuck due to this corona sickness ... (So I am writing this article)

What will happen from now on

It's often said that there are deadly routine tasks in the world that you want to automate, and EUC applications that have no one to maintain.

There are people and non-engineers who are interested in Python, and I hope that non-engineers will gradually increase their use of Python in the future.

By combining these, the number of small-scale jobs such as inventory of existing EUC, labor saving, and modernization will continue to increase, and there will be no business in which engineers support such jobs. I wonder if.