How do I send VBA output to a Python script?

Question

Here is a code snippet:

' Send the query in the body to Jira, and get the response from Jira

strJsonResponse = Utilities.JiraRequest(strJQLQuery)

I'd like to put this json into a Python parser and then return the parsed json back into the spreadsheet. Is this possible? I've never worked with VBA before.

I have downloaded the JIRA API module and the openpyxl module.

If you want to [parse it with Python](https://jira.readthedocs.io/en/master/), why do you bother VBA? — Tehscript, May 26 '17 at 19:48
Please show how VBA is relevant here. Is *Jira* only available as VBA reference? — Parfait, May 27 '17 at 19:15

score 1 · Accepted Answer · edited May 26 '17 at 21:10

You might be able to create a new text file and output VBA to that

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim Fileout As Object
Set Fileout = fso.CreateTextFile("C:\your_path\vba.txt", True, True)
Fileout.Write strJsonResponse
Fileout.Close

VBA code found here

then have VBA start up your python script where you can have python parse your file. There is probably a better way to do this but I'm not sure what.

score 0 · Answer 2 · answered May 27 '17 at 18:19

I think that Tehscript raised a valid question. Are you certain that there's a need to use VBA code in addition to your Python code? By running Python code that makes use of the openpyxl module you can read out and modify basically everything that's in an Excel file, completely without the need to run an Excel application.

Sometimes there are valid reasons for coupling VBA and Python code. The most common one is when you want to use Excel as a familiar graphical user interface to direct data manipulation operations, but want Python to do the actual heavy-lifting. If you're looking to do this, then there's the xlwings module. Xlwings makes it easy to call Python code from inside VBA modules, and to exchange data values between VBA and Python.

How do I send VBA output to a Python script?

2 Answers2