Python执行VBA脚本后自动覆盖保存Excel文件的解决方案

我用Python运行VBA脚本后，需要自动覆盖保存到原Excel文件，但现在每次都得手动点击「保存」才能覆盖旧文件。尝试在excel.Quit()前调用workbook.Save()时，会生成新的Excel文件，请问怎么修改代码实现自动保存？要求在「隐藏E列」的代码块之后运行VBA，全程无需手动操作。

原代码 #

import os
import io
import pyodbc
import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.utils import column_index_from_string
from openpyxl.drawing.image import Image
from tqdm import tqdm
from PIL import Image as PilImage

# Define database connection parameters
server = ...
database = ...
username = ...
password = ...

# Connect to database
cnxn = pyodbc.connect(f"DRIVER={{SQL Server}};SERVER={server};DATABASE={database};Trusted_Connection=yes;UID={username};PWD={password}")

# Read SQL query from file
with open('...item.txt', 'r', encoding='utf-8') as f:
    sql_query = f.read()

# Execute SQL query and store results in dataframe
df = pd.read_sql(sql_query, cnxn)

# Create new Excel file
excel_file = '....output.xlsx'
writer = pd.ExcelWriter(excel_file)

# Write dataframe to Excel starting from cell B1
df.to_excel(writer, index=False, startrow=0, startcol=1)

# Save Excel file
writer._save()

# Load the workbook
wb = load_workbook(excel_file)

# Select the active worksheet
ws = wb.active

# Set width of item column to 20
item_col = 'A'
ws.column_dimensions[item_col].width = 20

for i in range(2, len(df) + 2):
    ws.row_dimensions[i].height = 85

# Iterate over each row and insert the image in column A
for i, link in enumerate(df['Link to Picture']):
    if link.lower().endswith('.pdf'):
        continue  # Skip PDF links
    img_path = link.replace('file://', '')
    if os.path.isfile(img_path):
        # Open image with PIL Image module
        img_pil = PilImage.open(img_path)

        # Convert image to RGB mode
        img_pil = img_pil.convert('RGB')

        # Resize image while maintaining aspect ratio
        max_width = ws.column_dimensions[item_col].width * 7
        max_height = ws.row_dimensions[i+2].height * 1.3
        img_pil.thumbnail((max_width, max_height))

        # Convert PIL Image object back to openpyxl Image object
        img_byte_arr = io.BytesIO()
        img_pil.save(img_byte_arr, format='JPEG')
        img_byte_arr.seek(0)
        img = Image(img_byte_arr)

        cell = f'A{i+2}'  # Offset by 2 to account for header row
        ws[cell].alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center")
        ws.add_image(img, cell)

for col in range(2, ws.max_column + 1):
    max_length = 0
    column = ws.cell(row=1, column=col).column_letter
    for cell in ws[column]:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2) * 1.2 
    ws.column_dimensions[column].width = adjusted_width

# Select column E and hide it
col_E = ws.column_dimensions['E']
col_E.hidden = True

# Save the workbook
wb.save(excel_file)

import win32com.client as win32

# Connect to Excel
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False

# Open the Excel file
workbook = excel.Workbooks.Open(r'....output.xlsx')

# Add the macro to the workbook
vb_module = workbook.VBProject.VBComponents.Add(1)  # 1= vbext_ct_StdModule
macro_code = '''
Sub MoveAndSizePictures()
    Dim pic As Shape
    For Each pic In Sheets("Sheet1").Shapes
        If pic.Type = msoPicture Then
            pic.Placement = xlMoveAndSize
        End If
    Next pic
End Sub
'''
vb_module.CodeModule.AddFromString(macro_code)

# Run the macro
excel.Run('MoveAndSizePictures')

# Delete the macro from the workbook
workbook.VBProject.VBComponents.Remove(vb_module)

# Quit Excel
excel.Quit()

问题核心是win32com操作Excel时的保存逻辑未明确配置，通过以下3点调整即可实现自动覆盖保存：

