Lost order between file save and file quit

Question

I want to save a file, on top of my old file, after python running VBA script. But problem that I every time need press 'Save' to overwrite my old file, what I can do with this code, to make press Save automatic ? When I write workbook.Save() before excel.Quit() its creating new excel file.

After column E hide block, need run VBA and then save file without my 'touch'

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()

Answer 1

If workbook.Save() is creating a new file instead of overwriting the existing one, it could be, for instance, because the workbook is opened in another instance of Excel or another program might be using the file.

One workaround might be to (using shutil):

• save the workbook under a temporary name,
• close Excel,
• delete the original file,
• and then rename the temporary file to the original file's name.

Something like:

import os
import time
import shutil

# ...

# Define the name of the temporary file
temp_file = '....output_temp.xlsx'

# Save the workbook under a temporary name
workbook.SaveAs(temp_file)

# Quit Excel
excel.Quit()

# Wait for a moment to make sure Excel has fully closed
time.sleep(2)

# Delete the original file
os.remove(excel_file)

# Rename the temporary file to the original file's name
shutil.move(temp_file, excel_file)

This should ensure that the changes made by the VBA script are saved without Excel creating a new file.
Do replace '....output_temp.xlsx' with the actual path to your temporary file.

But, again, this is just a workaround.

---

If I add your code after # Delete the macro from the workbook, I get message : "PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: '....output.xlsx'".

The error message you are seeing is likely due to Excel not having fully closed and released the file before Python attempts to delete it. This can occur because the excel.Quit() command may return before Excel has completely closed.

One way to resolve this issue is to add a delay after the excel.Quit() command to ensure that Excel has fully closed before Python attempts to delete the file.

The modified code would be:

import os
import time
import shutil

# ...

# Define the name of the temporary file
temp_file = '....output_temp.xlsx'

# Save the workbook under a temporary name
workbook.SaveAs(temp_file)

# Quit Excel
excel.Quit()

# Wait for a moment to make sure Excel has fully closed
time.sleep(5)  # Wait for 5 seconds. Adjust this value as needed.

# Now, we should be able to delete and rename the files
try:
    # Delete the original file
    os.remove(excel_file)

    # Rename the temporary file to the original file's name
    shutil.move(temp_file, excel_file)

except PermissionError:
    print(f"Could not delete {excel_file}. The file might still be in use.")

This code will wait for 5 seconds after quitting Excel before it tries to delete the original file and rename the temporary file. If you are still seeing the PermissionError, you can try increasing the wait time.

Again, this is a workaround, and it might not work in every situation. For example, if another program (other than the Python script) is using the file, you might still see a PermissionError.

---

If that is not working,then you might need a simple function that checks if a file is locked by trying to open it in append mode. If the file is locked by another process, the function will raise an exception:

import os

def is_file_locked(file_path):
    locked = None
    file_object = None
    if os.path.exists(file_path):
        try:
            file_object = open(file_path, 'a')   # try to open file in append mode
            locked = False
        except IOError:
            locked = True
        finally:
            if file_object:
                file_object.close()
    else:
        print(f"{file_path} doesn't exist")
    return locked

You can use this function in your code to check if the file is locked before trying to delete or rename it:

# Now, we should be able to delete and rename the files
if not is_file_locked(excel_file):
    try:
        # Delete the original file
        os.remove(excel_file)

        # Rename the temporary file to the original file's name
        shutil.move(temp_file, excel_file)

    except PermissionError:
        print(f"Could not delete {excel_file}. The file might still be in use.")
else:
    print(f"{excel_file} is locked by another process.")

Do note that this function only checks if the file is locked at the moment the function is called. It's possible that the file could become locked again between the time you check and the time you try to delete or rename the file. This is known as a race condition. Therefore, you should still handle exceptions when you try to delete or rename the file.

Answer 2

If you are familiar with VBA but running code in python. I will suggest you to use win32com python package. This package will give you similar feelings like handling VBA excel. Most of the syntax is similar like VBA syntax code just the different is the code run in Python.

#python package that u need
import os
import win32com.client as xcel

excelwb = xcel.gencache.EnsureDispatch("Excel.Application")
oldWB = excelwb.Workbooks.Open(str("your full path existing excel file"))
wbsheet = oldWB.Worksheets[1]

##do your write file here

##after complete write the file:
##here will check if file exists then it will save otherwise will do SaveAS
try: 
  os.path.exists("your full path existing excel file")
  oldWB.Save()
except:
  oldWB.SaveAs("your full path existing excel file")

excelwb.Quit()