How to use Application.ClipboardFormats in the xlwings API way

The Application.ClipboardFormats property in Excel VBA returns an array that lists the data formats currently available on the Clipboard. This is useful for programmatically determining what type of data (e.g., text, bitmap, HTML) has been copied, allowing your xlwings script to handle pasting operations intelligently or to verify clipboard content before proceeding. In xlwings, you access this property through the Application object, which represents the Excel application instance.

Functionality:
The primary function is to inspect the clipboard. It does not alter the clipboard’s contents. By retrieving the array of format IDs, you can check for the presence of specific formats (like xlClipboardFormatText or xlClipboardFormatBitmap) to decide the appropriate course of action in your automation script, such as conditional pasting or data validation.

Syntax in xlwings:

import xlwings as xw

app = xw.apps.active # Or xw.App() for a new instance
formats_array = app.api.ClipboardFormats

• app: This is your xlwings App object.
• .api: This provides direct access to the underlying Excel VBA object model.
• ClipboardFormats: This is the property being called. It takes no arguments.
  The property returns a 1-based array (Variant) containing the integer IDs of the available formats. If the clipboard is empty, it may return None or an empty variant. The specific integer IDs correspond to Excel’s XlClipboardFormat enumeration. Common values include:
• 1: xlClipboardFormatText
• 2: xlClipboardFormatBitmap
• 8: xlClipboardFormatHTML
• -4142: xlClipboardFormatLink (DDE link)

Code Examples:

1. Basic Retrieval and Display:

import xlwings as xw
import sys

app = xw.apps.active
formats = app.api.ClipboardFormats

if formats is not None:
    # Convert the returned COM array to a Python list for easy handling
    # On Windows, the returned object is often a `tuple` when accessed via pywin32.
    format_list = list(formats)
    print(f"Available Clipboard Formats (IDs): {format_list}")
else:
    print("Clipboard is empty or formats cannot be retrieved.")

2. Checking for a Specific Format Before Pasting:
   This example checks if text is available on the clipboard before pasting into a specific cell.

import xlwings as xw

app = xw.apps.active
wb = app.books.active
sht = wb.sheets[0]

formats = app.api.ClipboardFormats

# Check if Text format (ID 1) is present
if formats is not None and 1 in formats:
    sht.range("A1").select() # Select the target cell
    app.api.Selection.PasteSpecial(Paste=-4163) # xlPasteValues = -4163
    # Alternatively, for a more xlwings-native approach after checking:
    # text_data = pyperclip.paste() # Using pyperclip module
    # sht.range("A1").value = text_data
else:
    print("No text format found on clipboard. Pasting aborted.")

3. Logging Available Formats for Debugging:

import xlwings as xw
import logging

logging.basicConfig(level=logging.INFO)
app = xw.apps.active

formats = app.api.ClipboardFormats
if formats:
    logging.info("Clipboard Snapshot:")
    for fmt_id in formats:
        # Map some known IDs for better readability
        format_names = {1: "Text", 2: "Bitmap", 8: "HTML", -4142: "DDE Link"}
        name = format_names.get(fmt_id, f"Unknown ID ({fmt_id})")
    logging.info(f" - {name}")