Blog

How to use Application.MailLogoff in the xlwings API way

The Application.MailLogoff method in Excel is used to log the user off from an email system (such as Microsoft Outlook) that has been previously logged into through Excel. This is particularly relevant when using Excel’s email features, like sending workbooks via email programmatically. In xlwings, which provides a Pythonic way to automate Excel, you can access this method through the Application object to manage email sessions cleanly, ensuring resources are released and sessions are properly closed after email operations.

Functionality
The primary function of MailLogoff is to terminate an active email session that was initiated via Excel. This is useful in automation scripts where you send emails from Excel and want to log out afterward to prevent issues like multiple open sessions or security concerns. It does not take any parameters and simply ends the session. Note that this method is part of the Excel object model and may not be frequently used in modern automation, as many developers prefer direct email libraries (e.g., smtplib in Python), but it remains available for compatibility with Excel-based email workflows.

Syntax
In xlwings, you call MailLogoff through the Application object. The syntax is straightforward since it has no arguments:

app.xl_app.MailLogoff()

Here, app refers to an xlwings App instance representing the Excel application. xl_app is the underlying COM object (via pywin32 on Windows or appscript on macOS) that exposes the Excel Application object from the Excel object model. The method is invoked without any parameters.

Example
Below is a complete xlwings code example that demonstrates logging into an email session (using MailLogon, which is often paired with MailLogoff) and then logging off. This example assumes you have Excel and an email client like Outlook set up. Note that MailLogon may require credentials, but in practice, it often uses the current user’s default profile.

import xlwings as xw

# Start an Excel application instance
app = xw.App(visible=False) # Set visible=True to see Excel

try:
# Log into email (this might trigger a login prompt or use default credentials)
# In some cases, MailLogon may not be needed if a session is already active
    app.xl_app.MailLogon(Name="YourName", Password="YourPassword",    DownloadNewMail=False)
    print("Logged into email successfully.")

    # Perform email-related tasks, e.g., send a workbook via email
    # (Code for sending email would go here, but is omitted for brevity)

    # Log off from the email session
    app.xl_app.MailLogoff()
    print("Logged off from email.")

except Exception as e:
    print(f"An error occurred: {e}")

finally:
# Close the Excel application
    app.quit()

How to use Application.MacroOptions in the xlwings API way

The MacroOptions member of the Application object in Excel’s object model provides a way to configure settings related to macros, particularly the Macro Recorder. This is useful for developers who need to programmatically control how macros are recorded, such as setting the description, shortcut key, or category for a newly recorded macro. While the primary interface is through Excel VBA, xlwings allows you to access and manipulate this functionality from Python, enabling automation of macro-related setups in Excel workbooks.

In xlwings, you can access the MacroOptions method through the api property of an App or Book object, which exposes the underlying Excel object model. The syntax for calling MacroOptions in xlwings closely mirrors its VBA counterpart, but adapted for Python. The general format is:

app.api.MacroOptions(Macro, Description, HasMenu, MenuText, HasShortcutKey, ShortcutKey, Category, StatusBar, HelpContextID, HelpFile)

Here, app is an instance of xlwings.App, representing the Excel application. The parameters are optional and correspond to the settings you can configure for a macro. Below is a table detailing each parameter:

Parameter	Type	Description	Default Value in xlwings (if omitted)
Macro	String	The name of the macro (e.g., “MyMacro”).	Required; no default.
Description	String	A description for the macro.	None (ignored).
HasMenu	Boolean	True to add the macro to a menu; False otherwise.	None (ignored).
MenuText	String	The text to display in the menu if HasMenu is True.	None (ignored).
HasShortcutKey	Boolean	True to assign a shortcut key; False otherwise.	None (ignored).
ShortcutKey	String	The shortcut key (e.g., “Ctrl+Shift+M”).	None (ignored).
Category	String	The category for the macro (e.g., “Custom Functions”).	None (ignored).
StatusBar	String	Text to display in the status bar when the macro is selected.	None (ignored).
HelpContextID	Long	The context ID for Help.	None (ignored).
HelpFile	String	The path to the Help file.	None (ignored).

In practice, you can use this method to set options for an existing macro or to pre-configure settings before recording. For example, to set a description and shortcut key for a macro named “TestMacro” in an active Excel workbook, you can write the following xlwings code:

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.apps.active

# Configure macro options for "TestMacro"
app.api.MacroOptions(
Macro="TestMacro",
Description="This macro performs a data cleanup operation.",
HasShortcutKey=True,
ShortcutKey="Ctrl+Shift+T"
)

