{"id":2234,"date":"2026-08-08T15:59:26","date_gmt":"2026-08-08T07:59:26","guid":{"rendered":"https:\/\/xlwings.net\/blog\/?p=2234"},"modified":"2026-03-28T11:40:55","modified_gmt":"2026-03-28T11:40:55","slug":"how-to-use-workbooksopendatabase-in-the-xlwings-api-way","status":"publish","type":"post","link":"https:\/\/xlwings.net\/blog\/how-to-use-workbooksopendatabase-in-the-xlwings-api-way\/","title":{"rendered":"How to use Workbooks.OpenDatabase in the xlwings API way"},"content":{"rendered":"\n

The OpenDatabase<\/strong> member of the Workbooks<\/strong> object in the Excel object model is a method used to connect to and import data from an external database directly into Excel. This functionality is particularly valuable for automating data retrieval from sources like Microsoft Access, SQL Server, or other ODBC-compliant databases, enabling dynamic report generation and data analysis without manual copy-paste operations. In xlwings, this method provides a programmatic way to execute such database queries through Excel’s engine, leveraging its native data connection capabilities.<\/p>\n\n\n\n

Syntax and Parameters<\/strong><\/p>\n\n\n\n

In xlwings, the method is accessed via the api<\/code> property of an Excel App<\/code> or Book<\/code> object. The typical call pattern is:<\/p>\n\n\n\n

workbook.api.OpenDatabase(Connection, CommandText, CommandType, BackgroundQuery, ImportDataAs)<\/code><\/pre>\n\n\n\n
The parameters map closely to the VBA Workbooks.OpenDatabase<\/code> method. Below is a detailed breakdown:<\/p>\n\n\n\n
Parameter<\/th>Description<\/th>Typical Values \/ How to Specify<\/th><\/tr><\/thead>
Connection<\/code><\/td>A string that defines the connection to the database. This includes the data source and any necessary credentials.<\/td>For an Access database: \"DSN=MS Access Database;DBQ=C:\\\\path\\\\database.accdb;\"<\/code>. For SQL Server: \"ODBC;DSN=MyServerDSN;UID=user;PWD=password;\"<\/code>.<\/td><\/tr>
CommandText<\/code><\/td>The SQL query string or the name of a table, query, or stored procedure to run.<\/td>\"SELECT * FROM SalesData\"<\/code> or \"TableName\"<\/code>.<\/td><\/tr>
CommandType<\/code><\/td>Specifies the type of command in CommandText<\/code>.<\/td>Use Excel constants: xlwings.constants.xlCmdTable<\/code> (default for table names), xlwings.constants.xlCmdSql<\/code> (for SQL strings).<\/td><\/tr>
BackgroundQuery<\/code><\/td>A boolean that determines if the query runs asynchronously.<\/td>True<\/code> for background (asynchronous) query, False<\/code> (default) for foreground.<\/td><\/tr>
ImportDataAs<\/code><\/td>Defines how the returned data is placed.<\/td>A Workbook<\/code> object or xlwings.constants.xlPTTable<\/code>. Often set as the workbook itself: wb.api<\/code> for a new workbook, or a specific Range<\/code> object like sheet.range('A1').api<\/code> to import to a specific location.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Important Notes on xlwings Usage:<\/strong><\/p>\n\n\n\n
    \n
  • The method is called on the api<\/code> property because OpenDatabase<\/code> is a method of the underlying COM object (Excel’s VBA object model).<\/li>\n\n\n\n
  • You often need to import xlwings.constants<\/code> to use the Excel constants for parameters like CommandType<\/code>.<\/li>\n\n\n\n
  • The Connection<\/code> string must be correctly formatted for your specific database provider (ODBC, OLE DB). Incorrect strings are a common source of errors.<\/li>\n<\/ul>\n\n\n\n
    Code Examples<\/strong><\/p>\n\n\n\n
      \n
    1. Basic Example: Importing a Table from Microsoft Access into a New Workbook<\/strong>
      This example opens a connection to an Access database and imports an entire table named “Customers”.<\/li>\n<\/ol>\n\n\n\n
      import xlwings as xw\nimport xlwings.constants as xl\n\n# Launch Excel app\napp = xw.App(visible=True)\n# Create a new, empty workbook\nwb = app.books.add()\n\n# Define the connection string (adjust the DBQ path)\nconnection_str = \"DSN=MS Access Database;DBQ=C:\\\\Data\\\\MyDatabase.accdb;\"\n\n# Use the OpenDatabase method on the workbook's API\n# This imports the 'Customers' table into the active sheet starting at cell A1\nwb.api.OpenDatabase(\nConnection=connection_str,\nCommandText=\"Customers\",\nCommandType=xl.xlCmdTable,\nBackgroundQuery=False,\nImportDataAs=wb.sheets.active.range('A1').api\n)<\/code><\/pre>\n\n\n\n
        \n
      1. Example with SQL Query and Importing to a Specific Location<\/strong>
        This example runs a custom SQL query on a SQL Server database via an ODBC DSN and places the results into a specific range on a sheet named “Report”.<\/li>\n<\/ol>\n\n\n\n
        import xlwings as xw\nimport xlwings.constants as xl\n\n# Connect to an existing workbook\nwb = xw.Book(\"Monthly_Report.xlsx\")\nreport_sheet = wb.sheets[\"Report\"]\ntarget_range = report_sheet.range(\"B5\")\n\n# Define ODBC connection string (DSN must be pre-configured on the system)\nconnection_str = \"ODBC;DSN=MyCompanySQLServer;UID=analyst;PWD=secure_pwd;\"\n\n# Define the SQL command\nsql_query = \"\"\"\nSELECT Region, Product, SUM(Sales) AS TotalSales\nFROM SalesTransactions\nWHERE TransactionDate >= '2024-01-01'\nGROUP BY Region, Product\nORDER BY Region, TotalSales DESC\n\"\"\"\n\n# Execute the query\nwb.api.OpenDatabase(\nConnection=connection_str,\nCommandText=sql_query,\nCommandType=xl.xlCmdSql, # Explicitly state it's a SQL command\nBackgroundQuery=True, # Run in the background to not block Excel\nImportDataAs=target_range.api\n)\n\n# Optional: Wait for the background query to complete if needed\n# while report_sheet.api.QueryTables(1).Refreshing:\n# xw.time.sleep(0.1)<\/code><\/pre>\n\n\n\n
        <\/p>\n","protected":false},"excerpt":{"rendered":"
        The **OpenDatabase** member of the **Workbooks** object in the Excel object model is a method used t…<\/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-2234","post","type-post","status-publish","format-standard","hentry","category-xlwings-api-reference"],"_links":{"self":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2234","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=2234"}],"version-history":[{"count":1,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2234\/revisions"}],"predecessor-version":[{"id":3415,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2234\/revisions\/3415"}],"wp:attachment":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/media?parent=2234"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/categories?post=2234"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/tags?post=2234"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}