Simple app demonstrating VBA, SQL like Querying and Python Package XLWings in MS Excel

1. Enable “Developer” tab in Excel.

Go to “FILE”

Go to “Options”

Go to “Customize Ribbon”

Then, check “Developer” in the column on the right labeled “Main Tabs”

<Fig Enabling Developer Tab>

2. If you have Anaconda distribution of Python installed, then XLWings comes pre-packaged in Anaconda. We will install XLWings add-on for MS Excel.

Ref: https://docs.xlwings.org/en/stable/addin.html#installation

Fire this command from CMD: xlwings addin install

<Fig Installing XLWings Addon>

3. Adding “xlwings” in “References” in VBA Developer.

<Fig Adding XLWings in References>

4. Write “Hello World” sub-routine in VBA

<Fig Hello World Sub-routine>

Sub GoToPythonProgram()     MsgBox ("Hello") End Sub

And, save the file with the extension “Excel Macro-Enabled Workbook” (.xlsm)

5. In the Excel sheet, insert a shape “Rectangle”, add text “CALL MY SUBROUTINE!”, and open the “Context Menu” on it to click “Assign Macro” option.

<Fig Assign Macro>

<Fig Select Macro>

6. Now, when you will click on the textbox “CALL MY SUBROUTINE”, it will show an alert.

<Fig Hello>

7. In the same directory as the XLSM file, create a file “hello.py”.

Insert following code in it:

# hello.py import numpy as np import xlwings as xw def world():     wb = xw.Book.caller()     wb.sheets[0].range('A1').value = 'Hello World!'

Change the VBA Code as follows:

Sub GoToPythonProgram()     MsgBox ("Hello")     RunPython ("import hello; hello.world()") End Sub

8. Now, click on the textbox again.

It will generate the same pop-up saying “Hello” and will also fill the cell A1 with text “Hello World!”

9. To support SQL queries in MS Excel, add the following references:

10. Then, we setup a sheet with the following data and a button:

11. The button is assigned a Macro "mySubRoutine()":

Sub mySubRoutine()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
   
    strFile = ThisWorkbook.FullName
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
   
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
   
    cn.Open strCon
   
    strSQL = "SELECT [Country], SUM([Points]) FROM [Sheet1$A1:B8] GROUP BY [Country]"   
    rs.Open strSQL, cn
   
    MsgBox (rs.GetString)
End Sub

12. On the click of the button the sub-routine is called and following message box appears: