I have a routine job by copying multiple ranges and make daily reports. Usually I've done it manually:
- Select range
- Copy as pic
- Insert in paint
- Save as "report#×tamp)
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()