Excel-VBA file operation methods (1)

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:
Workbooks. opentext (filename, origin, startrow, ype, textqualifier, delimiter, tab, semicolon, comma, space, other, otherchar, fieldinfo, textvisuallayout, delimiter, thousandsseparator, delimiter, local)

For more information about the meanings of the preceding parameters, see the help of VBA. In actual programming, it is generally not necessary to process these complex parameters. You can use a recording macro to obtain the VBA code for opening a text file. The specific method is to select "file-open", and then select Open Text File, the text import Wizard will appear, step by step, until the text is opened, stop recording.
The following is the code for recording macros:
Sub macro1 ()
'
'Macro1 macro
'Macro is recorded by MC system. Time:
'

'
Workbooks. opentext filename: = "F:/callwindowproc.txt", origin: = xlwindows ,_
Startrow: = 1, datatype: = xldelimited, textqualifier: = xldoublequote ,_
Consecutivedelimiter: = false, Tab: = true, semicolon: = false, comma: = false _
, Space: = false, other: = false, fieldinfo: = array (1, 1 ),_
Trailingminusnumbers: = true
End sub

In actual programming, you only need to make the corresponding changes.

3. Open other files

Excel objects can also be used to open XML files and some database (such as Access) files, which correspond to XML files and require Versions later than excel2003.

The syntax of the openxml method is as follows:
Workbooks. openxml (filename, stylesheets, loadoption)
Filename string type, required. The name of the file to open.

Stylesheets variant type. Optional. An array of single values or values, used to specify the XSL conversion (XSLT) style table processing instructions to be applied.

Loadoption variant type, conversion. Specifies how to open an XML data file in Excel. Can be one of the xlxmlloadoption constants.

Xlxmlloadoption can be one of the following xlxmlloadoption constants:
Xlxmlloadimporttolist puts the content of the XML data file in the XML list.
Xlxmlloadmapxml shows the XML data file architecture in the "XML structure" task pane.
Xlxmlloadopenxml open the XML data file. The file content is expanded.
Xlxmlloadpromptuser prompts you to select the file opening method.

Example
The following code opens the XML data file "MERs. xml" and displays the content of this file in the XML list.

Sub useopenxml ()
Application. workbooks. openxml _
Filename: = "customers. xml ",_
Loadoption: = xlxmlloadimporttolist
End sub

The opendatabase method syntax is as follows:
Workbooks. opendatabase (filename, commandtext, commandtype, backgroundquery, importdataas)

Filename string type, required. The connection string.

Commandtext variant type. Optional. Query command text.

Commandtype variant type. Optional. Query command type. The following command types are available: Default, SQL, and table.

(Optional) Type of backgroundquery variant. The background of the query.

Optional. importdataas variant type. Determine the Query format.

Example
In this example, the "northwind. mdb" file is opened in Excel.

Sub opendatabase ()

Workbooks. opendatabase filename: = "C:/northwind. mdb"

End sub

4. Save the file

The Save or saveas method of the workbook object is used to save files.
The Save method is easy to use and the syntax is
Expression. Save. expression is a workbook object.
Example: activeworkbook. Save
Saves the current active workbook.

If you want to save the workbook for the first time or save it as a file, use the saveas method to specify the file name.
Its syntax is:
Expression. saveas (filename, fileformat, password, writerespassword, readonlyrecommended, createbackup, accessmode, conflictresolution, addtomru, textcodepage, textvisuallayout, local)

For more information about the parameters, see the VBA help.
Example
In this example, you are prompted to enter a file name and save the workbook.

Set newbook = workbooks. Add
Do
Fname = application. getsaveasfilename
Loop until fname <> false
Newbook. saveas filename: = fname

Application. getsaveasfilename is used to call up the standard "Save as" dialog box, get the user file name, but do not really save any files, and then save the file using code. In addition, application. getopenfilename can call up the standard "open" dialog box.

5. close the file

To close a file, you can use the close method of the workbooks collection or workbook object. The former is to close all opened workbooks, and the latter is to close specific workbooks.
The close method syntax of the workbook object is:
Expression. Close (savechanges, filename, routeworkbook)

The savechanges parameter indicates whether to save the changes. For many operations that do not need to be changed, you can set it to false to avoid the pop-up dialog box for saving the change prompt.
Optional. Save the changes in this file name.
Routeworkbook is optional. If the specified workbook does not need to be transferred to the next recipient (no transfer list or transferred), ignore this parameter.

Example
In this example, close book1.xls and discard all changes to this workbook.

Workbooks ("book1.xls"). Close savechanges: = false

