回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>我试图在excel中运行一个宏,它每月生成报告宏调用python脚本时没有任何问题。但是,无论我在宏中选择哪个月,python脚本总是在一月运行。你知道吗</p>
<p>这是我选择月份的地方:
<img src="https://i.stack.imgur.com/WWykL.jpg" alt="This is where i select the month."/></p>
<p>当python脚本运行时,总是在一月:
<img src="https://i.stack.imgur.com/27wYo.jpg" alt="When the python script runs it is always January."/></p>
<p>下面是python脚本中的代码。你知道吗</p>
<pre><code>#Read the month and year selected in the Monthly Report
pathOfMonthlyReportFile = os.sep.join((os.path.expanduser("~"),
'Desktop'))+'\Monthly Activity Report - Template.xlsm'
wb = openpyxl.load_workbook(pathOfMonthlyReportFile, keep_vba=True)
sheet = wb.get_sheet_by_name('Macro')
MonthOfMonthlyReport = sheet['G10'].value
YearOfMonthlyReport = sheet['J10'].value
print(MonthOfMonthlyReport)
if MonthOfMonthlyReport == "January":
MonthNumber = 1
elif MonthOfMonthlyReport == "February":
MonthNumber = 2
elif MonthOfMonthlyReport == "March":
MonthNumber = 3
elif MonthOfMonthlyReport == "April":
MonthNumber = 4
elif MonthOfMonthlyReport == "May":
MonthNumber = 5
elif MonthOfMonthlyReport == "June":
MonthNumber = 6
elif MonthOfMonthlyReport == "July":
MonthNumber = 7
elif MonthOfMonthlyReport == "August":
MonthNumber = 8
elif MonthOfMonthlyReport == "September":
MonthNumber = 9
elif MonthOfMonthlyReport == "October":
MonthNumber = 10
elif MonthOfMonthlyReport == "November":
MonthNumber = 11
elif MonthOfMonthlyReport == "December":
MonthNumber = 12
MonthlyReportDate =
now.replace(month=MonthNumber,year=int(YearOfMonthlyReport))
if MonthNumber == 12:
last_day_of_month = MonthlyReportDate.replace(day = 31, month=MonthNumber,
year=int(YearOfMonthlyReport))
else:
last_day_of_month = MonthlyReportDate.replace(day=1, month=MonthNumber+1,
year=int(YearOfMonthlyReport)) - datetime.timedelta(days=1)
if MonthNumber >= 10:
stringenddate = "%s-%s-%s" % (MonthlyReportDate.year,
MonthlyReportDate.month,last_day_of_month.day)
stringstartdate = "%s-%s-01" % (MonthlyReportDate.year,
MonthlyReportDate.month)
else:
stringenddate = "%s-0%s-%s" % (MonthlyReportDate.year,
MonthlyReportDate.month,last_day_of_month.day)
stringstartdate = "%s-0%s-01" % (MonthlyReportDate.year,
MonthlyReportDate.month)
print(stringenddate)
MonthRange = [1,2,3,4,5,6,7,8,9,10,11,12]
ThreeMonthsAgo = 0
for i in range(len(MonthRange)):
if MonthRange[i] == MonthNumber:
ThreeMonthsAgo = MonthRange[i-2] #**************
print(ThreeMonthsAgo)
if ThreeMonthsAgo<MonthNumber:
if ThreeMonthsAgo >=10:
stringthreemonthsagodate = "%s-%s-01"%(MonthlyReportDate.year,
ThreeMonthsAgo)
else:
stringthreemonthsagodate = "%s-0%s-01"%(MonthlyReportDate.year,
ThreeMonthsAgo)
if ThreeMonthsAgo > MonthNumber:
if ThreeMonthsAgo >=10:
stringthreemonthsagodate = "%s-%s-01"%(MonthlyReportDate.year - 1,
ThreeMonthsAgo)
else:
stringthreemonthsagodate = "%s-0%s-01"%(MonthlyReportDate.year - 1,
ThreeMonthsAgo)
date_ranges = [(stringstartdate,
stringenddate)]
data_ranges_for_three_months = [(stringthreemonthsagodate, stringenddate)]
</code></pre>
<p>它应该在所选月份运行,但只在一月运行。
我真的没有得到一个错误消息,但它只是不是正确的一个月。你知道吗</p>
<hr/>
<p>已经修好了。这个解决方案有点尴尬,但我会发帖的一切都好我要做的是,每当我在宏中更改月份时,我都需要保存.xlsm,然后运行它。你知道吗</p>