Excel-VBA file operation 1

In our daily use of Excel, we not only use the data of the current Excel file, but also frequently need to access other data files. These data files may be Excel files, text files, or database files. Some people often ask how to operate these data files in the VBA code? This article systematically introduces how to use VBA to operate data files in Excel.

This document describes four common methods:

1. Use Excel objects to process files;

2. Use VBA file processing statements to process files;

3. Use the FileSystemObject object to process files;

4. Use API functions to process files.

Of course, you can also use ADO + SQL to operate database files. However, the Forum has already described such methods in detail, so this article will not repeat them.

1. Use Excel objects to process files

Using the built-in methods of Excel objects to operate files is the most convenient and simple.

We mainly use workbooks collections and workbook objects to operate files.

1. Open an Excel file

You can use workbooks. Open to open an Excel Workbook.

Workbooks. Open (filename, updatelinks, readonly, format, password, writerespassword, ignorereadonlyrecommended, origin, delimiter, editable, y, converter, addtomru, local, corruptload)

Filename is a required parameter, indicating the name of the workbook to be opened. If no path is specified, it indicates the current path. The other 14 are optional parameters, except Password parameters, which are rarely used. For specific meanings, see the help of VBA.

Example:

Workbooks. Open "F: test.xls"

Open the test.xls file on the fdisk.

2. Open a text file

You can use the open method to open text files, but we recommend that you use the opentext method. This method loads a text file and processes it as a workbook containing a single worksheet in a separate manner, and then puts the processed text file data in this worksheet. The complete syntax is as follows: