从python运行excel宏 [英] Running 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导入*
从tkinter import ttk
import tkinter.messagebox
def run_macro（）：
print（'macro'）
＃如果这是因为如果可执行文件被创建，__file__不工作
如果getattr（sys，'freeze'，False）：
name =（os.path.dirname（sys.executable）+ '\\Forecast template.xlsm'）
else：
name = str（os.path.dirname（os.path.realpath（__ file__））+'\\预测template.xlsm'）
print（name）
#this部分运行宏从excel
如果os.path.exists（name）：
xl = win32com.client.Dispatch（Excel.Application）
xl.Workbooks.Open（Filename = name，ReadOnl y = 1）
xl.Application.Run（ThisWorkbook.LoopFilesInFolder）
xl.Application.Quit（）＃注释如果您的excel脚本关闭
del xl
print（'文件刷新！'）
我似乎有一定的问题这样，运行这个之后，我去打开任何excel文件，我只得到一个灰色的窗口：
任何想法为什么会发生这种情况？另外如何添加到代码的东西只是在Excel中打开一个文件？ （不是获取信息，而是在Excel中打开该文件）
额外的问题：如何获取这个不能关闭所有打开的Excel文件？
编辑：我刚刚检查了这个宏，而且工作得很好，问题似乎来自于我运行代码时。
新编辑
这是宏中的代码：
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
设置wb1 = ActiveWorkbook
path = ActiveWorkbook.path& \csvs\
extension =* .csv
file = Dir（路径和扩展名）
尽管文件<>
设置wb2 = Workbooks.Open（文件名：=路径和文件）
wb2.Activate
'这部分是为avail头文件，基本上它只是打开它，并将信息复制到模板
如果wb2.Name =avail_heads.csv然后
范围（A1）。选择
范围（选择，选择。 （xlDown））。选择
范围（Selection，Selection.End（xlToRight））。选择
Selection.Copy
wb1.Activate
工作表（原始数据）范围（B88）。PasteSpecial xlPasteValues
End If
'这部分是用于预测文件，基本上它只是打开它，并将信息复制到模板
如果wb2.Name =forecast.csv，然后
范围（A1）。选择
范围（Selection，Selection.End（xlDown））。选择
范围（Selection，Selection.End（xlToRight））。选择
Selection.Copy
wb1.Activate
工作表（原始数据）。范围（B74）。PasteSpecial xlPasteValues
结束如果
'此部分用于收入文件，基本上它只是打开它，并将信息复制到模板
如果wb2.Name =收入volume.csv然后
范围（A1）。选择
范围（选择，选择.End（xlDown））。选择
范围（Selection，Selection.End（xlToRight））。选择
Selection.Copy
wb1.Activate
工作表（原始数据）。范围（B3）。PasteSpecial xlPasteValues
End If
'此部分用于输出卷文件，基本上它只是打开它并复制信息到模板
如果wb2.Name =outgoing_volume.csv然后
范围（A1）。选择
范围（Selection，Selection.End（xlDown））。选择
范围（Selection，Selection.End（xlToRight））。选择
Selection.Copy
wb1.Activate
工作表（raw data）。Range（B36）。PasteSpecial xlPasteValues
End If
'这部分是所需的头文件，基本上它只是打开它并将信息复制到模板
如果wb2.Name =required_heads.csv然后
范围（A1）选择
范围（Selection，Selection.End（xlDown））。选择
范围（Selection，Selection.End（xlToRight））。选择
Selection.Copy
wb1.Activate
工作表（raw data）。Range（B102）。PasteSpecial xlPasteValues
End If
wb2.Close
file = Dir
循环
'myFileName = ActiveWorkbook.path& \forecast_for_w&格式（现在，ww）+ 1
myFileName = ActiveWorkbook.path& \yoda_forecast
ActiveWorkbook.SaveAs文件名：= myFileName，FileFormat：= xlWorkbookNormal
'MsgBoxDone！
Application.DisplayAlerts = True
End Sub
我已经有了这个赏金了将近一个星期，我真的不知道有多少人有这个问题。在我的头发脱了一个多星期后，考虑多次跳出办公室窗口，我想出了什么问题。
你的问题不在于python（sort的），但主要是VBA代码，我刚刚添加了
Application.ScreenUpdating = True
最后，问题停止了。不知道如果一个excel的bug不能更新，当宏完成或一个python错误，不允许屏幕更新一旦宏完成。但是做完之后，一切都很好。
谢谢!!
I'm using the following code to run an Excel macro from Python:
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:
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)
Extra question: How do I get this not to close all open Excel files?
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
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
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.
Thanks!!
这篇关于从python运行excel宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
