How to use Worksheet.SaveAs in the xlwings API way

The SaveAs method in the Worksheet object of the Excel object model is a powerful feature for saving a specific worksheet as a new workbook file. This is particularly useful in scenarios where you need to extract or archive a single sheet from a larger workbook, or when generating reports from a template. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model, allowing for precise control similar to VBA.

The syntax for calling SaveAs via xlwings is: worksheet.api.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodepage, TextVisualLayout, Local). The key parameters are:

• Filename: A string specifying the full path and name of the file to save. It is required.
• FileFormat: A constant or number specifying the file format. Common values include 51 for .xlsx (Excel Workbook), 52 for .xlsm (Macro-Enabled Workbook), and 6 for .csv (CSV format). If omitted, the current file format is used.
• Password: An optional string to set a password for opening the workbook.
• WriteResPassword: An optional string to set a password for write reservation.
• CreateBackup: If set to True, Excel creates a backup file.
• Other parameters like ReadOnlyRecommended, AddToMru, TextCodepage, TextVisualLayout, and Local are less frequently used and often left as default.

For example, to save the active worksheet as a new Excel workbook in the .xlsx format to a specific directory, you can use the following xlwings code. This example assumes you have an existing workbook with a worksheet object referenced. The code saves the worksheet “DataSheet” as a separate file, ensuring the original workbook remains unchanged. It demonstrates setting a filename, specifying the file format, and optionally adding a password for protection. This method is efficient for automating report generation or data extraction tasks, as it leverages Excel’s native saving capabilities through a clean Python interface.

import xlwings as xw

# Connect to the active Excel instance or open a workbook
app = xw.apps.active
wb = app.books['OriginalWorkbook.xlsx']
ws = wb.sheets['DataSheet']

# Save the worksheet as a new workbook
new_file_path = r'C:\Reports\Extracted_DataSheet.xlsx'
ws.api.SaveAs(Filename=new_file_path, FileFormat=51) # 51 corresponds to .xlsx

# To save as a CSV file without a password
csv_path = r'C:\Reports\DataSheet.csv'
ws.api.SaveAs(Filename=csv_path, FileFormat=6) # 6 corresponds to CSV

# To save with a password for opening
protected_path = r'C:\Reports\Secure_DataSheet.xlsx'
ws.api.SaveAs(Filename=protected_path, FileFormat=51, Password='mysecret')