In this example, close all opened workbooks. If an opened workbook changes, Microsoft Excel displays a dialog box asking whether to save the changes and corresponding prompts.

Workbooks. Close

6. Integrated instances

Assume that the fdisk has an Excel file test.xls, and now there is another Excel file to upload test.xls data. Let's look at how to operate it using the VBA code. The Code is as follows:
Public sub test ()
Application. screenupdating = false
Workbooks. Open "F:/test.xls"
Thisworkbook. Sheets (1). Range ("B1") = activeworkbook. Sheets (1). Range ("A2 ")
Activeworkbook. Close
Application. screenupdating = true
End sub
First, shut down the screen and click "new" to prevent test.xls from being seen when it is opened (sometimes you can see it ). .
When the workbook to be opened is uncertain, you can call the open dialog box to select the workbook.
You can change it to the following:
Public sub test ()
Application. screenupdating = false
Dim filename as string
Filename = application. getopenfilename
Workbooks. Open filename
Thisworkbook. Sheets (1). Range ("B1") = activeworkbook. Sheets (1). Range ("A2 ")
Activeworkbook. Close
Application. screenupdating = true
End sub

7. Summary

Using Excel objects for file operations is the simplest and most convenient, and is suitable for beginners. For the Excel file format, this method is preferred if we only read the content in the table. The second method is more convenient for text file operations. To convert text into a table, this method is also suitable.

 

Ii. Use VBA file processing statements to process files

VBA contains many statements and functions used for file operations, which can meet the file operation requirements in most cases. Next, we will give a one-to-one introduction for the purpose of the operation.

(1) File Processing

1. Name statement

Syntax: Name oldpathname as newpathname

Function: rename a file, directory, or folder and move a file.

Note: Using name on an opened file will produce an error. When performing file operations, be sure to handle errors.

Example:
On Error resume next 'error handling
Name "F:/test.xls" as "F:/test123.xls" 'rename
Name "F:/test.xls" as "F:/dll/test.xls" 'Move the file
Name "F:/test.xls" as "D:/test123.xls" 'Move across drives and rename files

Note: Name cannot move a directory or folder.

2. filecopy statement

Syntax: filecopy source, destination

Function: copy an object.

Note: If you use the filecopy statement for an opened file, an error occurs.

Example:
Filecopy "F:/test.xls", "E:/test.xls" 'Copy test.xls from fdisk to edisk

3. Kill statement

Syntax: Kill pathname

Function: delete a file from a disk.

Note: Kill supports multi-character (*) and single-character (?) To specify multiple files. If you use kill to delete an opened file, an error occurs.

Example:
Kill "F:/test.xls" 'deletes the test.xls file of the drive.
Kill "F:/*. xls" 'Delete All XLS files on drive F

4. getattr Function

Syntax: getattr (pathname)

Function: obtains the attributes of a file, directory, or folder. Returns an integer.

Return Value

The value returned by getattr is the sum of the following property values:

Constant Value description
Vbnormal 0 normal
Vbreadonly 1 read-only
Vbhidden 2 Hide
Vbsystem 4 system files
Vbdirectory 16 directory or folder
Vbarchive 32 archive file
The file name specified by vbalias 64 is an alias. Only available in Macintosh.

Note: To determine whether an attribute is set, use the and operator to compare the getattr function with the attribute value you want to know. If the result is not zero, this attribute value is set.

Example:
Debug. Print getattr ("F:/test.txt") '. If the file is an archive file, the value is 32 in the displayed window.
Debug. Print getattr ("F:/test.txt") '. After removing the property-advanced-archive file check, the value is 0.

To determine whether a file is read-only, use the following method:
Debug. Print getattr ("F:/test.txt") and vbreadonly
If the value is not zero, it indicates read-only.

5. setattr statement

Syntax: setattr pathname, attributes

Function: Set attributes for a file.

Note: If you want to set properties for an opened file, a runtime error will occur.

Example:
Setattr "F:/test.txt", vbhidden 'sets the hidden attribute.
Setattr "F:/test.txt", vbhidden + vbreadonly 'is set to hide and read-only.

6. filelen Function

Syntax: filelen (pathname)

Function: obtains the length of a file, in bytes.

Note: When the filelen function is called, you do not need to open the file. If the specified file has been opened, the returned value is the size of the file before it is opened.

7. filedatetime Function

Syntax: filedatetime (pathname)

Function: gets the date and time after a file is created or last modified.

Example:
Debug. Print filedatetime ("F:/test.xls") 'in the displayed window,-3-29 19:28:27

(2) Directory Processing

1. curdir Function

Syntax: curdir [(drive)]

Function: returns the current path.