How to use Application.Intersect in the xlwings API way

The Application.Intersect method in Excel is a powerful tool for determining the overlapping range between two or more specified ranges. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model. This is particularly useful for tasks that require identifying common areas across different data sets, such as validating data entry, applying conditional formatting to specific intersections, or performing calculations only on overlapping cells.

Functionality:
The primary function of Intersect is to return a Range object that represents the rectangular intersection of two or more ranges. If the ranges do not overlap, it returns None. This allows for conditional logic in scripts to handle cases where no overlap exists.

Syntax in xlwings:
The xlwings API call follows the pattern:

intersection_range = xw.apps[app_key].api.Intersect(Range1, Range2, ...)

• app_key: The key identifier for the Excel application instance, typically accessed via xw.apps (e.g., xw.apps.active for the active app).
• Range1, Range2, …: These are Range objects representing the areas to check for overlap. You can specify two or more ranges, separated by commas. Each range must be a valid Excel range, which in xlwings can be defined using methods like sheet.range() or sheet.cells().

Parameters:
The parameters are Range objects. In practice, you pass the ranges as arguments directly to the Intersect method. There is no fixed limit on the number of ranges, but at least two are required. The ranges can be from the same or different worksheets within the same workbook.

Code Examples:

1. Basic Intersection Check:
   This example checks if two ranges on a sheet overlap and prints the address of the intersection if it exists.

import xlwings as xw

# Connect to the active workbook and sheet
wb = xw.books.active
sheet = wb.sheets['Sheet1']

# Define two ranges
range1 = sheet.range('A1:C10')
range2 = sheet.range('B5:E15')

# Get the intersection using the Excel API
intersection = sheet.api.Application.Intersect(range1.api, range2.api)

if intersection:
    print(f"Intersection address: {intersection.Address}")
else:
    print("No overlap between the ranges.")

2. Applying Formatting to an Intersection:
   Here, conditional formatting is applied to the overlapping area to highlight it.

import xlwings as xw

wb = xw.books.active
sheet = wb.sheets['DataSheet']

# Define multiple ranges
range_a = sheet.range('D1:F20')
range_b = sheet.range('E10:H30')

# Find the intersection
overlap = wb.api.Application.Intersect(range_a.api, range_b.api)

if overlap:
# Apply yellow fill to the overlapping cells
    overlap.Interior.Color = 65535 # Yellow color in RGB
    print("Formatting applied to intersection.")
else:
    print("No intersection to format.")

3. Data Validation in an Intersection:
   This example sums values only in the overlapping cells of three ranges.

import xlwings as xw

app = xw.apps.active
sheet = app.books['SalesData'].sheets['Monthly']

# Create three ranges
r1 = sheet.range('B2:M10')
r2 = sheet.range('F5:J15')
r3 = sheet.range('A1:Z20')

# Get the common intersection
common_area = app.api.Intersect(r1.api, r2.api, r3.api)

if common_area:
    total = sum(cell.value for cell in common_area if isinstance(cell.value, (int, float)))
    print(f"Total sum in intersection: {total}")
else:
    print("No common area found for calculation.")

How to use Application.InputBox in the xlwings API way

The Application.InputBox method in Excel VBA is a versatile tool for displaying a dialog box that prompts the user for input. In xlwings, this functionality is exposed through the api property, allowing direct access to the underlying Excel object model. This method is particularly useful for creating interactive Excel applications where you need to gather specific information from the user, such as a string, number, cell reference, or even a formula. Unlike a simple input box, it can validate the type of input, making data collection more robust.

Syntax in xlwings:
The method is accessed via the Application object. The general xlwings API call format is:

app.api.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

Where:

• Prompt (Required, String): The message displayed in the dialog box.
• Title (Optional, Variant): The title for the input box window. If omitted, the default title is “Input”.
• Default (Optional, Variant): A default value that appears in the text box when the dialog is shown.
• Left, Top (Optional, Variant): The screen coordinates (in points) for the upper-left corner of the dialog box.
• HelpFile, HelpContextID (Optional, Variant): Identifiers for a custom Help file.
• Type (Optional, Variant): Specifies the return data type. If omitted, it defaults to 0 (a String). This is a critical parameter.

The Type argument can take the following values:

Value	Meaning
0	A formula
1	A number
2	Text (a string)
4	A logical value (True or False)
8	A cell reference, as a Range object
16	An error value, such as #N/A
64	An array of values

