0

I've had some trouble finding something along these lines...

So I wrote a python program that queries some databases and does some dataframe manipulation using pandas. Then I used Xlwings to go the reading and writing to the .xlsm file that I want to update with this python program. Then I turned the python program into an executable using pyinstaller and stored that executable in a common folder so any user could access it. I then wrote this vba macro and attached it to a button in my spreadsheet:

Sub runPython()

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim RetVal
    RetVal = Shell("\\srv\manuf\Cell\Projects\pbu_improvements\partBreakdownUpdate_9_5.exe", 1)
End Sub

Now, I also want to run some vba macros after the executable executes. But it seems like vba just sprints through this line of code before the executable can finish (it takes almost 2 minutes to perform a full update) and doesn't wait for the executable to finish.

There's two ways I can think of that might solve this but I'm not sure how to do either, 1) write some kind of while loop in the vba that checks for the last row of one of the sheets that will be populated by the executable

Do 
    lastRow = Sheet4.Cells(Rows.Count, PartNoCol).End(xlUp).Row
Loop Until lastRow > 2

But this just times out the excel file so that

import xlwings as xw
def getActiveWorkbook():
    try:
        # logic from xlwings to grab the current excel file
        activeWb = xw.books.active.name
    except:
        # print error message if unable to get the current workbook
        print('Unable to grab the current Workbook')
        pause()
        exitProgram()
    else:
        return activeWb

just returns "Unable to grab the current workbook"

or 2) Run the VBA macros from the end of my program.

Has anyone done something like this before?

smichael_44
  • 167
  • 11
  • Assuming Windows, there's a system call to shell an executable and wait for it to finish. It looks as though that might be implemented in python from the results here https://duckduckgo.com/?q=python+execute+shell+command+and+wait&t=chromentp&ia=web I'm a vba guy, don't use python, so whether or not this will apply here is up to you. – Steve Rindsberg Aug 05 '22 at 17:51
  • https://stackoverflow.com/questions/8902022/wait-for-shell-to-finish-then-format-cells-synchronously-execute-a-command – Tim Williams Aug 05 '22 at 18:05

0 Answers0