General Process for Operating the Excel Object Model

General Process of Operating the Excel Object Model Using Excel VBA

In Excel VBA, the Application object represents the Excel application itself and can be used directly. From Figure 4-1, we can see that the child object of the Application object is the Workbooks collection, which saves and manages all workbooks in the current Excel application. Through an index, we can obtain a specific workbook from the Workbooks collection, such as outputting the name of the first workbook in the collection in the Immediate Window. The sample file path is Samples\ch04\Excel VBA\GeneralProcess.xlsm.

Dim bk As Workbook
Set bk = Application.Workbooks(1)
Debug.Print bk.Name

Here, Application represents the Application object and can be omitted. Therefore, the above statement can also be written as:

Debug.Print Workbooks(1).Name

If you directly set an attribute of the Excel application, you cannot omit the Application object, such as setting the Excel application window to be invisible:

Application.Visible = False

Use the Add method of the Workbooks collection object to create a new workbook object:

Dim bk2 As Workbook
Set bk2 = Workbooks.Add

Use the Open method of the Workbooks collection object to open an Excel file. For example, you can use the following statement to open the Excel file test.xlsx under drive C:

Dim bk2 As Workbook
Set bk2 = Workbooks.Open("C:\test.xlsx")

After creating a new workbook, a worksheet is added to the workbook by default and saved in the Worksheets collection object. You can use the following statement to obtain the worksheet:

Dim sht As Worksheet
Set sht = bk.Worksheets(1)

Alternatively, you can directly use ActiveSheet to represent the added worksheet. The full representation of the worksheet is Application.ActiveSheet, i.e., the active worksheet of the Excel application.

You can use the Add method of the Worksheets collection object to create a new worksheet object:

Set sht = bk.Worksheets.Add

The Range property of the worksheet object returns a cell object or a cell range object. Below, set the value of cell A1 in the worksheet to 10:

sht.Range("A1").Value = 10

Read the value of cell A1 in the worksheet:

Debug.Print sht.Range("A1").Value

Save the changes to the workbook by calling the Save method of the Workbooks collection object:

bk.Save

If you want to save the file as a new file, or save a newly created workbook for the first time, you can use the SaveAs method, with parameters specifying the path and filename for saving the file:

bk.SaveAs "D:\test.xlsx"

Use the Close method of the Workbooks collection object to close the workbook:

Workbooks(1).Close

Use the Quit method to exit the application without saving any workbooks:

Application.Quit

Python Packages Related to Excel

Currently, commonly used third-party Python packages related to Excel are listed in Table 4-1. These packages have their own characteristics: some are small and flexible, some are feature-rich (comparable to the model used by VBA), some do not depend on Excel, some must depend on Excel, some have average work efficiency, and some have very high work efficiency.

Table 4-1 Commonly Used Third-Party Python Packages Related to Excel

The xlwings Package and Its Installation

Among the third-party Python packages related to Excel listed in Table 4-1, this book focuses on the xlwings package. The xlwings package is a secondary encapsulation based on the win32com package, claiming to "give wings to Excel"—it is one of the most powerful Excel Python packages currently. It encapsulates all objects of software such as Excel and Word. Therefore, from this perspective, almost anything that can be done with Excel VBA can also be done with the xlwings package.

The xlwings package has also undergone many improvements and extensions. It can easily perform type conversion and read operations with data provided by packages such as NumPy and pandas, and can conveniently write graphs drawn with Matplotlib into Excel worksheets. Using the xlwings package also enables mixed programming with VBA: you can call Python code in the VBA programming environment, or call VBA functions in Python code.

Use the following command to install the xlwings package:

pip install xlwings

General Process of Operating the Excel Object Model Using the xlwings Package

Before using the xlwings package, you need to import it. Open the Python IDLE file script window and import the xlwings package in the Python Shell window:

>>> class="text-secondary">import xlwings

Create an Excel application:

>>> app = xlwings.App()

For convenience in later use, it is common to create a short alias for the xlwings package:

>>> class="text-secondary">import xlwings class="text-secondary">as xw

Create an Excel application (App) using the following form:

>>> app = xw.App()

At this time, an Excel interface will pop up—this is the Excel application. By default, it is visible. By default, a new workbook object is created and saved in the books collection object, which can be referenced by index:

>>> bk = app.books(1)

Alternatively, get the currently active workbook:

>>> bk = app.books.active

When creating an Excel application, you can set its visibility and whether to add a workbook through parameters. Below, create a visible Excel application without adding a workbook:

>>> app = xw.App(visible=True, add_book=False)

When the value of the visible parameter is True, the Excel application is visible; when it is False, the application is invisible. When the value of the add_book parameter is True, a workbook is added to the Excel application; when it is False, no workbook is added.

Use the add method of the books collection object to create a new workbook object:

>>> bk2 = app.books.add()

Use the open method of the books collection object to open an Excel file. For example, open the Excel file test.xlsx under drive C:

>>> bk3 = app.books.open(r'C:/test.xlsx')

By default, a worksheet is added to the workbook after creating a new workbook, and it is saved in the sheets collection object. You can use the following code to obtain the added worksheet:

>>> sht = bk.sheets(1)

Use the add method of the sheets collection object to create a new worksheet object:

>>> sht2 = bk.sheets.add()

Set the value of cell A1 in the worksheet to 10:

>>> sht.range('A1').value = 10

Read the value of cell A1 in the worksheet:

>>> sht.range('A1').value
10

Use the save method of the workbook object to save the data of the specified workbook to the specified file:

>>> bk.save(r'D:\test.xlsx')

Use the close method of the workbook object to close the workbook:

>>> bk.close()

Use the quit method of the workbook object to exit the application without saving any workbooks:

>>> app.quit()