用python批量执行VBA代码

先说下背景环境

　　1. 公司需要问卷调查，有两份问卷， 1）是spss问卷，2）是excel问卷。spss问卷数据不全，但有各种标签， excel呢， 生成的数据直接把选项变成了值

　　2. 现在需要把excel的选项值变成1， 2这种数字{1：“满意”}

　　3. 妹子已经把vba写好了。（不忍直视！）

 

由于需要执行很多vba命令，我就用py的字符串拼接，生成了，900多份，怀疑vba的代码有问题，凭直觉，但我不会vba，又懒得查，只能让cpu去做重复工作了

 

需要模块win32com

安装方法

```

python3 -m pip install pypiwin32

```

 

生成vba命令

#!/usr/bin/env python # -*- coding:utf-8 -*- import savReaderWriterfilepath = "/opt/code/my_code/testStata/5976d077606f07d4418b46eb160938.sav"a = '''Dim m m = 1 Columns("'''b = '''").Select m = 1 On Error GoTo Err_Handle'''c = '''For m = 1 To 65 Selection.Find(What:="'''d = '''", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, MatchByte:=False, SearchFormat:=False).Activate ActiveCell.Replace What:="'''e = '''", Replacement:="'''f = '''", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next m '''g = ''' Exit Sub Err_Handle: End Sub'''sum = 0totalStr = '\n************hello************\n\n'excelList = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',"AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ","BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ","CA","CB","CC","CD","CE","CF","CG","CH","CI","CJ","CK","CL","CM","CN","CO","CP","CQ","CR","CS","CT","CU","CV","CW","CX","CY","CZ","DA","DB","DC","DD","DE","DF","DG","DH","DI","DJ","DK","DL","DM","DN","DO","DP","DQ","DR","DS","DT","DU","DV","DW","DX","DY","DZ","EA","EB","EC","ED","EE","EF","EG","EH","EI","EJ","EK","EL","EM","EN","EO","EP","EQ","ER","ES","ET","EU","EV","EW","EX","EY","EZ","FA","FB","FC","FD","FE","FF","FG","FH","FI","FJ","FK","FL","FM","FN","FO","FP","FQ","FR","FS","FT","FU","FV","FW","FX","FY","FZ","GA","GB","GC","GD","GE","GF","GG","GH","GI","GJ","GK","GL","GM","GN","GO","GP","GQ","GR","GS","GT","GU","GV","GW","GX","GY","GZ","HA","HB","HC","HD","HE","HF","HG","HH","HI","HJ","HK","HL","HM","HN","HO","HP","HQ","HR","HS","HT","HU","HV","HW","HX","HY","HZ","IA","IB","IC","ID","IE","IF","IG","IH","II","IJ","IK","IL","IM","IN","IO","IP","IQ","IR","IS","IT","IU","IV","IW","IX","IY","IZ","JA","JB","JC","JD","JE","JF","JG","JH","JI","JJ","JK","JL","JM","JN","JO","JP","JQ",]def readSpss():with savReaderWriter.SavReader(filepath, ioUtf8=True) as read:ret = read.getSavFileInfo()return ret[4], ret[2], ret[5], ret[6]def vbaStr(totalStr, sum, readSpss):formats, varnames, varLabels, valueLabels = readSpss()for i in range(len(varnames)):if varnames[i] in valueLabels:subvalueLables = {}for j in valueLabels[varnames[i]]:subvalueLables[int(j)] = valueLabels[varnames[i]][j]# totalStr += a + excelList[i] + ":" + excelList[i] + bfor zz in subvalueLables:totalStr += a + excelList[i] + ":" + excelList[i] + btotalStr += c + subvalueLables[zz] + d + subvalueLables[zz] + e + str(zz) + ftotalStr += gsum += 1totalStr += "\n************hello************\n\n"# totalStr += gtotalStr += "=============================\n\n"else:continuereturn totalStr, sumtotalStr, sum = vbaStr(totalStr, sum, readSpss)print(totalStr)print(sum) View Code

 

用Python在执行vba命令方法，有点类似执行shell的形式，但必须在windows环境下面执行，不能在linux下面

注意问题：

　　1. 必须另存为xlsm文件格式2. 第二，在执行Python脚本的时候必须是打开excel的3. 必须新建模块（vba中）4. vba代码必须有函数5. office必须开启宏，否则不生效
　　6. 打开vba快捷键alt + f11

 

 

 

 

最后看代码

vba代码

Sub test() 《============这个函数很重要，对应着python Dim m m = 1 Columns("G:G").Select m = 1 On Error GoTo Err_HandleFor m = 1 To 65 Selection.Find(What:="男", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, MatchByte:=False, SearchFormat:=False).Activate ActiveCell.Replace What:="男", Replacement:="1", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next mExit Sub Err_Handle: End Sub

 

python代码

#!/usr/bin/env python # -*- coding:utf-8 -*-import win32com.clientfilename = "C:/Users/Administrator/Desktop/test.xlsm" xls = win32com.client.Dispatch('Excel.Application') xls.Workbooks.Open(Filename=filename) xls.DisplayAlerts = 0 xls.Run("test1")

  

总结

以上是生活随笔为你收集整理的用python批量执行VBA代码的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。