Description: The drive parameter is optional. It specifies an existing drive. If the drive is not specified or the drive is a zero-length string (""), curdir returns the path of the current drive.

Example:
Debug. Print curdir 'returns "C:/Documents and Settings/YC/My Documents ents ".
Debug. Print curdir ("C") 'returns "C:/Documents and Settings/YC/my documents ".
Debug. Print curdir ("D") 'returns "D :/".

2. chdir statement

Syntax: chdir path

Function: change the current directory or folder.

Note: The chdir statement changes the default directory location, but does not change the default drive location. The default drive is generally C.

Example:

Chdir "D:/Temp"
Debug. Print curdir 'returns "C:/Documents and Settings/YC/My Documents ents ".
Debug. Print curdir ("D") 'returns "D:/Temp ".
Compared with the preceding example, the current directory of disk D has changed to "D:/Temp", but the default drive is C.

3. chdrive statement

Syntax: chdrive drive

Function: change the current drive.

Note: If a zero-length string ("") is used, the current drive will not change. If the drive parameter contains multiple characters, chdrive uses only the first letter.

Example:
Chdrive "D"
Chdir "D:/Temp"
Debug. Print curdir 'returns "D:/Temp ".
Debug. Print curdir ("D") 'returns "D:/Temp ".

Compared with the preceding example, "d:/Temp" is returned using curdir, and the current drive has changed to D.

4. dir Function

Syntax: dir [(pathname [, attributes])]
Both parameters are optional. Attributes indicates file attributes.
Function: returns a file name, directory name, or folder name that must match the specified mode or file attribute or disk volume tag.

Note: when calling the Dir function for the first time, you must specify the pathname. Otherwise, an error occurs. If file attributes are also specified, pathname must be included.

Dir returns the first file name that matches the pathname. If you want to get another file name that matches the pathname, call dir again and do not use the parameter. If no matching file exists, Dir returns a zero-length string (""). Once the return value is a zero-length string and you want to call dir again, you must specify the pathname. Otherwise, an error occurs. You do not need to access all file names that match the current pathname, but you can change them to a new pathname. However, the Dir function cannot be called recursively. When you call dir using the vbdirectory attribute, the subdirectory cannot be returned consecutively.

Example:
Debug. Print Dir ("F:/test.xls") 'returns "test.xls"
Debug. Print Dir ("F:/*. xls") 'returns the first file name found by the condition.
Debug. Print Dir ("F:/*. txt", vbreadonly) 'returns the first read-only TXT file.

The following process displays all directories under the C root directory.
Sub dirc ()

Mypath = "C :/"
Myname = Dir (mypath, vbdirectory.
Do While myname <> "" 'starts the loop.
'Skip the current directory and the upper directory.
If myname <> "." And myname <> "..." then
'Use bitwise comparison to determine that myname represents a directory.
If (getattr (mypath & myname) and vbdirectory) = vbdirectory then
Debug. Print myname' if it is a directory, display its name.
End if
End if
Myname = dir' find the next directory.
Loop

End sub

The following procedure uses Recursion to find all files in directories and subdirectories.

Public sub findfile (mpath as string, optional sfile as string = "")
On Error resume next
Dim s as string, sdir () as string
Dim I as long, D as long

If right (mpath, 1) <> "/" then
Mpath = mpath &"/"
End if
'Look up the files in the directory
S = Dir (mpath & sfile, vbarchive + vbdirectory + vbhidden + vbnormal + vbreadonly + vbsystem)
Do While S <> ""
Debug. Print mpath & S
S = dir
Loop
'Look up the subdirectories in the directory.
S = Dir (mpath, vbarchive + vbdirectory + vbhidden + vbnormal + vbreadonly + vbsystem)
Do While S <> ""
If S <> "." And S <> "..." then
If (getattr (mpath & S) and vbdirectory) = vbdirectory then
D = d + 1
Redim preserve sdir (d)
Sdir (d) = mpath & S
End if
End if
S = dir
Loop

'Start Recursion
For I = 1 to d
Findfile sdir (d )&"/"
Next

End sub

5. mkdir statement

Syntax: mkdir path

Function: Create a new directory or folder.

Note: A path can contain a drive. If no drive is specified, mkdir creates a new directory or folder on the current drive.

Example:
Mkdir "mydir" 'creates a new directory or folder in the current directory.

6. rmdir statement

Syntax: rmdir path

Function: delete an existing directory or folder.

Note: If you want to use rmdir to delete a directory or folder containing files, an error will occur. Before trying to delete a directory or folder, use the kill statement to delete all files.

Example:
Rmdir "mydir" 'deletes mydir.