Excel-VBA file operation method 3

3. Use the FileSystemObject object to process files

The FileSystemObject object model is provided by Microsoft to access computer file systems with a large number of attributes, methods, and events. It uses the object-oriented "object. Method" syntax to process folders and files, which is very convenient to use (requires Versions later than Office 2000 ). FileSystemObject is not part of VBA. It is provided as a COM component. Therefore, you must first create a FileSystemObject object.

The FileSystemObject object model contains the following objects and sets:

· The main object of FileSystemObject, which contains the methods and attributes used to create, delete, and obtain relevant information, as well as to operate the drive, folder, and file.

· The drive object contains the methods and attributes used to obtain information about the drive connected to the system, such as how much space is available. A drive is not necessarily a hard disk, it can also be a CD-Rom, a USB flash disk, or a hard disk that is logically connected through the network (such as a server network hard disk shared by a department in the company ).

· Drives collection, which provides a list of drives that are physically or logically connected to the system. The drives collection contains all the drives, regardless of the type.

· File object, including methods and attributes used to create, delete, or move files.

· Files set, which provides a list of all files contained in the folder.

· Folder object, including the methods and attributes used to create, delete, or move folders.

· Folders set, which provides a list of all folders contained in the folder.

· Textstream object, used to read and write text files.

(1) Preparations

To use the FileSystemObject object, you must first create it. To create a FileSystemObject object, use the creatobject function. The Createobject function is used to create and return a reference to ActiveX objects.

Syntax: Createobject (class, [servername])
Class is the name and class of the application to be created.
Servername: name of the network server on which the object is to be created. (If you want to create an object on a remote computer)

The class parameter uses the appname. objecttype syntax, which includes the following parts:

The appname is required. The application name of the object is provided.
Objecttype is required. The type or class of the object to be created.

Therefore, we use the following code to create a FileSystemObject object:

Dim FSO as object
Set FSO = Createobject ("scripting. FileSystemObject ")

Scripting is the name of the Type Library, and FileSystemObject is the name of the object to be created.

Similarly, we can create a dictionary object as follows:
Dim d
Set d = Createobject ("scripting. Dictionary ")

(2) FileSystemObject object Method

Some functions of the FileSystemObject object model are repeated. For example, you can use the cpoyfile method of the FileSystemObject object or copy method of the file object to copy files. The following describes the FileSystemObject object method.

1. getdrive Method

Syntax: object. getdrive drivespec

