Excel VBA Get Started (ix) Operation workbook

Although I have said earlier, it is not a good idea to manipulate a workbook in VBA, but sometimes it is unavoidable to do so. In the vast majority of cases, what we want to do is get a workbook object and use it to get the sheet object, and then process the data in the worksheet. The latter part (the action sheet) has been discussed earlier, so this chapter is about how to open, close, and describe two commonly used workbook objects in VBA. There are two ways to open a workbook in VBA: open it explicitly and open it implicitly.

1. Explicitly open

The explicit opening is the same as our manual double-click Open an Excel file, you can see the contents of the workbook, you can do various operations inside. The code and examples are as follows:

Sub workbook_operate()    ‘ 定义工作薄对象    Dim wbk As Workbook    Dim fname As String        fname = "E:/temp/test.xlsx"    ‘ 根据工作薄文件路径打开工作薄    Set wbk = Application.Workbooks.Open(Filename:=fname)    MsgBox fname & "已打开"    ‘ 关闭工作薄    wbk.CloseEnd Sub

The results are as follows:

You can then workbook get to the worksheet based on this object (the WBK variable in the example):

Set parameter_sht = wbk.Worksheets("Parameter")

Or get some properties for this workbook:

wbk.Name

Finally, you can use Close the method to close the workbook:

wbk.Close

2. Implicit Open

With implicit opening, the workbook is invisible to the front end and can be seen only in the VBE environment (the Project window). The sample code and results are as follows:

Sub workbook_operate()    ‘ 定义工作薄对象    Dim wbk As Workbook    Dim fname As String        fname = "E:/temp/test.xlsx"    ‘ 根据工作薄文件路径获取工作薄对象    Set wbk = GetObject(fname)    Debug.Print wbk.NameEnd Sub

Unlike explicit opening, this is a method that is used GetObject . The results are as follows:

You can see that a new project has been loaded in the Project window (left) of the VBE. However, it is not possible to see an Excel file opened, this has been implicitly opened by VBA file, can only use VBA to operate, in use and the explicit open after the workbook object is no different, such as getting the sheet object, get its name, close it, etc., are the same. This is not an example.

3. Close

Whether you open it explicitly or implicitly, if there is a change in the newly opened workbook, use workbook. Close method, there will be a popup window to indicate whether to save changes, as we normally use:

Or there are some formulas in the workbook that are opened, and the formula results change when you open the workbook (which is also equivalent to an update), which will appear when you close the book. If you can determine if changes are required, you can add the appropriate parameters when saving SaveChanges :

wbk.Close SaveChanges:=False

SaveChangesEquals is False not saved, equals is True saved

4. ThisWorkbook and ActiveWorkbook

ThisWorkbookObject is Application a special object under the VBA top-level object that represents the current (VBA code) workbook.

ActiveWorkbookObject is also Application a special object under the VBA top-level object, which represents the currently active workbook object.

Just as we use QQ chat, we can not send messages alone to two different QQ friends, only one chat window is activated. As with Excel, the currently active workbook is the ActiveWorkbook object (similarly, the Activeworksheet object).

In some cases, it ThisWorkbook ActiveWorkbook is equivalent, but in some cases it is not. such as the following code:

Sub workbook_operate()    ‘ 定义工作薄对象    Dim wbk As Workbook    Dim fname As String        fname = "E:/temp/ActiveMe.xlsx"    ‘ 根据工作薄文件路径获取工作薄对象    Set wbk = Workbooks.Open(fname)      Debug.Print ThisWorkbook.Name    Debug.Print ActiveWorkbook.NameEnd Sub

Operation Result:

Because the newly opened workbook will be activated (just like opening another Excel file manually), So the newly opened activeme.xlsx is ActiveWorkbook, and the current code's workbook (TEST.XLSM) is ThisWorkbook. ThisWorkbook and ActiveWorkbook are the same object if there is only one work comforter open or if the workbook in which the current code is active.

Other articles in this tutorial series

Getting Started with Excel VBA (0)
Getting Started with Excel VBA (a) data type
Getting Started with Excel VBA (ii) Arrays and dictionaries
Getting Started with Excel VBA (iii) Process Control 1-Conditional selection
Getting Started with Excel VBA (iv) Process Control 2-loop control
Excel VBA Primer (v) Excel object manipulation
Getting Started with Excel VBA (vi) Procedures and functions
Getting Started with Excel VBA (vii) Comments, macro buttons, and error handling
Excel VBA Primer (eight) cell border
Excel VBA Get Started (ix) Operation workbook

Excel VBA Get Started (ix) Operation workbook