vba 从python运行excel宏

Running excel macro from python

提问by rodrigocf

I'm using the following code to run an Excel macro from Python:

我正在使用以下代码从 Python 运行 Excel 宏：

import pymysql
import datetime
import csv
import math
import os
import glob
import sys
import win32com.client
import numpy
from tkinter import *
from tkinter import ttk
import tkinter.messagebox

def run_macro():
    print('macro')

    #this if is here because if an executable is created, __file__ doesn't work
    if getattr(sys, 'frozen', False):
        name = (os.path.dirname(sys.executable) + '\Forecast template.xlsm')

    else:
        name = str(os.path.dirname(os.path.realpath(__file__)) + '\Forecast template.xlsm')

    print(name)

    #this part runs the macro from excel
    if os.path.exists(name):
        xl=win32com.client.Dispatch("Excel.Application")
        xl.Workbooks.Open(Filename=name, ReadOnly=1)
        xl.Application.Run("ThisWorkbook.LoopFilesInFolder")
        xl.Application.Quit() # Comment this out if your excel script closes
        del xl

    print('File refreshed!')

I seem to be be having a certain issue with this, after running this, I go to open any excel file and I only get a grey window:

我似乎对此有一些问题，运行此程序后，我打开任何 excel 文件，但只看到一个灰色窗口：

Any idea of why this happens? Also how do I add to the code something to just open a file in Excel? (not to get the information, but to just open that file in Excel)

知道为什么会这样吗？另外我如何在代码中添加一些东西来在 Excel 中打开一个文件？（不是获取信息，而是在 Excel 中打开该文件）

Extra question: How do I get this not to close all open Excel files?

额外问题：我如何才能不关闭所有打开的 Excel 文件？

EDIT: I just checked the macro, and that works just fine, the problem seems to come just from when I run the code.

编辑：我刚刚检查了宏，效果很好，问题似乎出在我运行代码时。

NEW EDIT:

新编辑：

This is the code from the macro:

这是宏中的代码：

Sub LoopFilesInFolder()

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim path As String
    Dim file As String
    Dim extension As String
    Dim myFileName As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Set wb1 = ActiveWorkbook

    path = ActiveWorkbook.path & "\csvs\"

    extension = "*.csv"
    file = Dir(path & extension)

    Do While file <> ""

        Set wb2 = Workbooks.Open(Filename:=path & file)
        wb2.Activate

        'this section is for the avail heads file, basically it just opens it and copies the info to the template
        If wb2.Name = "avail_heads.csv" Then
            Range("A1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy

            wb1.Activate

            Worksheets("raw data").Range("B88").PasteSpecial xlPasteValues
        End If

        'this section is for the forecast file, basically it just opens it and copies the info to the template
        If wb2.Name = "forecast.csv" Then
            Range("A1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy

            wb1.Activate

            Worksheets("raw data").Range("B74").PasteSpecial xlPasteValues
        End If

        'this section is for the income file, basically it just opens it and copies the info to the template
        If wb2.Name = "income volume.csv" Then
            Range("A1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy

            wb1.Activate

            Worksheets("raw data").Range("B3").PasteSpecial xlPasteValues
        End If

        'this section is for the outgoing volume file, basically it just opens it and copies the info to the template
        If wb2.Name = "outgoing_volume.csv" Then
            Range("A1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy

            wb1.Activate

            Worksheets("raw data").Range("B36").PasteSpecial xlPasteValues
        End If

        'this section is for the required heads file, basically it just opens it and copies the info to the template
        If wb2.Name = "required_heads.csv" Then
            Range("A1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy

            wb1.Activate

            Worksheets("raw data").Range("B102").PasteSpecial xlPasteValues
        End If

        wb2.Close
        file = Dir

    Loop

    'myFileName = ActiveWorkbook.path & "\forecast_for_w" & Format(Now, "ww") + 1

    myFileName = ActiveWorkbook.path & "\yoda_forecast"

    ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal

    'MsgBox "Done!"

    Application.DisplayAlerts = True

End Sub

回答by rodrigocf

I have had this on bounty for almost a week and I really don know how many people have had this issue before. After tearing my hair out for over a week and considering multiple times to jump out the office window, I figured what the problem was.

我已经有将近一周的时间了，我真的不知道以前有多少人遇到过这个问题。在撕了一个多星期的头发并多次考虑跳出办公室窗户后，我才知道问题出在哪里。

Thee problem is not in python (sort of) but mostly on the VBA code, I just added

你的问题不在于 python（有点），而主要在于 VBA 代码，我刚刚添加

Application.ScreenUpdating = True

At the very end and the problem stopped. Not sure if an excel bug for not updating when the macro is done or a python bug for not allowing the screen to update once the macro has finished. However after doing that everything is fine now.

最后，问题停止了。不确定是否是在宏完成时不更新的 excel 错误，还是宏完成后不允许屏幕更新的 python 错误。但是，在这样做之后，现在一切都很好。

Thanks!!

谢谢！！

回答by Felix Zumstein

You need to set the Visibility to True after dispatching:

您需要在调度后将 Visibility 设置为 True：

xl = win32com.client.Dispatch("Excel.Application")
xl.Application.Visible = True

Also, calling the macro shouldn't need the ThisWorkbook:

此外，调用宏不应该需要ThisWorkbook：

xl.Application.Run("LoopFilesInFolder")

To be able to close a workbook again, you need to assign it to a variable:

为了能够再次关闭工作簿，您需要将其分配给一个变量：

wb = xl.Workbooks.Open(Filename=name, ReadOnly=1)
wb.Close(SaveChanges=False)

I wrote the package xlwingsto make things easier:

我编写了xlwings包来让事情变得更简单：

from xlwings import Workbook, Range
wb = Workbook(r'C:\path\to\workbook.xlsx')  # open a workbook if not open yet
Range('A1').value = 123  # Write a value to cell A1
wb.close()  # close the workbook again

Running a Macro hasn't been implemented yet, but there's an issueopen for that. In the meantime, you can workaround this by doing (following from above):

运行宏还没有实现，但有一个问题。同时，您可以通过执行以下操作来解决此问题（从上面开始）：

wb.xl_app.Run("macro_name")