Is there a way to run a piece of VBA code or even launch the Solver function in Excel that exists on the connected Excel sheet through Anvil?

Anvil Q&A
1

I have an excel sheet that’s connected to my Anvil form that contains a VBA code (essentially it launches the ‘Solver’ function in Excel). I am wondering if there is a simple function to call from my anvil form that can either run the VBA or even simply launch the Solver function after which I can simply extract the result back to my Anvil form.

Any and all help much appreciated.

2

Hallo @kmichael, you might want to look into PyXLL, a library designed for working with Excel and VBA from Python. Take a look at their docs to learn more about how to use it for what you’re after. (Please note that this is a paid library, although they do offer a free trial and educational discounts).

Hope this helps!

3

I don’t think there is any “simple function to call from my anvil form that can either run the VBA or even simply launch the Solver function”.

The form runs inside the browser using JavaScript. If you are lucky you can import PyXLL, assuming it is available in Skulpt, but I doubt it.

So, you will need to call a server function and see if PyXLL can do the job for you. Again, I doubt it. I don’t know PyXLL, but very likely is a library that deals with the data stored in the file and can’t evaluate any functions.

So, you will need to call an uplink function that runs on a computer with Excel installed and uses something like this to start an instance of Excel and run a VBA macro:

excel_app = win32com.client.GetObject(Class="Excel.Application")
excel_app.Application.Run(macro_name, args)

The excel_app object is the usual COM object and can be used with the usual Workbooks.Open etc. methods. You can keep the excel_app invisible for a quicker launch and get it to work in background, or make it visible with excel_app.Visible = True to see what’s going on.

My uplink scripts keep checking on Excel and kills it if it takes too long to do its job (this is the most difficult part of the script: checking if something went wrong). I sometimes click on the script window and select some text, so the script stops running (it’s a nice side effect of a horrible behavior of the Windows command prompt). While the text is selected, if the Excel instance is visible, I can press Alt+F11 and debug the VBA code. When I’m done I will press enter on the terminal window and the script keeps running.

1 Like