Force python script not to wait for a vba macro to terminate

Question

I run a long excel VBA macro (about 10-15 minutes) from a python script. I would like to make a progressbar for the end users. In order to communicate from the macro to the python script, I tried to write in a txt file the % of progress from the VBA macro, and plug that number to a progressBar (done with Qt) in my .py The problem is: the py scripts waits for the macro to finsih to go read the .txt file value. I am looking for a way to force Python to go to the next command without waiting for the macro to finish. Here is the .py I have used unsuccesfully

from PyQt4 import QtGui, QtCore
import sys

import progress
import win32com.client

class Progress(QtGui.QDialog, progress.Ui_Progress):
    def __init__(self, parent=None):
        super(Progress, self).__init__(parent)
        self.setupUi(self)
        self.progressBar.setValue(0)

    def main(self):
        self.show()

    def evolution(self, a):
        self.progressBar.setValue(a)

if __name__=='__main__':
    app = QtGui.QApplication(sys.argv)
    progress = Progress()
    progress.main()
    xl=win32com.client.Dispatch("Excel.Application")
    xl.Workbooks.Open(Filename="filename",ReadOnly=1)
    xl.Application.ScreenUpdating = False
    xl.Application.Run("myscript")
    while a < 100
        file = open('newfile.txt', 'r')
        a = int(file.read())
        progress.evolution(a)
        file.close()
    app.exec_()


def main():
    pass

You're asking your python to process two sequential commands, concurrently, sounds like [threading](http://stackoverflow.com/questions/8241099/executing-tasks-in-parallel-in-python). I would probably just update teh `myScript` code for a statusbar or a user form to create a fancier progress bar, from the VBA side, though. — David Zemens, Jun 05 '14 at 15:03

score 2 · Answer 1 · answered Jun 05 '14 at 14:08

2

Why not create a user form as progress bar, that you call from the Excel VBA code directly?

http://www.ehow.com/how_7764247_create-progress-bar-vba.html

answered Jun 05 '14 at 14:08

score 1 · Answer 2 · answered Jun 05 '14 at 15:58

If you don't want to make a progress bar in the VB script, as suggested by others, you should use threading. I think changing the VBA is a better idea, but if you really are opposed for some reason, the following change should work:

import threading
import time
... # imports, progress class unchanged

# probably want to add arguments to make generic, but not required
def run_vba_script():
    xl=win32com.client.Dispatch("Excel.Application")
    xl.Workbooks.Open(Filename="filename",ReadOnly=1)
    xl.Application.ScreenUpdating = False
    xl.Application.Run("myscript")

if __name__=='__main__':
    app = QtGui.QApplication(sys.argv)
    progress = Progress()
    progress.main()

    vb_thread = threading.Thread(target=run_vba_script)
    vb_thread.start()

    while vb_thread.is_alive():
        file = open('newfile.txt', 'r')
        a = int(file.read())
        progress.evolution(a)
        file.close()
        # only check once a second to not eat your file system and processor alive
        time.sleep(1)
    app.exec_()

Force python script not to wait for a vba macro to terminate

2 Answers2