How to execute Python Script that runs a excel instance and triggers VBA on daily basis in Azure

Question

thanks for reading.

I need to run a Python Script in Azure on daily basis, but there are some challenges. The script uses following libraries and run the steps:

import os
import shutil
import win32com.client
from selenium import webdriver

1. Python script navigates some URL to download 8 .xls files.
2. Python script opens an Excel instance and triggers VBA code with win32com.
3. VBA script loops all downloaded files and copies data into tabular form.
4. VBA script saves CSV file of data.
5. VBA script uploads CSV file to Azure Blob Storage.
6. VBA script run SQL Server stored procedures and queries with uploaded CSV.

Process is fine and running smoothly. The challengue is to run it on daily basis without human interaction.

I read different approaches:

a) Use Windows VM in Azure and run with Windows Task Scheduler. I already did this and tomorrow morning will know if it works.

b) Use Docker and Azure services. Have not tried, but I've heard that it is more efficient.

I don't know which approach is more efficient in cost and runtime. I don't know if there is another solution.

The only thing I know, is that needs to have Excel installed to run VBA. Thankfull for any ideas.

Answer 1

You can use Containerized Azure Functions.

Docker container will give you the flexibility to install anything you want (like selenium, etc) and Azure Functions will enable you to have a time triggered execution (check this tutorial on official microsoft docs).