通过 python 运行 vba 宏，但你可以在 python 中添加进度条来测量 vba 进程吗?

问题描述

我目前正在使用 python 运行 VBA 宏，因此我可以自动执行一些流程，我目前还使用 tqdm 将进程栏添加到我的其他 python 代码中，这非常有用.

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.

有谁知道是否可以运行宏并测量需要多长时间并使用进程栏查看我处于哪个阶段，因为我已将其设置为在后台运行?代码需要 5 到 6 个小时才能运行，所以最好知道我还剩下多长时间.

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:

推荐答案

我不太了解宏，但根据您之前的一个问题，我实现了一个进度条，以提供有关已传输文件的信息.

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")

进度条显示 100%|██████████|10/10 [00:50 10/10 是传输的文件数，[00:50 的格式为 [time take<time剩余， 和 5.00s/it] 表示复制一个文件需要多长时间.所有时间都是根据复制文件的平均时间估算的.

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.

这篇关于通过 python 运行 vba 宏，但你可以在 python 中添加进度条来测量 vba 进程吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！