Backend - Queue for Excel Macro

I have a really big problem about finding the right architecture and for the following;

1. I have a VBA Macro which retrieves the parameters from one sheet, calculates an algorithm (it takes time) and outputs the result in the another sheet.
2. I need to create a backend (could be Flask or Nodejs) which is connected a kind of queue (Redis or RabbitMQ) which gets the parameters from Frontend (or HTTP Client) as POST request, then saves this parameters into the queue. After that a worker function should get them asynchronously run the excel macro and sends result back.
3. Its not obligatory but I want to use Docker ( I am open any other suggestions).

So I have tried this solution with xlwings, redis and flask with docker but it did not work.

• If I use docker containers for Windows, I can not use Redis.
• If I use docker containers for Linux, I can not use pywin32, I can only use xlwings library but it is not supported to save some parameters. Limitation of Xlwings
• I can add the another macro to excel to make http request to rabbitmq As Shown here but I dont know how to keep this macro running in Docker container

Can you give me a better architecture or solution?

Thank you.

    # ----------------- Flask (main.py) -------------
import os
from flask import Flask, make_response, request, jsonify
from redis import Redis
from rq import Queue
from server.excel_worker import run_excel_macro
app = Flask(__name__)
redis = Redis('redis', 6379)
q = Queue('excel_tasks', connection=redis)
@app.route('/add-task', methods=['POST'])
def create_task():
json = request.json
absolute_path = os.path.dirname(__file__)
filename = os.path.join('excel-file', 'excel-file.xlsm')
full_path = os.path.join(absolute_path, filename)
task = q.enqueue(run_excel_macro, full_path, 'Macro1', json)
data = {'message': 'Task Created', 'task_id': task.get_id()}
return make_response(jsonify(data), 201)
@app.route("/tasks/<task_id>", methods=["GET"])
def get_status(task_id):
task = q.fetch_job(task_id)
if task.is_queued:
response_object = {
"data": {
"task_id": task.get_id(),
"task_status": task.get_status(),
"task_result": task.return_value(),
},
}
return jsonify(response_object)
if task.is_started:
response_object = {
"data": {
"task_id": task.get_id(),
"task_status": task.get_status(),
"task_result": task.return_value(),
},
}
return jsonify(response_object)
elif task.is_finished:
return jsonify({'result': task.return_value()})
else:
return jsonify({"status": "error"})
if __name__ == '__main__':
app.run(host='0.0.0.0')
# ----------------- Excel Worker-------------
def run_excel_macro(filename, macro_name, variable_dict):
# Setting some parameters from variables
name = variable_dict['name']
surname = variable_dict['surname']
# Set Some more
# Run Excel macro using xlwings
xw.App().visible = False
wb = xw.Book(filename)
sheet1 = wb.sheets[0]
# Write some values in Excel Sheet
sheet1.range('A1').value = name
sheet1.range('A2').value = surname
macro = wb.macro(macro_name)
# Run Macro 
macro()
# Get Calculation of Macro and send result
result = {
... calculation result
}
wb.save()
wb.close()
return result
# -----------------Dockerfile-------------
FROM python:3.9
COPY . /app
WORKDIR /app
RUN pip install -r requirements.txt
EXPOSE 5000
CMD ["python", "main.py"]
# -----------------Docker-Compose-------------
version: '3.8'
services:
redis:
image: redis:latest
ports:
- '6379:6379'
volumes:
- ./redis:/redis
flask-app:
image: app-image
container_name: flask-app
restart: always
build: app
ports:
- '5000:5000'
depends_on:
- redis
worker:
image: app-image:latest
depends_on:
- redis
command: rq worker excel_tasks --url redis://redis:6379
links:
- redis

The trouble of running Excel inside a VM or a Container might not be worth it. If your code works well on your machine and it doesn't need to be on a server, you can probably stop here.

Also, since this is a question about architecture, you don't need to use a mixed architecture to make POST requests, VBA has that capability already. For instance, you could use the WinHttp.WinHttpRequest.5.1 object from the Microsoft WinHTTP Services, version 5.1 Library.

For Example:

    Dim TCRequestItem As Object
Set TCRequestItem = CreateObject("WinHttp.WinHttpRequest.5.1")
Dim Endpoint As String
Endpoint = "https://jsonplaceholder.typicode.com/posts"
Dim PayLoad As String
PayLoad = "{ ""context"" : { ""priority"" : ""REALTIME"", ""properties"" : [ ] }, ""inputPerson"" : { ""type"" : ""NaturalInputPerson"", ""personName"" : { ""nameFields"" : [ { ""string"" : ""John"", ""fieldType"" : ""GIVENNAME""}, {""string"" : ""Doe"", ""fieldType"" : ""SURNAME""} ]}, ""gender"" : ""UNKNOWN"" }}"
Dim Token As String
Token = "FakeToken"
With TCRequestItem
.Open "POST", Endpoint, False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.111 Safari/537.36"
.setRequestHeader "Authorization", "Bearer " & Token
.Send PayLoad
End With
Debug.Print TCRequestItem.responseText

You can also have a main workbook (parent) that controls the queue and make those POST requests while the other workbook (child) is executing the algorithm.

    Dim ExcelApp As Excel.Application
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Workbooks.Open "YourWorkBookLocation"
ExcelApp.Run "MacroName"
ExcelApp.Quit
Set ExcelApp = Nothing

The child could then write to a text file and the parent would be waiting for it in a loop (make sure to use Sleep for the Windows API to reduce CPU usage).

You could even consider using multi-threading if your algorithm supports that by creating multiple instances of Excel that run in parallel. (See also this Analyst's Cave article on the topic).

<hr>

However, if you really need to run this on a server you could consider paying for a web-hosting service that supports Windows. Otherwise, I would recommend porting the VBA code to a similar language that can more easily be run in a container like VBScript, VB6, TwinBASIC or even VB.NET.