Can't copy full excel range image with python script

40 Views Asked by At

I have a routine job by copying multiple ranges and make daily reports. Usually I've done it manually:

  1. Select range
  2. Copy as pic
  3. Insert in paint
  4. Save as "report#&timestamp)

This code is actually working fine, except it can't copy full range. Instead of this it's limited by only 6 columns.

Clipboard tells following: Item not collected Larger than clipboard capacity

It's strange, because when I do it manually everything copying fine multiple times.

How to solve this and copy full range multiple times?

this code below is working exact i need, except can't copy full range.

import os
import win32com.client
import pyautogui
import datetime
import time

def find_workbook_and_sheet(workbook_name, sheet_name):
    xl = win32com.client.Dispatch("Excel.Application")
    for wb in xl.Workbooks:
        if wb.Name == workbook_name:
            for sheet in wb.Worksheets:
                if sheet.Name == sheet_name:
                    return wb, sheet
    return None, None

def open_paint():
    try:
        os.startfile("mspaint.exe")
    except Exception as e:
        print(f"Error opening Paint: {e}")

def main():
    print("Starting script...")
    
    # Define workbook name and sheet name
    workbook_name = "ReportForm.xlsm"
    sheet_name = "Sheet1"
    
    # Define the starting row, starting column, and ending column for the first range
    start_row = 7
    start_col = 2
    end_col = 11


    num_rows = 21
    num_cols = 10

    # Define the interval between ranges
    interval = 2

    # Define the number of ranges
    num_ranges = 5

    # Define the directory to save files
    save_directory = "C:\\Users\\User\\Downloads\\"
    
    # Find the workbook and sheet
    workbook, sheet = find_workbook_and_sheet(workbook_name, sheet_name)
    
    if workbook and sheet:
        print("Workbook and sheet found.")
               
        # Loop through each range, copy as picture, paste into Paint, and save as JPEG
        for i in range(num_ranges):
            print(f"Processing Range {i+1}...")
            
            # Calculate the starting and ending rows for the current range
            current_start_row = start_row + (num_rows + interval) * i
            current_end_row = current_start_row + num_rows - 1
            
            # Select the range in Excel
            xl = win32com.client.Dispatch("Excel.Application")
            xl.Range(sheet.Cells(current_start_row, start_col), sheet.Cells(current_end_row, end_col)).CopyPicture()
            
            # Open Paint
            print("Opening Paint...")
            open_paint()
        
            # Wait for Paint to open
            print("Waiting for Paint to open...")
            time.sleep(2)  # Adjust the delay as needed
            
            # Paste the copied range into Paint
            pyautogui.hotkey('ctrl', 'v')

            # Generate file name with date stamp and range information
            date_stamp = datetime.datetime.now().strftime("%Y-%m-%d")
            file_name = f"{date_stamp}_Range_{i+1}.jpeg"
            
            # Save the Paint image
            pyautogui.hotkey('F12')
            time.sleep(5)  # Add a short delay before typing the file name
            pyautogui.write(save_directory)
            pyautogui.typewrite(file_name)
            time.sleep(5)            
            pyautogui.press('enter')
                                              
            # Wait for the Save As dialog to open
            time.sleep(2)
                        
            # Save the image
            pyautogui.press('enter')
            
            # Close Paint
            pyautogui.hotkey('alt', 'f', 'x')
            
            # Wait before proceeding to the next range
            time.sleep(2)  # Adjust the delay as needed
        
        print("Script completed successfully.")
    else:
        print("Workbook or sheet not found.")

if __name__ == "__main__":
    main()

0

There are 0 best solutions below