How to open, save and close Excel workbooks in VBA

2024-07-12

Preface

This section will introduce how to use VBA methods to open, save and close Excel workbooks, and will use the Open, Save, and Close methods respectively.

1. Use the Open method to open the workbook

Workbooks.Open(FileName,UpdateLinks,ReadOnly,Format,Password,WriteResPassword,Ignore-ReadOnlyResommended,Origin,Delimiter,Editable,Notify,Converter,AddToMru,Local,Corrupt-Load)
虽参数较多，但只有FileName为必填参数，表示需要打开的工作簿文件名，关于参数说明请参考下表：

parameter	illustrate
FileName	The file name of the workbook to be opened. The absolute path of the file is required.
UpdateLinks	Specifies how external references (links) in a workbook are updated. The possible values are 0 or 3, which respectively indicate that external references are not updated when the workbook is opened, and external references are updated when the workbook is opened.
ReadOnly	True to open the workbook in read-only mode
Format	When opening a text file, use this parameter to specify the delimiter. Possible values are 1 (tab), 2 (comma), 3 (space), 4 (semicolon), 5 (none), 6 (custom character, specified by the Delimiter parameter)
Password	If the workbook to be opened has an open password set, use this parameter to enter the password. If a password is set but this parameter is not filled in, a dialog box will pop up to prompt the user to enter the password
WriteResPassword	If the workbook to be opened has a password protection structure, use this parameter to give the corresponding password.
IgnoreReadOnlyRecommended	When True, it means that read-only suggestion information is not displayed.
Origin	When opening a text file, use this parameter to indicate which operating system the file comes from, so that the code page and carriage return/line feed characters can be handled correctly. Possible values are the constants xlMacintosh, xlWindows, and xlMSDOS.
Delimiter	When opening a text file, if the Format parameter is set to 6, use this parameter to specify the character used as the delimiter. If multiple characters are set, only the first character will be used by default.
Editable	If the file to be opened is an Excel 4.0 add-in, when the value of this parameter is True, the add-in is a visible window; if it is False or the parameter is ignored, the add-in is opened in hidden mode and cannot be made visible; this parameter cannot be used for versions 5.0 and above.
Notify	When the file to be opened cannot be opened in read-write mode, if the value of this parameter is True, the file can be added to the file notification list. Excel will open the file in read-only mode and poll the file notification list, and notify the user when the file is available. If the value of this parameter is False or omitted, no notification is requested, and any unavailable files cannot be opened.
Converter	The index number of the file converter that is tried first when opening a file. If the specified file converter does not recognize the file, other converters are tried first.
AddToMru	If this parameter is True, the job is not added to the list of recently used files. Default is False
Local	If the value of this parameter is True, the file is saved in the language of Excel (including control panel settings). The default value is False, which saves the file in the language of VBA.
CorruptLoad	The possible values are the constants xlNormalLoad, xlRepairFile, and xlExtractData, which respectively represent opening the workbook normally, attempting to repair the workbook, and attempting to recover the data in the workbook. The default value is xlNormalLoad

Example 1: Open an Excel workbook

Sub OpenExcelFile()
    
    Dim strFileName As String
    ' 定义文件名称
    strFileName = "D:VBAEmployee.xlsx"
    
    ' 打开工作簿
    Workbooks.Open Filename:=strFileName
    
End Sub
1
2
3
4
5
6
7
8
9
10

Example 2: Open the Excel workbook in the current path

Sub OpenExcelFile()

    Dim strFileName As String
    Dim strFilePath
    
    ' 定义文件名称
    strFileName = "Employee.xlsx"
    ' 获取当前路径
    strFilePath = ThisWorkbook.Path
    
    ' 打开工作簿
    Workbooks.Open Filename:=strFilePath & "" & strFileName
    
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2. Save and close the file

When you open an Excel workbook and operate on its contents, if you want to save and close it, you can use the Save and Close methods.

Example: After opening the Employee.xlsx file in the current path, write the current date to cell A1 of Sheet1, save and close the Employee.xlsx file. The complete code is as follows:

Sub WriteCurrentDate()
    
    ' 申明变量
    Dim strFileName As String
    Dim strFilePath
    Dim ws As Worksheet
    Dim wb As Workbook
    
    ' 定义文件名称
    strFileName = "Employee.xlsx"
    ' 获取当前路径
    strFilePath = ThisWorkbook.Path
    
    ' 打开工作簿
    Workbooks.Open Filename:=strFilePath & "" & strFileName
    
    ' 定义sheet1工作表对象以及当前打开的工作簿对象
    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    Set wb = ActiveWorkbook
    
    ' A1单元格写入当前日期
    ws.Range("A1").Value = Date
    
    ' 保存并关闭文件
    wb.Save
    wb.Close
    
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

Technology Sharing