Using API calls with VBA and Python to write a database is actually a pretty decent architecture. Here, I would like to say that “It takes the best of the both worlds”, but the truth is that it takes the best from Python and somehow through a lot of “magic” it makes the VBA code works as well.
This is how the architecture looks like:
So, we obviously have 3 parts of the architecture:
- Excel with VBA, where the user writes the data and makes GET and POST api calls;
- Python with Flask, responsible to run a server and handle these api calls in a way to write these to a DB;
- A DB, which is supposed to store these calls;
Excel with VBA
The first part of the architecture consists of worksheets and VBA code. The worksheets have the input in a table:
This table is uploaded to the database with a POST api call, through the function MainUpload:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
Sub MainUpload() Dim myLastRow As Long: myLastRow = lastRow(tblRanking.Name) Dim http As New MSXML2.XMLHTTP60 Dim i As Long For i = 2 To myLastRow Dim jsonPerLine As String: jsonPerLine = GenerateJson(i, tblRanking) http.Open "POST", "http://127.0.0.1:4000/football_ranking", False http.setRequestHeader "Content-Type", "application/json" http.send jsonPerLine If http.Status = 201 Then Application.StatusBar = "201 OK -> " & tblRanking.Cells(i, 2) Else Err.Raise 999, Description:="Line " & i & " Error!" End If Next i Application.StatusBar = "" End Sub Public Function GenerateJson(i As Long, tbl As Worksheet) As String Dim result As String: result = "{" Dim lastCol As Long: lastCol = LastColumn(tbl.Name) Dim myCell As Range For Each myCell In tbl.Range(tbl.Cells(i, 1), tbl.Cells(i, lastCol)) With WorksheetFunction Dim myKey As String: myKey = .Trim("""" & tbl.Cells(1, myCell.Column) & """") Dim myVal As String: myVal = .Trim("""" & myCell.Value & """") End With If myCell.Column = lastCol Then result = result & myKey & ":" & Trim(myVal) & "}" Else result = result & myKey & ":" & Trim(myVal) & "," End If Next GenerateJson = result End Function |
Well, obviously the function above hopes that there is a running server at http://127.0.0.1:4000 and calling a POST to it would do something. The function GenerateJson() makes JSON, based on the table column names and the i-th value.
Additionally, there is a function called MainDownload() , which looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Sub MainDownload() Dim http As New MSXML2.XMLHTTP60 http.Open "GET", "http://127.0.0.1:4000/football_ranking", False http.send Dim allTeams As Object Set allTeams = JsonConverter.ParseJson(http.responseText) tblDb.Cells.Clear Dim r As Long: r = 1 Dim c As Long: c = 1 Dim team As Object For Each team In allTeams Dim element As Variant For Each element In team If r = 1 Then tblDb.Cells(r, c) = element Else tblDb.Cells(r, c) = team(element) End If c = c + 1 Next r = r + 1 c = 1 Next End Sub |
It uses the JsonConvertor class from vba-tools, which actually works pretty decent (thumbs up for the documentation, Tim). What it does is to take the http.responseText and to write it to a worksheet, which has been cleared previously with tblDb.Cells.Clear. So far so good.
Python + Flask, running a server and serving the API
Here the story is a bit easier. Somehow, Python is used to run servers and make API calls. Thus, in general we are having plenty of libraries and two files – main.py and FootballRankingModel.py. I hope that it is easier to understand which one makes what as these two are really small. Anyway, run main.py to start the local server.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
from flask import Flask from flask_sqlalchemy import SQLAlchemy import json from settings import app db = SQLAlchemy(app) class FootballRanking(db.Model): __tablename__ = 'football_ranking' Id = db.Column(db.Integer, primary_key=True) Name = db.Column(db.String(100), nullable=False) Wins = db.Column(db.Integer, nullable=False) Coach = db.Column(db.String(100)) def json(self): return { 'Id': self.Id, 'Coach': self.Coach, 'Wins': self.Wins, 'Name': self.Name, } def __repr__(self): football_ranking_object = { 'Id': self.Id, 'Name': self.Name, 'Wins': self.Wins, 'Coach': self.Coach, } return json.dumps(football_ranking_object) def add_line(_name, _wins, _coach): new_team_line = FootballRanking( Name=_name, Wins=_wins, Coach=_coach ) db.session.add(new_team_line) db.session.commit() def get_all_lines(): return [FootballRanking.json(line) for line in FootballRanking.query.all()] |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
import json from flask import Flask, jsonify, request, Response from FootballRankingModel import * from settings import * def football_ranking_object_is_valid(football_ranking): print(football_ranking) if ("Wins" in football_ranking) and ("Coach" in football_ranking) and ("Name" in football_ranking): return True else: print("Error from main.football_ranking_object_is_valid") return False # GET /football_ranking @app.route('/football_ranking') def get_football_ranking(): return jsonify(FootballRanking.get_all_lines()) # POST /football_ranking @app.route('/football_ranking', methods=['POST']) def add_line(): request_data = request.get_json() if football_ranking_object_is_valid(request_data): print (request_data['Name']) FootballRanking.add_line( request_data['Name'], request_data['Wins'], request_data['Coach']) response = Response("OK", 201, mimetype='application/json') return response else: invalid_football_ranking_object = { "error": "Invalid ns_cos object passed in the request!", "help_string": "Hello dear friend! Data passed should be a bit similar to the expected JSON!" } response = Response(json.dumps(invalid_ns_cos), status=400, mimetype='application/json') return response app.run(port=4000, debug=True) |
Database
For the database part I have left 2 things – the SQL, used to create the table football_ranking and the settings.py, which shows the path to the Flask app and the DB.
|
1 2 3 4 5 |
from flask import Flask app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///football_db.db' app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True |
|
1 2 3 4 5 6 |
CREATE TABLE "football_ranking" ( "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "Name" TEXT NOT NULL, "Wins" INTEGER NOT NULL, "Coach" TEXT NOT NULL ) |
At the end conda env export > requirements_py37.yml would export all the libraries and packages, used for the project.
Enjoy everything in Git – https://github.com/Vitosh/Python_personal/tree/master/PythonProjects/VBA_API!