The drivespec parameter can be a drive character (c), a drive character plus a colon (C :), a drive character plus a colon and a path separator (C :/) or any network sharing instructions (// computer2/share1 ).

Purpose: return a drive object corresponding to the drive in the specified path.

Example:
Dim d
Set d = FSO. getdrive ("D:") 'variable D represents the drive D object
If drivespec does not conform to any accepted form or does not exist, an error occurs.

Note: In this example, FSO is the created FileSystemObject object.

2. getdrivename Method

Syntax: object. getdrivename (PATH)

Purpose: return a string containing the name of the drive in the specified path.

Example:
Debug. Print FSO. getdrivename ("C:/test.txt") 'displayed in the "C :"

3. getextensionname Method

Syntax: object. getextensionname (PATH)

Purpose: return a string containing the last part extension in the path.

Example:
Debug. Print FSO. getextensionname ("C:/test.txt") 'the current window displays "TXT"

4. getbasename Method

Syntax: object. getbasename (PATH)

Purpose: return a string containing the basic name of the last part of the path (removing any file extension.

Example:
Debug. Print FSO. getbasename ("C:/ABC/test.txt") 'the current window displays "test"

5. getabsolutepathname Method

Syntax: object. getabsolutepathname (pathspec)

Purpose: return a complete and clear path from the provided path description.

Example:
If 'pattern' is a null string, the current path is returned. Assume that the current path is C:/Documents and Settings/YC/My Documents ENTs.

Debug. Print fs. getabsolutepathname ("") 'displays C:/Documents and Settings/YC/My Documents ents

Debug. Print fs. getabsolutepathname ("C: ..") 'displays C:/statements and settings/YC, that is, the upper directory

Debug. Print fs. getabsolutepathname ("ABC") 'displays C:/Documents and Settings/YC/My Documents ents/ABC

Debug. Print fs. getabsolutepathname ("C:/test.txt") 'display C:/test.txt

6. GetFile method

Syntax: object. GetFile (filespec)

Purpose: return a file object corresponding to the file in the specified path.

Example:
Dim F
Set F = FSO. GetFile ("C:/test.txt") 'variable frepresents the test.txt object.
Note: If the specified file does not exist, an error occurs.

7. getfilename Method

Syntax: object. getfilename (pathspec)

Purpose: return the last part of the specified path, which is not part of the drive description.

Example:
Debug. Print FSO. getfilename ("C:/ABC/test.txt") 'the current window displays "test.txt"

8. getfolder Method

Syntax: object. getfolder (folderspec)

Purpose: return a folder object corresponding to the folder in the specified path.

Example:
Dim FD
Set FD = FSO. getfolder ("C:/Windows") 'variable f Represents the windows object in the folder.
Note: If the specified folder does not exist, an error occurs.

9. getspecialfolder Method

Syntax: object. getspecialfolder (folderspec)

Purpose: return the specified special folder.

Note:
The folderspec parameter can be any of the following values:

Windowsfolder 0 Windows folder, which contains files installed by the Windows operating system.
Systemfolder 1 system folder, including libraries, fonts, and device drivers.
Temporaryfolder 2 temp folder, used to store temporary files. Its path is in the TMP environment variable.

10. getparentfoldername Method

Syntax: object. getparentfoldername (PATH)

Purpose: return a string containing the name of the parent folder of the last part of the specified path.

Example:
Debug. Print FSO. getparentfoldername ("C:/tmp/test.txt") 'displays "C:/tmp"

11. gettempname Method

Syntax: object. gettempname

Purpose: return the name of a randomly generated temporary file or folder, which is useful for executing operations that require temporary files or folders.

Note: The gettempname method does not generate a file. It only provides a temporary file name, which can be used by createtextfile to create a file.

Example:
Debug. Print FSO. gettempname 'displays "radb0208.tmp", which changes every time.

12. buildpath Method

Syntax: object. buildpath (path, name)

Purpose: append a name to an existing path.

Example:
Debug. Print FSO. buildpath ("C:/tmp", "ABC") 'displays "C:/tmp/ABC"

13. createfolder Method

Syntax: object. createfolder (Foldername)

Purpose: Create a folder.

Note: If the specified folder already exists, an error occurs.

Example:
FSO. createfolder ("C:/myfolder") 'create a myfolder folder on drive C

14. copyfolder Method

Syntax: object. copyfolder source, destination [, overwrite]
Source is required. Description of one or more string folders in the copied folder, which can contain wildcards.
Destination is required. Specifies the strings of the copied folder and subfolders in the source. Wildcards are not allowed.
Overwrite is optional. Boolean value, which indicates whether existing folders are overwritten. If true, the file is overwritten. If it is false, the file is not overwritten. The default value is true.

Purpose: copy a folder to another place.

Note:
① Wildcards can only be used for the last path part of the Source parameter.

For example: FSO. copyfolder "C:/mydocuments/Letters/*", "C:/tempfolder.
But it cannot be like this: FSO. copyfolder "C:/mydocuments/*", "C:/tempfolder /"

② If the source contains a wildcard or destination ending with a path separator (/), the destination is considered to be an existing folder, where the matched folders and subfolders are copied. Otherwise, destination is the name of the folder to be created.

For example: FSO. copyfolder "C:/tmp", "F:/ABC /"
If the drive F does not have the ABC folder, an error will occur. If the folder already exists, you can see the TMP folder in the ABC folder.
For example: FSO. copyfolder "C:/tmp", "F:/ABC"
If ABC does not exist, the ABC folder will be created and the content in the TMP folder will be copied to the ABC folder instead of the TMP folder. Only when ABC is an existing folder, to copy the entire TMP folder to the ABC folder.

③ If destination is an existing file, an error occurs.

④ If destination is a directory, it will try to copy the folder and all its contents. If a file contained in the source already exists in destination, an error occurs when overwrite is false. Otherwise, it will try to overwrite the file.

⑤ If destination is a read-only directory, an error occurs when you try to copy an existing read-only file to this directory and overwrite is false.

⑥ If the source does not exist or the wildcard used cannot match any folder, an error is also reported.

7. The copyfolder method stops on the first error it encountered. The previous operations will not disappear.

15. movefolder Method

Syntax: object. movefolder source, destination
The parameter is the same as the first two parameters of copyfolder.

Purpose: move one or more folders from one place to another.

Note:
① This method allows folders to be moved between volumes only when supported by the operating system. Windows is not allowed. It is not allowed to move the folder on drive C to drive D.

② If the source contains a wildcard or destination ending with a path separator (/), it is assumed that destination specifies an existing folder and moves the matched file in this folder. Otherwise, destination is considered as the name of the target folder to be created. This is the same as copyfolder.

③ If destination is an existing file, an error occurs.

④ If destination is a directory, an error occurs.
For example:
FSO. movefolder "C:/tmp", "C:" 'error.

⑤ If the source does not exist or the wildcard used cannot match any folder, an error also occurs.

⑥ The movefolder method stops on the first error it encountered. Do not try to make any changes before an error occurs.

16. deletefolder Method

Syntax: object. deletefolder folderspec [, force]

Folderspec is required. The name of the folder to be deleted. Folderspec can contain wildcards in the final path part.
Force is optional. Boolean value. to delete a folder with read-only attribute settings, the value is true. If the value is false (default), the folder with read-only attribute settings cannot be deleted.

Purpose: delete a specified folder and its content.

NOTE: If no matching folder is found, an error occurs. The deletefolder method stops on the first error it encountered. Do not try to roll back or undo any changes made before the error occurs.

Example:
FSO. deletefolder ("C:/tmp ")

17. folderexists Method

Syntax: object. folderexists (folderspec)

Purpose: if the specified folder exists, true is returned. If the specified folder does not exist, false is returned.

18. driveexists Method

Syntax: object. driveexists (drivespec)

Purpose: if the specified drive exists, true is returned. If the drive does not exist, false is returned.

19. fileexists Method

Syntax: object. fileexists (filespec)

Purpose: if the specified object exists, true is returned. If the object does not exist, false is returned.

20. createtextfile Method

Syntax: object. createtextfile (filename [, overwrite [, Unicode])

Overwrite is optional. Boolean value, indicating whether an existing file can be overwritten. If it can be overwritten, its value is true, and its value is false, it cannot be overwritten. If it is omitted, existing files cannot be overwritten.

Unicode is optional. Boolean value, indicating whether the file is created as a Unicode file or as an ASCII file. If it is created as a Unicode file, its value is true. If it is created as an ASCII file, its value is false. If omitted, it is considered as an ASCII file.

Purpose: Create a specified file name and return a textstream object for reading and writing the file.

Example:
Dim F
Set F = FSO. createtextfile ("C:/testfile.txt", true)

21. opentextfile Method

Syntax: object. opentextfile (filename [, iomode [, create [, format])

Purpose: open a specified file and return a textstream object. This object can be used to read, write, and append files.

Note:
· The iomode parameter can be set to any of the following values:

Forreading 1 open a read-only file and cannot write the file.
Forwriting 2 open a file for write operations. If a file with the same name as this file already exists, the previous content will be overwritten.
Forappending 8 open a file and write it to the end of the file.

Note: There is no forwriting In the VBA help, but there are actually some. The VBA help is also incorrect. In addition, these constants must be declared before use, or directly use numerical values.

· Create (optional) indicates whether to create a new file if the specified filename does not exist. If a new file is created, its value is true. If you do not create a file, its value is false. The default value is false.

· The format parameter can be set to any of the following values:

Tristateusedefault-2 use the system default to open the file.
Tristatetrue-1 open a file in unicode format.
Tristatefalse 0 open the file in ASCII format.

Example:
Dim F
Set F = FSO. opentextfile ("C:/testfile.txt", 2, true)
Or:
Const forwriting = 2
Set F = FSO. opentextfile ("C:/testfile.txt", forwriting, true)
The two functions are the same. One declares a constant and the other directly uses a value. The file testfile.txt is created on the C drive (if it does not exist), or opened in write mode (if it exists ).

22. copyfile Method

Syntax: object. copyfile source, destination [, overwrite]

Purpose: copy one or more files from one place to another.

Note: note that copyfolder is similar to copyfolder.

Example:
FSO. copyfile "C:/testfile.txt", "F:/ABC/" 'If ABC does not exist, an error occurs.
FSO. copyfile "C:/testfile.txt", "F:/ABC" 'If abcdoes not exist, copy testfile.txt to the f disk file name to ABC. If ABC exists, an error occurs because it is a directory.

23. movefile Method

Syntax: object. movefile source, destination

Purpose: move one or more files from one place to another.

Note: It is similar to movefolder.

24. deletefile Method

Syntax: object. deletefile filespec [, force]

Purpose: delete a specified object.

Note: force is optional. If you want to delete a file with read-only attribute settings, the value is true. If the value is false (default), files with read-only attribute settings cannot be deleted.

(3) process the drive

You can use the drive object to obtain information about various drives. The drive object attributes include:

Total size of the totalsize drive, in bytes.
The available space of the availablespace attribute drive, in bytes.
The remaining space of the freespace drive is the same as that of the availablespace drive.
For computer systems that support quotas, the two may be different.
Driveletter attribute drive letters, that is, drive letters.
The type of the drivetype attribute drive. Such as "removable", "fixed", "network", "CD-ROM", "RAM disk"
The serial number of the drive.
The file system type used by the filesystem attribute drive. Such as fat, FAT32, NTFS, and CDFs.
Whether the isready attribute drive is available.
The Network Share Name of the drive.
Volume name of the volumename attribute drive.
Path: the path of the drive. The path of the C drive is C:, rather than C :/.
The rootfolder attribute specifies the root folder of the drive. The root folder of drive C is C :/.

These attributes are easy to use. You can simply use "object. Attribute. Before use, you must first use getdrive to obtain a drive object. Note that you cannot create a drive object. The following is an example:

Sub showfreespace (drvpath)
Dim FS, d, S
Set FS = Createobject ("scripting. FileSystemObject ")
Set d = FS. getdrive (FS. getdrivename (drvpath ))
S = "Drive" & ucase (drvpath )&"-"
S = S & D. volumename & vbcrlf
S = S & "Free Space:" & formatnumber (D. freespace/1024, 0)
S = S & "Kbytes"
Msgbox s
End sub

Call showfreespace ("C:") to obtain the volume name and available space of disk C.

(4) process folders

1. Get Folder Information

You can use the folder object to obtain information about folders. The attributes of the folder object include:

Attribute of the attributes attribute folder. Can be any or any logical combination of the following values:
Normal 0 is a normal file. No property is set.
Readonly 1 read-only file. The attribute is read/write.
Hidden 2 hides the file. The attribute is read/write.
System 4 system file. The attribute is read/write.
Volume 8 disk drive volume label. The attribute is read-only.
Directory 16 folder or directory. The attribute is read-only.
Archive 32 is a file that has been changed since the last backup. The attribute is read/write.
Alias 64 link or shortcut. The attribute is read-only.
Compressed 128. The attribute is read-only.
Name attribute folder name.
The short name used by the program whose shortname attribute is earlier than the 8.3 naming convention.
Type attribute folder type.
A set of files composed of all file objects. These file objects are included in the specified folder.
── Include the files with hidden and system file attributes.
The drive letter of the drive property folder.
Whether the isrootfolder attribute folder is the root folder.
The parent folder object of the parentfolder attribute folder.
Subfolders.
Path.
The short path used by the program with the earlier 8.3 file naming convention in the export path attribute.
The size of the folder, in bytes.
The date and time when the datecreated attribute folder was created.
The date and time when the last folder was modified in the datelastmodified attribute.
The date and time when the last folder was accessed by the datelastaccessed attribute.

The usage of attributes is the same as that of the drive object. You can use getfolder to obtain a folder object, or use the createfolder method of the FileSystemObject object to create a folder object.

2. Folder object Method

(1) copy method

Syntax: object. Copy destination [, overwrite]

Purpose: copy a specified folder from one place to another.

Note: The function of the copy method is the same as that of the copyfolder method of the FileSystemObject object. The difference is that the latter can copy Multiple folders at a time.

(2) Move Method

Syntax: object. Move destination

Purpose: Move a specified folder from one place to another.

Note: The function of the move method is the same as that of FileSystemObject. movefolder. The difference is that the latter can move Multiple folders at a time.

(3) Delete Method

Syntax: object. Delete force

Purpose: delete a specified folder.

Note: The delete method works the same as FileSystemObject. deletefolder.

(4) createtextfile Method

Syntax: object. createtextfile (filename [, overwrite [, Unicode])

Purpose: it is the same as the createtextfile method of the FileSystemObject object.

Example:
Set FD = FS. getfolder ("C:/tmp ")
Set F = FD. createtextfile ("testfile.txt", true)
You can create the testfile.txt file under the tmpfolder on the cdisk.

(5) process files

1. Get File Information

You can use the file object to obtain information about the file. The attributes of the file object are identical with those of the folder object, except that the files, isrootfolder, and subfolders attributes are missing. It is not listed here.

2. File object Method

(1) copy method

(2) Move Method

(3) Delete Method

The preceding three methods are similar to those of folder, and the syntax is the same. They can also be replaced by the corresponding FileSystemObject object method.

(4) openastextstream Method

Syntax: object. openastextstream ([iomode, [format])

Purpose: open a specified file and return a textstream object. This object can be used to read, write, and append the file.

Note: This method is the same as the opentextfile method of the FileSystemObject object. The parameters are also consistent.

(6) process text files

1. Open or create a text file

To open an existing text file, you can use the opentextfile method of the FileSystemObject object or the openastextstream method of the file object.
To create a file, you can use the creattextfile method of the FileSystemObject object or set the iomode parameter to forwriting = 2 in the opentextfile method and the create parameter to true.
For example:
Set F = FSO. opentextfile ("C:/test1.xls", 2, true) 'is automatically created if test1.xls does not exist.

2. Read files

After opening the file, a textstream object is returned. We can use the attributes and methods of the textstream object to read and write the file.

First, let's look at the attributes of the textstream object.

· Whether the atendofline attribute file pointer is in front of the line mark
· Whether the atendofstream attribute file pointer is at the end of the textstream File
· Column attribute column number of the current character position in the textstream File
· Current row number in the textstream file of the line attribute

There are three methods to read files using textstream objects.

· Read Method

Syntax: object. Read (characters)

Function: reads a specified number of characters from a textstream file and returns the string.

Example:
Sub du ()
Dim FSO, A, retstring
Const forreading = 1
Set FSO = Createobject ("scripting. FileSystemObject ")
Set a = FSO. opentextfile ("C:/testfile.txt", forreading, false)
Do while a. atendofline <> true 'indicates whether the row ends
Retstring = retstring & A. Read (1) 'read a character
Loop
A. Close
Debug. Print retstring '. You can see the characters in the first line.
End sub

· Readline Method

Syntax: object. Readline

Function: Read a full line (to line break but not line break) from a textstream file and return the string.

Example:
Sub du_line ()
Dim FSO, A, retstring
Const forreading = 1
Set FSO = Createobject ("scripting. FileSystemObject ")
Set a = FSO. opentextfile ("C:/testfile.txt", forreading, false)
Do while a. atendofstream <> true' whether it is at the end of the textstream File
Retstring = A. readline' reads a row
Debug. Print retstring 'is displayed in the current window
Loop
A. Close
End sub

· Readall Method

Syntax: object. readall

Function: Read the entire textstream file and return the string.

Note: For large files, the readall method is used to waste memory resources. Other technologies should be used to input a file, such as reading files by row.

Example:
Sub du_all ()
Dim FSO, A, retstring
Const forreading = 1
Set FSO = Createobject ("scripting. FileSystemObject ")
Set a = FSO. opentextfile ("C:/testfile.txt", forreading, false)
Retstring = A. readall' read all
Debug. Print retstring
A. Close
End sub

There are also two methods for secondary reading:

· Skip Method

Syntax: object. Skip (characters)

Function: Skips a specified number of characters when reading a textstream file.

Example:
Sub duskip ()
Dim FSO, A, retstring
Const forreading = 1
Set FSO = Createobject ("scripting. FileSystemObject ")
Set a = FSO. opentextfile ("C:/testfile.txt", forreading, false)
Do while a. atendofline <> true 'indicates whether the row ends
Retstring = retstring & A. Read (1) 'read a character
A. Skip (1) 'skip one character
Loop
A. Close
Debug. Print retstring '. You can see the characters that read the odd digits of the first line.
End sub

· Skipline Method

Syntax: object. skipline

Function: skip the next row when reading a textstream file.

3. Write data to a file

There are also three methods to write data to a file.

· Write Method

Syntax: object. Write (string)

Function: Write a specified string to a textstream file.

Example:
Sub Xie ()
Const forwriting = 2, forappending = 8
Dim FS, F
Set FS = Createobject ("scripting. FileSystemObject ")
Set F = FS. opentextfile ("C:/testfile.txt", forappending, tristatefalse)
F. Write "Hello world! "'Write string
F. Close
End sub

· Writeline Method

Syntax: object. writeline ([String])

Function: Write a specified string and line break to a textstream file.

Example:
F. writeline ("Hello world! ") 'Write a string with a line break.

· Writeblanklines Method

Syntax: object. writeblanklines (lines)

Function: Write a specified number of linefeeds to a textstream file.

Example:
F. writeblanklines (3) 'is equivalent to pressing the carriage return three times.

4. close the file

Using the close method of the textstream object, the above example already exists, which is very simple.

(7) Summary

From the above introduction, we can see that FileSystemObject is easier to process files and folders than VBA statements. This is because the FileSystemObject object uses the object-oriented syntax. In addition, FileSystemObject is not inferior to VBA statements in processing text files, which is highly recommended. The only problem is that binary files cannot be processed. Microsoft said in the relevant documents that it plans to support binary files in the future, but it should be just a plan.