CODEWITHSUNDEEP
pythonprogress-bar
Ooo
Ooo

Reputation: 39

Running vba macro via python but can you add progress bar in python to measure vba procress?

I am currently running a VBA Macro using python so I can automate some of the processes, I also currently use tqdm to add a processbar to my other python code which is extremely useful.

Does anyone know whether it is possible to run macros and measure how long it will take and use the process bar to see what stage I am at as I have set it to run in the background? The code take between 5 - 6 hours to run so would be good to know how long I have left.

import xlwings as xw

def run_mac(file_path):
    
    try:
        xl = xw.App(visible=True, add_book=False)
        wb = xl_app.books.open(file_path)

        run_macro = wb.app.macro('lookupLoop.Open_Report')
        run_macro()

        wb.save()
        wb.close()

        xl.quit()

    except Exception as ex:
        template = "An exception of type {0} occurred. Arguments:\n{1!r}"
        error = template.format(type(ex).__name__, ex.args)
        print(error)


run_mac(input('Excel path: '))

This is what I have for the file transfer:

enter image description here

Upvotes: 0

Views: 614

Answers (1)

David sherriff
David sherriff

Reputation: 476

I dont know much about macros, but based on a previous question of yours I have come implemented a progress bar to provide information on what file has been transferred.

import os
import shutil
import time
import datetime
import tqdm

src = "C:/Users/eldri/OneDrive/Desktop/"
dst = "C:/Users/eldri/OneDrive/Desktop/output"
ext = input("[+] File format: ")  # "txt"
start = input("[+] Date start: ")  # "01/07/2020"
end = input("[+] Date end: ")  # "30/07/2020"


def dateRange(createdDate, startDate, endDate):
    """determines if date is in range"""
    createdDate = datetime.datetime.strptime(createdDate, '%a %b %d %H:%M:%S %Y')
    startDate = datetime.datetime.strptime(startDate, '%d/%m/%Y')
    endDate = datetime. datetime.strptime(endDate, '%d/%m/%Y')
    return startDate < createdDate < endDate


files = os.listdir(src)
numFiles = len(files)
pbar = tqdm.tqdm()
for filename, i in zip(files, tqdm.trange(numFiles-1)):
    print(filename)
    created = time.ctime(os.path.getmtime(src + filename))
    if filename.endswith('.' + ext) and dateRange(created, start, end):
        shutil.copy(src + filename, dst)
        msg = "[+] File transferred " + filename + created
    else:
        msg = "[+] File not transferred " + filename + created
    pbar.set_postfix_str(msg)

print("[+] Transfer complete")

the progress bar shows 100%|██████████| 10/10 [00:50<00:00, 5.00s/it] 10/10 is the number of files transferred, [00:50<00:00, has the format [time taken<time remaining, and 5.00s/it] indicates how long it takes to copy one file. All times are estimates based on the average time to copy a file.

Upvotes: 1

Related Questions