1. 禁用Excel保存提示：打开文件前设置excel.DisplayAlerts = False，跳过手动确认覆盖的弹窗；
2. 明确保存+关闭流程：执行完宏后，先调用workbook.Save()保存修改，再关闭工作簿，最后退出Excel；
3. 使用绝对路径打开文件：避免相对路径导致的Excel解析保存路径出错。

修改后的完整代码 #

import os
import io
import pyodbc
import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.utils import column_index_from_string
from openpyxl.drawing.image import Image
from tqdm import tqdm
from PIL import Image as PilImage

# Define database connection parameters
server = ...
database = ...
username = ...
password = ...

# Connect to database
cnxn = pyodbc.connect(f"DRIVER={{SQL Server}};SERVER={server};DATABASE={database};Trusted_Connection=yes;UID={username};PWD={password}")

# Read SQL query from file
with open('...item.txt', 'r', encoding='utf-8') as f:
    sql_query = f.read()

# Execute SQL query and store results in dataframe
df = pd.read_sql(sql_query, cnxn)

# Create new Excel file
excel_file = '....output.xlsx'
writer = pd.ExcelWriter(excel_file)

# Write dataframe to Excel starting from cell B1
df.to_excel(writer, index=False, startrow=0, startcol=1)

# Save Excel file
writer._save()

# Load the workbook
wb = load_workbook(excel_file)

# Select the active worksheet
ws = wb.active

# Set width of item column to 20
item_col = 'A'
ws.column_dimensions[item_col].width = 20

for i in range(2, len(df) + 2):
    ws.row_dimensions[i].height = 85

# Iterate over each row and insert the image in column A
for i, link in enumerate(df['Link to Picture']):
    if link.lower().endswith('.pdf'):
        continue  # Skip PDF links
    img_path = link.replace('file://', '')
    if os.path.isfile(img_path):
        # Open image with PIL Image module
        img_pil = PilImage.open(img_path)

        # Convert image to RGB mode
        img_pil = img_pil.convert('RGB')

        # Resize image while maintaining aspect ratio
        max_width = ws.column_dimensions[item_col].width * 7
        max_height = ws.row_dimensions[i+2].height * 1.3
        img_pil.thumbnail((max_width, max_height))

        # Convert PIL Image object back to openpyxl Image object
        img_byte_arr = io.BytesIO()
        img_pil.save(img_byte_arr, format='JPEG')
        img_byte_arr.seek(0)
        img = Image(img_byte_arr)

        cell = f'A{i+2}'  # Offset by 2 to account for header row
        ws[cell].alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center")
        ws.add_image(img, cell)

for col in range(2, ws.max_column + 1):
    max_length = 0
    column = ws.cell(row=1, column=col).column_letter
    for cell in ws[column]:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2) * 1.2 
    ws.column_dimensions[column].width = adjusted_width

# Select column E and hide it
col_E = ws.column_dimensions['E']
col_E.hidden = True

# Save the workbook
wb.save(excel_file)

import win32com.client as win32

# Connect to Excel
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False
# 禁用保存提示，自动覆盖原有文件
excel.DisplayAlerts = False

# 转换为绝对路径，避免路径解析问题
excel_file_abs = os.path.abspath(excel_file)
# Open the Excel file
workbook = excel.Workbooks.Open(excel_file_abs)

# Add the macro to the workbook
vb_module = workbook.VBProject.VBComponents.Add(1)  # 1= vbext_ct_StdModule
macro_code = '''
Sub MoveAndSizePictures()
    Dim pic As Shape
    For Each pic In Sheets("Sheet1").Shapes
        If pic.Type = msoPicture Then
            pic.Placement = xlMoveAndSize
        End If
    Next pic
End Sub
'''
vb_module.CodeModule.AddFromString(macro_code)

# Run the macro
excel.Run('MoveAndSizePictures')

# Delete the macro from the workbook
workbook.VBProject.VBComponents.Remove(vb_module)

# 先保存修改，再关闭工作簿，最后退出Excel
workbook.Save()
workbook.Close()
excel.Quit()

内容的提问来源于stack exchange，提问作者Nickname_used