{"id":2315,"date":"2026-09-18T07:27:29","date_gmt":"2026-09-17T23:27:29","guid":{"rendered":"https:\/\/xlwings.net\/blog\/?p=2315"},"modified":"2026-03-28T12:59:53","modified_gmt":"2026-03-28T12:59:53","slug":"how-to-use-worksheetcustomproperties-in-the-xlwings-api-way","status":"publish","type":"post","link":"https:\/\/xlwings.net\/blog\/how-to-use-worksheetcustomproperties-in-the-xlwings-api-way\/","title":{"rendered":"How to use Worksheet.CustomProperties in the xlwings API way"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The <code>CustomProperties<\/code> member of the <code>Worksheet<\/code> object in Excel&#8217;s object model provides a powerful way to store and retrieve custom metadata associated with a specific worksheet. These properties are stored directly within the Excel file and persist with it, making them ideal for attaching auxiliary information like configuration settings, version numbers, author notes, or any custom identifiers that your automation scripts might need. Unlike cell values, they are not directly visible on the grid, offering a clean way to embed data for programmatic use.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In xlwings, you access this collection through the <code>api<\/code> property of a <code>Sheet<\/code> object, which grants direct access to the underlying Excel VBA object model. The <code>CustomProperties<\/code> collection itself has methods to <code>Add<\/code>, <code>Item<\/code> (for retrieval), and <code>Count<\/code>, and each <code>CustomProperty<\/code> object has <code>Name<\/code> and <code>Value<\/code> properties.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Key xlwings API Syntax:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Accessing the Collection:<\/strong> <code>sheet.api.CustomProperties<\/code><\/li>\n\n\n\n<li><strong>Adding a Property:<\/strong> <code>sheet.api.CustomProperties.Add(Name, Value)<\/code><\/li>\n\n\n\n<li><code>Name<\/code>: A required <code>String<\/code> that is the unique identifier for the property.<\/li>\n\n\n\n<li><code>Value<\/code>: A required <code>Variant<\/code> that can be a string, number, or boolean. This is the data stored.<\/li>\n\n\n\n<li><strong>Retrieving a Property by Name:<\/strong> <code>sheet.api.CustomProperties.Item(Name)<\/code><\/li>\n\n\n\n<li>Returns a <code>CustomProperty<\/code> object.<\/li>\n\n\n\n<li><strong>Getting\/Setting a Property&#8217;s Value:<\/strong> <code>cp.Value<\/code> (where <code>cp<\/code> is a <code>CustomProperty<\/code> object).<\/li>\n\n\n\n<li><strong>Getting the Count:<\/strong> <code>sheet.api.CustomProperties.Count<\/code><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Code Examples:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Adding and Reading a Custom Property:<\/strong><\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>import xlwings as xw\n\n# Connect to an open workbook or open a new one\nwb = xw.Book(r'C:\\path\\to\\your\\file.xlsx')\nsheet = wb.sheets&#91;'Sheet1']\n\n# Add a custom property\nsheet.api.CustomProperties.Add(\"DataVersion\", \"2.5\")\nsheet.api.CustomProperties.Add(\"Processed\", True)\n\n# Read a specific property's value\ntry:\n    version_prop = sheet.api.CustomProperties.Item(\"DataVersion\")\n    print(f\"Data Version: {version_prop.Value}\") # Output: Data Version: 2.5\nexcept:\n    print(\"Property not found.\")\n\n# Iterate through all custom properties\nfor i in range(1, sheet.api.CustomProperties.Count + 1):\n    cp = sheet.api.CustomProperties.Item(i) # Can also index by position\n    print(f\"{cp.Name}: {cp.Value}\")\n    # Output might be:\n    # DataVersion: 2.5\n    # Processed: True<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li><strong>Updating an Existing Property:<\/strong><\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code># Check if a property exists and update it\nprop_name = \"LastRefresh\"\ntry:\n    last_refresh_prop = sheet.api.CustomProperties.Item(prop_name)\n    last_refresh_prop.Value = \"2024-05-27 14:30\" # Update the value\nexcept:\n    # If it doesn't exist, create it\n    sheet.api.CustomProperties.Add(prop_name, \"2024-05-27 14:30\")<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li><strong>Using Properties for Workflow Control:<\/strong><\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code># A common use case is to mark if a sheet has been initialized or processed\nif sheet.api.CustomProperties.Count > 0:\n    status_prop = sheet.api.CustomProperties.Item(\"InitializationStatus\")\n    if status_prop.Value == \"Complete\":\n        print(\"Sheet is ready for analysis.\")\n    else:\n        print(\"Running setup macro...\")\n    # ... run setup code ...\n    status_prop.Value = \"Complete\"\nelse:\n    print(\"First-time setup required.\")\n    sheet.api.CustomProperties.Add(\"InitializationStatus\", \"Complete\")\n# Save the workbook to persist the properties\nwb.save()<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The `CustomProperties` member of the `Worksheet` object in Excel&apos;s object model provides a powerful &#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25],"tags":[],"class_list":["post-2315","post","type-post","status-publish","format-standard","hentry","category-xlwings-api-reference"],"_links":{"self":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2315","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/comments?post=2315"}],"version-history":[{"count":2,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2315\/revisions"}],"predecessor-version":[{"id":3530,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2315\/revisions\/3530"}],"wp:attachment":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/media?parent=2315"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/categories?post=2315"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/tags?post=2315"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}