You can sum these values to allow multiple types (e.g., Type=1+2 allows both numbers and text). If the user enters data of an incorrect type or clicks “Cancel”, the method returns False.

Code Examples:

1. Prompting for a Text String (Default):

import xlwings as xw
app = xw.apps.active
user_name = app.api.InputBox(Prompt="Enter your name:", Title="User Info", Type=2)
if user_name is not False:
    print(f"Hello, {user_name}")

2. Prompting for a Number with a Default Value:

budget = app.api.InputBox("Enter the project budget:", "Budget Input", Default=10000, Type=1)
if budget is not False:
    total = budget * 1.1
    print(f"Budget with contingency: {total}")

3. Prompting for a Cell Reference (returns an xlwings Range object):

target_range = app.api.InputBox("Select a data range:", "Range Selector", Type=8)
if target_range is not False:
    # target_range is an xlwings Range object
    values = target_range.value
    print(f"Selected values: {values}")

4. Allowing Multiple Input Types (Number or Text):

data = app.api.InputBox("Enter ID (number) or Name (text):", "Data Entry", Type=1+2)
if data is not False:
    print(f"Received: {data} (Type: {type(data).__name__})")

How to use Application.InchesToPoints in the xlwings API way

The InchesToPoints member of the Application object in Excel is a method used to convert a measurement from inches to points. In the context of Excel and desktop publishing, a “point” is a unit of measurement equal to 1/72 of an inch. This conversion is particularly useful when programmatically setting or adjusting properties that require point values, such as row heights, column widths, font sizes, or shape dimensions, but where the initial measurement or design specification is more conveniently thought of in inches. Using InchesToPoints ensures precision and consistency in layout and formatting tasks within a workbook.

Function:
The primary function of Application.InchesToPoints is to take a numeric value representing a length in inches and return the equivalent length in points as a Single (floating-point) data type.

Syntax in xlwings:
In xlwings, you access this method through the app object, which represents the Excel application. The syntax is straightforward:

app.api.InchesToPoints(Inches)

• app: Your xlwings App instance. This is typically obtained with xw.App() or as xw.apps[#] when connecting to an existing instance.
• .api: This property provides direct access to the underlying Excel object model (the COM API). It is necessary for calling this method, as xlwings does not wrap every single Excel method in its high-level API.
• Inches: (Required) A Single or Double number representing the length in inches that you want to convert to points.

Code Examples:
Here are practical examples demonstrating how to use Application.InchesToPoints with xlwings.

1. Basic Conversion:
   This example simply converts a measurement and prints the result.

import xlwings as xw

# Start a new Excel instance or connect to a running one
app = xw.App(visible=False) # Use visible=True to see Excel

# Convert 2.5 inches to points
points_value = app.api.InchesToPoints(2.5)
print(f"2.5 inches is equal to {points_value} points.")
# Output: 2.5 inches is equal to 180.0 points.

app.quit()

2. Setting Row Height Based on Inches:
   A common use case is setting a row’s height to a specific inch measurement.

import xlwings as xw

app = xw.App(visible=False)
wb = app.books.add()
ws = wb.sheets[0]

# Desired row height: 0.75 inches
desired_height_inches = 0.75
row_height_points = app.api.InchesToPoints(desired_height_inches)

# Set the height of the first row
ws.api.Rows(1).RowHeight = row_height_points

print(f"Set Row 1 height to {desired_height_inches} inches ({row_height_points} points).")
wb.save('row_height_example.xlsx')
app.quit()

3. Setting Column Width Based on Inches:
   While column width in Excel uses a different, character-based unit, this method can be part of calculations for shapes or other objects placed relative to columns. For direct cell formatting related to width/height in points, it’s applicable.

import xlwings as xw

app = xw.App(visible=True)
wb = app.books.add()
ws = wb.sheets[0]

# Create a shape and set its width based on inches
# Let's say we want a rectangle that is 2 inches wide
shape_width_inches = 2.0
shape_width_points = app.api.InchesToPoints(shape_width_inches)

# Add a rectangle shape
my_shape = ws.shapes.add_shape(
type=1, # 1 corresponds to a Rectangle
left=ws.range('C5').left,
top=ws.range('C5').top,
width=shape_width_points,
height=50 # height in points
)
my_shape.name = "MyRectangle"

print(f"Shape width set to {shape_width_inches} inches ({shape_width_points} points).")

# Keep the workbook open
input("Press Enter to close...")
app.quit()