目录
VBA常用技巧 1
第10章 文件操作 2
技巧1 导入文本文件 2
1-1 使用查询表导入 2
1-2 使用Open 语句导入 3
1-3 使用OpenText方法 5
技巧2 将数据写入文本文件 6
2-1 使用Print # 语句 6
2-2 另存为文本文件 8
技巧3 文件修改的日期和时间 9
技巧4 查找文件或文件夹 10
技巧5 获得当前文件夹的名称 12
技巧6 创建和删除文件夹 12
技巧7 重命名文件或文件夹 13
技巧8 复制指定的文件 14
技巧9 删除指定的文件 15
技巧10 搜索特定的文件 16
技巧11 使用WSH处理文件 18
11-1 获取文件信息 18
11-2 查找文件 20
11-3 移动文件 21
11-4 复制文件 21
11-5 删除文件 22
11-6 创建文件夹 23
11-7 复制文件夹 24
11-8 移动文件夹 25
11-9 删除文件夹 25
11-10 导入文本文件 26
11-11 创建文本文件 28
第10章 文件操作
技巧1 导入文本文件
在实际应用中,我们经常从软件中将数据导出为文本文件,在需要将这些文本文件导入到Excel中时可以使用以下的方法。
1-1使用查询表导入
在Excel VBA中可以使用Add方法新建查询表后导入文本文件,如下面的代码所示。
#001 Sub AddQuery()
#002 Sheet1.UsedRange.ClearContents
#003 With Sheet1.QueryTables.Add( _
#004 Connection:="TEXT;" & ThisWorkbook.Path & "\工资表.txt", _
#005 Destination:=Range("A1"))
#006 .TextFilePlatform = 936
#007 .TextFileCommaDelimiter = True
#008 .Refresh
#009 End With
#010 End Sub
代码解析:
AddQuery过程使用QueryTable对象的Add方法新建查询表后将文本文件“工资表.txt”的内容导入到工作表中。
应用于QueryTable对象的Add方法新建一个查询表,返回QueryTable对象,该对象代表新建的查询表,语法如下:
expression.Add(Connection, Destination, Sql)
参数expression是必需的,返回一个QueryTables对象。
参数Connection是必需的,查询表的数据源。如果数据源是文本文件,是“TEXT;<文本文件路径和名称>”形式的字符串,其他数据源请参阅帮助。
参数Destination是必需的,Range类型,查询表目标区域左上角单元格用于放置生成的查询表的区域。目标区域必须在包含expression 指定的QueryTables对象的工作表上。
参数Sql是可选的,在ODBC数据源上运行的SQL查询字符串,当将QueryTable对象、文本文件、或是ADO或DAO Recordset对象指定为数据源时不能使用该参数。
第3行到第5行代码在工作表中建立对位于同一目录中的“工资表.txt”文本文件的查询,并将查询结果放置到工作表中。
第6行代码设置导入的文本文件的原始格式,QueryTables对象的TextFilePlatform属性返回或设置正向查询表中导入的文本文件的原始格式,默认值是在“文本导入向导”的“文件原始格式”选项中的当前设置。
第7行代码设置文本文件导入查询表中时,是以逗号作为分隔符。
第8行代码使用Refresh方法更新外部数据区域,应用于QueryTable对象的Refresh方法更新外部数据区域,语法如下:
expression.Refresh(BackgroundQuery)
参数expression是必需的,返回一个QueryTable对象。
参数BackgroundQuery是可选的的,只用于基于SQL查询结果的QueryTable。
1-2使用Open 语句导入
使用Open语句输入文本文件,如下面的代码所示。
#001 Sub OpenText()
#002 Dim Filename As String
#003 Dim myText As String
#004 Dim mArr() As String
#005 Dim i As Integer
#006 Dim j As Integer
#007 Filename = ThisWorkbook.Path & "\工资表.txt"
#008 j = 1
#009 Sheet1.UsedRange.ClearContents
#010 Open Filename For Input As #1
#011 Do While Not EOF(1)
#012 Line Input #1, myText
#013 mArr = Split(myText,
#014 For i = 0 To UBound(mArr)
#015 Sheet1.Cells(j, i + 1) = mArr(i)
#016 Next
#017 j = j + 1
#018 Loop
#019 Close #1
#020 End Sub
代码解析:
OpenText过程使用Open语句将文本“工资表.txt”的内容输入到工作表中。
第10行代码使用Open语句打开文本文件以完成对文本文件的输入。Open语句能够对文件输入/输出(I/O),语法如下:。
Open pathname For mode [Access access] [lock] As [#]filenumber [Len=reclength]
Pathname是必需的,指定文件名,该文件名可能还包括目录、文件夹及驱动器。
mode是必需的,指定文件方式,有Append、Binary、Input、Output、或Random方式。如果未指定方式,则以Random访问方式打开文件。
Access是可选的,说明打开的文件可以进行的操作,有Read、Write、或Read Write操作。
lock是可选的,说明限定于其它进程打开的文件的操作,有Shared、Lock Read、Lock Write、和Lock Read Write操作。
filenumber是必需的,一个有效的文件号,范围在 1 到 511 之间。使用FreeFile函数可得到下一个可用的文件号。
reclength是可选的,小于或等于 32,767(字节)的一个数。对于用随机访问方式打开的文件,该值就是记录长度。对于顺序文件,该值就是缓冲字符数。
第11行代码使用Do...Loop 语句重复执行第12行到第17行代码,直到文本文件的结尾。EOF函数返回一个Integer,它返回Boolean值True,表明已经到达为Random或顺序Input打开的文件结尾,语法如下:
EOF(filenumber)
参数filenumber是必需的,是一个Integer,包含任何有效的文件号。
第12行代码使用Line Input # 语句读入一行数据并将其赋予变量myText。Line Input # 语句从已打开的顺序文件中读出一行并将它分配给String变量,语法如下:
Line Input #filenumber, varname
Filenumber是必需的,任何有效的文件号。
varnamer是必需的,有效的Variant或String变量名。
第13行代码使用Split函数按逗号作为分隔符分开这行字符,赋值数组mArr。关于Split函数请参阅技巧169-2。
第14行到第16行代码将数组mArr循环赋值给单元格,请参阅技巧169-1。
第19行代码关闭文本文件。Close语句关闭Open语句所打开的输入/输出 (I/O) 文件,语法如下:
Close [filenumberlist]
参数filenumberlist是可选的,为一个或多个文件号,如省略则将关闭所有由Open语句打开的活动文件。
1-3使用OpenText方法
使用OpenText方法载入一个文本文件并将其作为包含单个工作表的工作簿处理,如下面的代码所示。
#001 Sub OpenText()
#002 Dim myFileName As String
#003 myFileName = "工资表.txt"
#004 Sheet1.UsedRange.ClearContents
#005 Workbooks.OpenText _
#006 Filename:=ThisWorkbook.Path & "\\" & myFileName, _
#007 StartRow:=1, DataType:=xlDelimited, Comma:=True
#008 With ActiveWorkbook
#009 With .Sheets("工资表").Range("A1").CurrentRegion
#010 ThisWorkbook.Sheets("Sheet1").Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
#011 End With
#012 .Close False
#013 End With
#014 End Sub
代码解析:
OpenText过程使用OpenText方法载入“工资表.txt”文本文件并将其数据写入到工作表中。
第5行到第7行代码使用OpenText方法载入“工资表.txt”文本文件。OpenText方法载入一个文本文件,并将其作为包含单个工作表的工作簿进行分列处理,然后在此工作表中放入经过分列处理的文本文件数据,语法如下:
expression.OpenText(FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)
其中参数FileName是必需的,指定要载入并作分列处理的文件名称。
参数StartRow是可选的,作分列处理的起始行号,默认值为 1。
参数DataType是可选的,在文件中指定数据的列格式。
参数Comma是可选的,如果该值为True,则将分隔符设为逗号。
其他参数请参阅VBA中的帮助文档。
第9行到第11行代码将作为工作表打开的文本文件中的数据写入到工作表中。
第13行代码使用Close方法关闭打开的文本文件。
技巧2 将数据写入文本文件
在需要时可以将Excel中的数据写入到文本文件中,有以下方法可以实现。
2-1使用Print # 语句
使用Print # 语句将数据写入文本文件中,如下面的代码所示。
#001 Sub PrintText()
#002 Dim myFileName As String
#003 Dim myDataAr() As Variant
#004 Dim myStr As String
#005 Dim myRow As Integer
#006 Dim myCol As Integer
#007 Dim i As Integer
#008 Dim j As Integer
#009 On Error Resume Next
#010 myFileName = "工资表.txt"
#011 Kill ThisWorkbook.Path & "\\" & myFileName
#012 With Sheet1
#013 myRow = .Range("A65536").End(xlUp).Row
#014 myCol = .Range("IV1").End(xlToLeft).Column
#015 ReDim myDataAr(1 To myRow, 1 To myCol)
#016 For i = 1 To myRow
#017 For j = 1 To myCol
#018 myDataAr(i, j) = .Cells(i, j).Value
#019 Next
#020 Next
#021 Open ThisWorkbook.Path & "\\" & myFileName For Output As #1
#022 For i = 1 To UBound(myDataAr, 1)
#023 myStr = ""
#024 For j = 1 To UBound(myDataAr, 2)
#025 myStr = myStr & CStr(myDataAr(i, j)) &
#026 Next
#027 myStr = Left(myStr, (Len(myStr) - 1))
#028 Print #1, myStr
#029 Next
#030 Close #1
#031 End With
#032 MsgBox "文件保存成功!"
#033 End Sub
代码解析:
PrintText过程将工作表中数据写入到文本文件“工资表.txt”中。
第11行代码使用Kill方法删除同一目录中可能存在的同名文本文件。
第13、14行使用单元格的End属性取得工作表中已使用数据的行、列号,关于End属性请参阅技巧3 。
第15行代码重新定义动态数组myDataAr的大小。关于动态数组请参阅技巧169-3。
第16行到第20行代码将工作表数据赋给数组myDataAr。
第21行代码使用Open语句打开文本文件以完成对文本文件的输入。关于Open语句请参阅技巧170-2。
第22行到第29行代码使用Print #语句将数组myDataAr中的所有元素写入到文本文件中。Print #语句将格式化显示的数据写入顺序文件中,语法如下:
Print #filenumber, [outputlist]
Filenumber是必需的,任何有效的文件号。
第30行代码Close语句关闭文本文件。
2-2另存为文本文件
使用SaveAs方法将工作表另存为文本文件,如下面的代码所示。
#001 Sub SaveText()
#002 Dim myFileName As String
#003 myFileName = "工资表.txt"
#004 On Error Resume Next
#005 Kill ThisWorkbook.Path & "\\" & myFileName
#006 Application.ScreenUpdating = False
#007 Worksheets("Sheet1").Copy
#008 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
#009 & "\\" & myFileName, _
#010 FileFormat:=xlCSV
#011 MsgBox "文件保存成功!"
#012 ActiveWorkbook.Close SaveChanges:=False
#013 Application.ScreenUpdating = True
#014 End Sub
代码解析:
SaveText过程将工作表“Sheet1”保存为文本文件。
第4、5行代码使用Kill方法删除同一目录中可能存在的同名文本文件。
第7行代码使用Copy方法复制工作表“Sheet1”。
第8行到第10行代码使用SaveAs方法将文件保存为文本文件。应用于Workbook对象的SaveAs方法保存对不同文件中的工作表的更改,语法如下:
expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
其中参数Filename表示要保存的文件名。可包含完整路径。如果不指定路径,Microsoft Excel 将文件保存到当前文件夹中。
其中参数FileFormat指定保存文件时使用的文件格式,在本例中指定为xlCSV即保存为文本文件。
第12行代码使用Close方法关闭活动工作簿。
技巧3 文件修改的日期和时间
在VBA过程中如果需要获得文件最后修改的日期和时间,可以使用FileDateTime函数,如下面的代码所示。
#001 Sub myDateTime()
#002 Dim Stmp As String
#003 Dim myDateTime As Date
#004 Stmp = ThisWorkbook.Path & "\\" & ThisWorkbook.Name
#005 myDateTime = FileDateTime(Stmp)
#006 MsgBox Stmp & "最后修改时间是:" & Chr(13) & myDateTime
#007 End Sub
代码解析:
myDateTime过程使用消息框显示文件最后修改的日期和时间。
FileDateTime函数返回一个文件被创建或最后修改后的日期和时间,语法如下:
FileDateTime(pathname)
pathname 参数是必需的,用来指定文件名的字符串表达式。pathname 可以包含目录或文件夹、以及驱动器。
第4行代码使用变量Stmp保存代码所在工作簿的路径和名称。
第5行代码使用变量myDateTime保存FileDateTime函数返回的日期和时间。
运行myDateTime过程结果如图 1721所示。
图 1721 文件最后修改的日期和时间
技巧4 查找文件或文件夹
在磁盘中查找文件或文件夹,可以使用Dir函数,如下面的代码所示。
#001 Sub mydir()
#002 Dim mydir As String
#003 Dim b As Byte
#004 b = 1
#005 Range("A:A").ClearContents
#006 mydir = Dir(ThisWorkbook.Path & "\\*.xls", vbNormal)
#007 Do While mydir <> ""
#008 Cells(b, 1) = mydir
#009 mydir = Dir
#010 b = b + 1
#011 Loop
#012 End Sub
代码解析:
Mydir过程使用Dir函数在代码所在工作簿的文件夹中查找所有的Excel文件,找到后写入到工作表的A列单元格中。
第2行代码声明变量mydir保存返回的文件名称。
第3行代码声明变量b保存返回的文件数目。
第4行代码设置变量b的初始值。
第5行代码清除A列所有数据。
第6行代码使用Dir函数在代码所在工作簿的文件夹中查找Excel文件。Dir函数返回一个String,用以表示一个文件名、目录名或文件夹名称,语法如下:
Dir[(pathname[, attributes])]
参数pathname是可选的,用来指定文件名的字符串表达式,可能包含目录或文件夹、以及驱动器。如果没有找到pathname,则会返回零长度字符串 ("")。
参数attributes是可选的,常数或数值表达式,其总和用来指定文件属性,如表格 1731所示。如果省略,则会返回不包含属性的匹配文件。
常数 | 值 | 描述 |
vbNormal | 0 | (缺省) 指定没有属性的文件。 |
vbReadOnly | 1 | 指定无属性的只读文件。 |
vbHidden | 2 | 指定无属性的隐藏文件。 |
VbSystem | 4 | 指定无属性的系统文件,在Macintosh中不可用。 |
vbVolume | 8 | 指定卷标文件;如果指定了其它属性,则忽略。vbVolume 在Macintosh中不可用。 |
vbDirectory | 16 | 指定无属性文件及其路径和文件夹。 |
vbAlias | 指定的文件名是别名,只在Macintosh上可用。 |
注意 在第一次调用Dir函数时,必须指定pathname,否则会产生错误。
第7行到第11行代码将返回的文件名称写入到A列单元格中。Dir函数会返回匹配 pathname参数的第一个文件名,若想得到其他匹配pathname参数的文件名,需再一次调用Dir函数,且不要使用参数。如果已没有合乎条件的文件,则Dir函数会返回一个零长度字符串 ("")。
运行Mydir过程工作表中如图 1731所示。
图 1731 使用Dir函数返回文件名称
技巧5 获得当前文件夹的名称
在处理文件时经常需要获得当前文件夹的名称,此时可以使用CurDir函数,如下面的代码所示。
#001 Sub CurFolder()
#002 MsgBox CurDir("F")
#003 End Sub
CurFolder过程使用消息框显示F盘中的当前文件夹名称。
CurDir函数返回一个Variant类型的文件路径。如果需要返回字符串类型的文件路径则使用CurDir$,语法如下:
CurDir[(drive)]
参数drive是可选的,字符串表达式,指定一个存在的驱动器。如果没有指定驱动器,或参数drive 是零长度字符串 (""),则CurDir函数会返回当前驱动器的路径。
假设示例文件保存在F盘的“VBA常用技巧\示例文件\第10章 文件操作”文件夹中,运行CurFolder过程结果如图 1741所示。
图 1741 获得当前文件夹的名称
技巧6 创建和删除文件夹
可以在程序运行时创建和删除文件夹,如下面的代码所示。
#001 Sub TempFolder()
#002 On Error Resume Next
#003 MkDir ThisWorkbook.Path & "\\Temp"
#004 End Sub
代码解析:
TempFolder过程使用MkDir语句在示例所在的文件夹中创建“Temp”文件夹。MkDir语句创建一个新的目录或文件夹,语法如下:
MkDir path
参数path是必需的,指定所要创建的目录或文件夹的字符串表达式,可以包含驱动器。如果没有指定驱动器,则在当前驱动器上创建新的目录或文件夹。
第2行代码启动错误处理程序,因为在创建过程中如果文件夹中已存在相同名称的“Temp”文件夹会发生 “路径未找到”错误,所以使用On Error Resume Next语句忽略错误。
第3行代码使用MkDir语句创建“Temp”文件夹。
如果需要删除不需要的文件夹可以使用RmDir语句,如下面的代码所示。
#001 Sub RmFolder()
#002 On Error Resume Next
#003 RmDir ThisWorkbook.Path & "\\Temp"
#004 End Sub
代码解析:
RmFolder过程使用RmDir语句删除在示例所在的文件夹中创建“Temp”文件夹。RmDir语句删除一个存在的目录或文件夹,语法如下:
RmDir path
参数path是必需的,指定所要创建的目录或文件夹的字符串表达式,可以包含驱动器。如果没有指定驱动器,则在当前驱动器上创建新的目录或文件夹。
第2行代码启动错误处理程序,因为在使用RmDir语句删除并不存在的文件夹或删除含有文件的文件夹时会发生 “路径未找到”错误,所以使用On Error Resume Next语句忽略错误。
第3行代码使用RmDir语句删除“Temp”文件夹。如果“Temp”文件夹中含有文件可以在删除文件夹之前,先使用Kill语句来删除所有文件,请参阅技巧178 。
技巧7 重命名文件或文件夹
使用Name语句可以重命名文件或文件夹,如下面的代码所示。
#001 Sub Filename()
#002 On Error Resume Next
#003 Name ThisWorkbook.Path & "\\123" As ThisWorkbook.Path & "\\ABC"
#004 Name ThisWorkbook.Path & "\\123.xls" As ThisWorkbook.Path & "\\ABC\\ABC.xls"
#005 End Sub
Filename过程使用Name语句重命名示例文件所在文件夹中的“123”文件夹和“123.xls”Excel文件并将重命名后的Excel文件移动到重命名后的文件夹中。
Name语句重新命名一个文件、目录、或文件夹,语法如下:
Name oldpathname As newpathname
参数oldpathname是必需的,字符串表达式,指定已存在的文件名和位置,可以包含目录或文件夹、以及驱动器。
参数newpathname是必需的,字符串表达式,指定新的文件名和位置,可以包含目录或文件夹、以及驱动器。
第2行代码启动错误处理程序,因为在重命名过程中如果参数oldpathname指定的文件或文件夹不存在会发生 “文件未找到”错误,所以使用On Error Resume Next语句忽略错误。
第3行代码使用Name语句将示例文件所在文件夹中的“123”文件夹重命名为“ABC”文件夹。
第4行代码使用Name语句将示例文件所在文件夹中的“123.xls”Excel文件重命名为“ABC.xls”文件并移动到“ABC”文件夹中。
在运行Filename过程前请确认示例文件所在文件夹中包含一个“123”文件夹和一个“123.xls”Excel文件。
技巧8 复制指定的文件
如果需要把文件从一个地方复制到另一个地方,可以使用FileCopy语句复制文件,如下面的代码所示。
#001 Sub CopyFile()
#002 Dim SourceFile As String
#003 Dim DestinationFile As String
#004 SourceFile = ThisWorkbook.Path & "\\123.xls"
#005 DestinationFile = ThisWorkbook.Path & "\\ABC\\abc.xls"
#006 FileCopy SourceFile, DestinationFile
#007 End Sub
代码解析:
CopyFile过程使用FileCopy语句将示例文件所在文件夹中的“123.xls”Excel文件复制到示例文件目录下的“ABC”文件夹中并重新命名为“abc.xls”。
FileCopy语句复制一个文件,语法如下:
FileCopy source, destination
Source参数是必需的,字符串表达式,用来表示要被复制的文件名。source参数可以包含目录或文件夹、以及驱动器。
destination参数是必需的,字符串表达式,用来指定要复制的目地文件名。destination参数 可以包含目录或文件夹、以及驱动器。
注意 不能对一个已打开的文件使用 FileCopy 语句,否则会产生错误。
第4行代码指定被复制的文件名称和路径。
第5行代码指定目的文件名称和路径,如果已存在相同名称的文件则会覆盖原文件。
第6行代码使用FileCopy语句复制文件。
技巧9 删除指定的文件
使用Kill方法删除指定的文件,如下面的代码所示。
#001 Sub KillFile()
#002 Dim myFile As String
#003 myFile = ThisWorkbook.Path & "\\123.xls"
#004 If Dir(myFile) <> "" Then Kill myFile
#005 End Sub
代码解析:
KillFile过程使用Kill方法示例文件所在文件夹中的“123.xls”文件。
第3行代码指定所要删除文件的路径和文件名称。
第4行代码使用Dir函数返回指定文件名,(关于Dir函数请参阅技巧173 )如果存在该文件则使用Kill语句删除。Kill语句从磁盘中删除文件,语法如下:
Kill pathname
参数pathname是必需的,用来指定一个文件名的字符串表达式,可以包含目录或文件夹、以及驱动器。
在Microsoft Windows中,Kill方法支持多字符 (*) 和单字符(?)的统配符来指定多重文件,如需要删除当前目录下所有*. Xls文件可以使用下面的代码:
Kill "*.xls"
注意 使用Kill方法不能删除已打开的文件,否则会产生错误。
技巧10 搜索特定的文件
如果需要对文件夹中所有的Excel文件进行相同的操作,那么可以使用Execute方法进行文件搜索,示例代码如下所示。
#001 Sub Sort()
#002 Dim i As Byte
#003 Application.ScreenUpdating = False
#004 With Application.FileSearch
#005 .LookIn = ThisWorkbook.Path
#006 .FileType = msoFileTypeExcelWorkbooks
#007 If .Execute > 0 Then
#008 For i = 1 To .FoundFiles.Count
#009 If .FoundFiles(i) <> ThisWorkbook.FullName Then
#010 Workbooks.Open .FoundFiles(i)
#011 With ActiveWorkbook
#012 .Sheets("Sheet1").Range("A1") = "最后打开时间:" & Now
#013 .Close True
#014 End With
#015 End If
#016 Next
#017 End If
#018 End With
#019 Application.ScreenUpdating = True
#020 End Sub
代码解析:
Sort过程搜索同一目录中的所有Excel文件并对其进行操作。
第3行代码关闭屏幕更新功能,加快代码的运行速度。
第4行代码为文件搜索创建一个FoundFiles对象。
第5行代码设置要搜索的文件夹,应用于FoundFiles对象的LookIn属性返回或设置在指定的文件搜索过程中要搜索的文件夹。
第6行代码设置搜索的文件类型为Excel文件,应用于FoundFiles对象的FileType属性返回或设置文件搜索过程中要查找的文件类型,设置为msoFileTypeExcelWorkbooks返回Excel文件。
第7行代码开始对指定文件进行搜索,应用于FoundFiles对象的Execute方法用于搜索文件,语法如下:
expression.Execute(SortBy, SortOrder, AlwaysAccurate)
参数expression是必需的,返回一个FoundFiles对象。
参数SortBy是可选的,用于对返回的文件进行排序。
参数SortOrder是可选的,表明所返回文件的排序顺序。
参数AlwaysAccurate是可选的,设置为True使文件搜索包括上次更新文件索引以来添加、修改或删除的文件。
在使用Execute方法搜索文件时,如果没有找到文件,则返回零(0),如果找到一个或多个文件,则返回一个正数。
第8行代码使用For...Next 语句遍历Execute方法返回的返回的文件列表。应用于FoundFiles对象的FoundFiles属性返回一个FoundFiles对象,代表由文件搜索过程中返回的文件列表。
第9行代码判断返回的单个FoundFiles对象的名称是否是示例文件的名称,如果否则执行后续代码。
第10行代码使用应用于Workbooks对象的Open方法打开由返回的单个FoundFiles对象代表的工作簿。
第11行到第14行代码在打开的活动工作簿的工作表中写入打开时间后保存、关闭活动工作簿。
运行Sort过程将打开示例所在文件夹中所有的Excel文件并对其进行相应的操作。
技巧11 使用WSH处理文件
Windows Scripting Host(WSH)可以创建一些控制Windows操作系统和应用程序以及从操作系统中获取信息的小程序,而使用WSH的FileSystemObject对象可以用来处理文件系统。
在使用WSH处理文件时,必需使用CreateObject函数创建一个ActiveX对象(FileSystemObject对象),用来提供访问计算机的文件系统,如下面的代码所示:
Dim MyFile As Object
Set MyFile = CreateObject("Scripting.FileSystemObject")
上面的代码首先声明一个名为MyFile的对象变量,然后使用CreateObject函数创建一个ActiveX对象并将该对象赋给对象变量。
CreateObject函数创建并返回一个对ActiveX对象的引用,语法如下:
CreateObject(class,[servername])
其中参数class是必需的,要创建的应用程序名称和类,使用appname.objecttype这种语法,appname指定该对象的应用程序名称,objecttype指定该对象的类型或类。
在声明了对象变量MyFile为Windows Scripting库的FileSystemObject对象后就能使用该对象的属性、方法来处理文件系统。
11-1获取文件信息
如果需要获得指定文件的信息,可以使用File对象的Getfile方法,如下面的代码所示。
#001 Sub Fileinfo()
#002 Dim MyFile As Object
#003 Dim Str As String
#004 Dim StrMsg As String
#005 Str = ThisWorkbook.Path & "\\123.xls"
#006 Set MyFile = CreateObject("Scripting.FileSystemObject")
#007 With MyFile.Getfile(Str)
#008 StrMsg = StrMsg & "文件名称:" & .Name & Chr(13) _
#009 & "文件创建日期:" & .DateCreated & Chr(13) _
#010 & "文件修改日期:" & .DateLastModified & Chr(13) _
#011 & "文件访问日期:" & .DateLastAccessed & Chr(13) _
#012 & "文件保存路径:" & .ParentFolder
#013 End With
#014 MsgBox StrMsg
#015 Set MyFile = Nothing
#016 End Sub
代码解析:
Fileinfo过程使用Getfile方法获取示例所在文件夹中的“123.xls”文件的信息。
第5行代码将文件路径名称赋给变量Str。
第6行代码使用CreateObject函数创建FileSystemObject对象并将该对象赋给变量MyFile。
第7行代码使用Getfile方法返回一个File对象。Getfile方法返回一个和指定路径中文件相对应的File对象,语法如下:
object.GetFile(filespec)
参数object是必需的,FileSystemObject对象的名称。
参数filespec是必需的,指定文件的路径。
第8行到第12行代码根据File对象的属性取得文件信息,File对象的常用属性如表格 1801所示。
属性 | 描述 |
Name | 文件名称 |
DateCreated | 文件创建日期 |
DateLastModified | 文件最后修改日期 |
DateLastAccessed | 文件最后访问日期 |
ParentFolder | 文件的父文件夹 |
Path | 文件的完整路径 |
Type | 文件类型 |
Size | 以字节表示的文件大小 |
运行Fileinfo过程使用消息框显示“123.xls”文件的信息,如图 1801所示。
图 1801 获取文件信息
11-2查找文件
使用FileSystemObject对象的FileExists方法可以查找指定的文件,如下面的代码所示。
#001 Sub FileExis()
#002 Dim MyFile As Object
#003 Dim Str As String
#004 Dim StrMsg As String
#005 Str = ThisWorkbook.Path & "\\123.xls"
#006 Set MyFile = CreateObject("Scripting.FileSystemObject")
#007 If MyFile.FileExists(Str) Then
#008 MsgBox "文件已找到!"
#009 Else
#010 MsgBox "文件不存在!"
#011 End If
#012 Set MyFile = Nothing
#013 End Sub
代码解析:
FileExis过程使用FileExists方法查找示例所在文件夹中是否存在“123.xls”文件。
第6行代码使用CreateObject函数创建FileSystemObject对象并将该对象赋给变量MyFile。
第7行代码使用FileExists方法可以查找文件。应用于FileSystemObject对象的FileExists方法查找指定的文件,语法如下:
object.FileExists(filespec)
参数object是必需的,FileSystemObject对象的名称。
参数filespec是必需的,要确定是否存在的文件的名字。如果文件不在当前文件夹中,必须提供一个完整的路径说明。
使用FileExists方法查找文件时如果指定的文件存在,返回True,若不存在,则返回False,根据返回值可以确定所要查找的文件是否存在。
11-3移动文件
如果需要把文件从一个地方移动到另一个地方,可以使用FileSystemObject对象的MoveFile方法,如下面的代码所示。
#001 Sub MoveFile()
#002 Dim MyFile As Object
#003 On Error Resume Next
#004 Set MyFile = CreateObject("Scripting.FileSystemObject")
#005 MyFile.MoveFile ThisWorkbook.Path & "\\123.xls", ThisWorkbook.Path & "\\ABC\\"
#006 Set MyFile = Nothing
#007 End Sub
代码解析:
MoveFile过程使用MoveFile方法将示例文件所在文件夹中的“123.xls”文件移动到“ABC”文件夹中。
第4行代码使用CreateObject函数创建FileSystemObject对象并将该对象赋给变量MyFile。
第5行代码使用MoveFile方法移动文件。应用于FileSystemObject对象的MoveFile方法将一个或多个文件从一个地方移动到另一个地方,语法如下:
object.MoveFile source, destination
参数object是必需的, FileSystemObject对象的名称。
参数source是必需的,一个或多个要移动文件的路径,source参数字符串在路径的最后部件中可以使用通配符。
参数destinatio是必需的,一个或多个文件要移动到的目标路径,不能使用通配符。
运行MoveFile过程将示例文件所在文件夹中的“123.xls”文件移动到同一目录中的“ABC”文件夹。
11-4复制文件
如果需要把文件从一个地方复制到另一个地方,可以使用CopyFile方法,如下面的代码所示。
#001 Sub CopyFile()
#002 Dim MyFile As Object
#003 On Error Resume Next
#004 Set MyFile = CreateObject("Scripting.FileSystemObject")
#005 MyFile.CopyFile ThisWorkbook.Path & "\\123.xls", ThisWorkbook.Path & "\\ABC\\"
#006 Set MyFile = Nothing
#007 End Sub
代码解析:
CopyFile过程使用CopyFile方法将示例文件所在文件夹中的“123.xls”文件复制到“ABC”文件夹中。
第4行代码使用CreateObject函数创建FileSystemObject对象并将该对象赋给变量MyFile。
第5行代码使用CopyFile方法复制文件。应用于FileSystemObject对象的CopyFile方法把一个或多个文件从一个地方复制到另一个地方,语法如下:
object.CopyFile source, destination[, overwrite]
参数object是必需的, FileSystemObject对象的名字。
参数source是必需的,指明一个或多个要被复制文件的字符串文件说明,可以包括通配符。
参数destination是必需的,指明参数source中的一个或多个文件要被复制到的接受端的字符串,不允许有通配符。
参数overwrite是可选的,表示存在的文件是否被覆盖。如果是True,文件将被覆盖;如果是False,它们不被覆盖,缺省值是True。
注意 如果参数destination指定的接受端具有只读属性设置,不论参数overwrite的值如何设置,CopyFile方法都将失败。
运行CopyFile过程将示例文件所在文件夹中的“123.xls”文件复制到“ABC”文件夹中。
11-5删除文件
如果需要删除一个指定的文件,可以使用DeleteFile方法,如下面的代码所示。
#001 Sub DelFile()
#002 Dim MyFile As Object
#003 On Error Resume Next
#004 Set MyFile = CreateObject("Scripting.FileSystemObject")
#005 MyFile.DeleteFile ThisWorkbook.Path & "\\123.xls"
#006 Set MyFile = Nothing
#007 End Sub
代码解析:
DelFile过程使DeleteFile方法删除示例文件所在文件夹中的“123.xls”文件。
第4行代码使用CreateObject函数创建FileSystemObject对象并将该对象赋给变量MyFile。
第5行代码使用DeleteFile方法复制文件。应用于FileSystemObject对象的DeleteFile方法删除一个指定的文件,语法如下:
object.DeleteFile filespec[, force]
参数object是必需的, FileSystemObject对象的名字。
参数filespec是必需的,指明要删除文件的名字,可以在最后的路径部件中包含通配符。
参数force是可选的,如果要删除具有只读属性设置的文件,其值为True。如果其值为False(缺省),则不能删除具有只读属性设置的文件。
运行DelFile过程删除示例文件所在文件夹中的“123.xls”文件。
11-6创建文件夹
如果需要创建一个文件夹,可以使用CreateFolder方法,如下面的代码所示。
#001 Sub CreFolder()
#002 Dim MyFile As Object
#003 On Error Resume Next
#004 Set MyFile = CreateObject("Scripting.FileSystemObject")
#005 MyFile.CreateFolder (ThisWorkbook.Path & "\\ABC")
#006 Set MyFile = Nothing
#007 End Sub
代码解析:
CreFolder过程使CreateFolder方法在示例文件所在文件夹中创建一个“ABC”文件夹。
第4行代码使用CreateObject函数创建FileSystemObject对象并将该对象赋给变量MyFile。
第5行代码使用CreateFolder方法创建文件夹。应用于FileSystemObject对象的CreateFolder方法创建一个文件夹,语法如下:
object.CreateFolder(foldername)
参数object是必需的, FileSystemObject对象的名字。
参数foldername是必需的,字符串表达式,指明要创建文件夹的名称和路径。
运行CreFolder过程将在示例文件所在文件夹中创建一个“ABC”文件夹。
11-7复制文件夹
如果需要复制文件夹,可以使用CopyFolder方法,如下面的代码所示。
#001 Sub CopyFolder()
#002 Dim MyFile As Object
#003 Set MyFile = CreateObject("Scripting.FileSystemObject")
#004 MyFile.CopyFolder ThisWorkbook.Path & "\\ABC", ThisWorkbook.Path & "\\123"
#005 Set MyFile = Nothing
#006 End Sub
代码解析:
CopyFolder过程使CopyFolder方法将示例文件所在文件夹中的“ABC”文件夹复制并改名为“123”文件夹。
第3行代码使用CreateObject函数创建FileSystemObject对象并将该对象赋给变量MyFile。
第4行代码使用CopyFolder方法复制文件夹。应用于FileSystemObject对象的CreateObject方法从一个地方递归地复制一个文件夹到另一个地方,语法如下:
object.CopyFolder source, destination[, overwrite]
参数object是必需的, FileSystemObject对象的名字。
参数source是必需的,指明一个或多个被复制文件夹的字符串文件夹说明,可以包括通配符。
参数destination是必需的,被复制文件夹和子文件夹的接受端的字符串,不允许有通配符。
参数overwrite是可选的,表示已存在的文件夹是否被覆盖。如果为True,文件被覆盖,如果为False,文件不被覆盖。缺省值为True。
如果参数source中包含通配符或参数destination以路径分隔符(\)为结尾,则认为参数destination是一个已存在的文件夹,在其中复制相匹配的文件夹和子文件夹。否则认为参数destination是一个要创建的文件夹的名字。
运行CopyFolder过程将示例文件所在文件夹中的“ABC”文件夹复制并改名为“123”文件夹。
11-8移动文件夹
如果需要移动文件夹,可以使用MoveFolder方法,如下面的代码所示。
#001 Sub MoveFolder()
#002 Dim MyFile As Object
#003 On Error Resume Next
#004 Set MyFile = CreateObject("Scripting.FileSystemObject")
#005 MyFile.MoveFolder ThisWorkbook.Path & "\\123", "F:\\123"
#006 Set MyFile = Nothing
#007 End Sub
代码解析:
MoveFolder过程使MoveFolder方法将示例文件所在文件夹中的“123”文件夹移动到F盘中。
第4行代码使用CreateObject函数创建FileSystemObject对象并将该对象赋给变量MyFile。
第5行代码使MoveFolder方法移动文件夹。应用于FileSystemObject对象的MoveFolder方法将一个或多个文件夹从一个地方移动到另一个地方,语法如下:
object.MoveFolder source, destination
参数object是必需的, FileSystemObject对象的名字。
参数source是必需的,指明一个或多个要移动文件夹的字符串文件夹说明,在路径的最后部件中可以包括通配符。
参数destination是必需的,一个或多个文件夹要移动到的目标路径,不能包含通配符。
如果参数source中包含通配符或参数destination以路径分隔符(\)为结尾,则认为参数destination是一个已存在的文件夹,在此文件夹中移动相匹配的文件。否则认为参数destination是一个要创建的文件夹的名字。
运行MoveFolder过程将示例文件所在文件夹中的“123”文件夹移动到F盘中。
11-9删除文件夹
如果需要删除一个文件夹,可以使用DeleteFolder方法,如下面的代码所示。
#001 Sub DelFolder()
#002 Dim MyFile As Object
#003 On Error Resume Next
#004 Set MyFile = CreateObject("Scripting.FileSystemObject")
#005 MyFile.DeleteFolder ThisWorkbook.Path & "\\123"
#006 Set MyFile = Nothing
#007 End Sub
代码解析:
DelFolder过程使DeleteFolder方法删除示例文件所在文件夹中的“123”文件夹。
第4行代码使用CreateObject函数创建FileSystemObject对象并将该对象赋给变量MyFile。
第5行代码使用DeleteFolder方法删除文件。应用于FileSystemObject对象的DeleteFolder方法删除一个指定的文件夹和其中的内容,语法如下:
object.DeleteFolder folderspec[, force]
参数object是必需的, FileSystemObject对象的名字。
参数filespec是必需的,指明要删除的文件夹的名称,可以在最后的路径部件中包含通配符。
参数force是可选的,如果要删除具有只读属性设置的文件夹,其值为True。如果其值为False(缺省),则不能删除具有只读属性设置的文件夹。
运行DelFolder过程删除示例文件所在文件夹中的“123.xls”文件夹。
11-10导入文本文件
如果需要从文本文件中导入数据,可以使用OpenTextFile方法,如下面的代码所示。
#001 Sub OpenText()
#002 Dim MyFile As Object
#003 Dim mArr() As String
#004 Dim j As Integer, i As Integer
#005 j = 1
#006 Sheet1.UsedRange.ClearContents
#007 Set MyFile = CreateObject("Scripting.FileSystemObject") _
#008 .OpenTextFile(ThisWorkbook.Path & "\\" & "工资表.txt")
#009 Do While Not MyFile.AtEndOfStream
#010 mArr = Split(MyFile.ReadLine,
#011 For i = 0 To UBound(mArr)
#012 Sheet1.Cells(j, i + 1) = mArr(i)
#013 Next
#014 j = j + 1
#015 Loop
#016 MyFile.Close
#017 Set MyFile = Nothing
#018 End Sub
代码解析:
OpenText过程使用OpenTextFile方法打开示例文件所在文件夹中的“工资表.txt”文件并将数据导入到工作表中。
第7、8行代码使用OpenTextFile方法打开文本文件。应用于FileSystemObject对象的OpenTextFile方法打开一个指定的文件并返回一个TextStream对象,该对象可用于对文件进行读操作或追加操作,语法如下:
object.OpenTextFile(filename[, iomode[, create[, format]]])
参数object是必需的,FileSystemObject对象的名字。
参数filename是必需的,需要打开的文件名称。
参数iomode是可选的,表示输入/输出方式,设置值如表格 1802所示。
常数 | 值 | 描述 |
ForReading | 1 | 打开一个只读文件,不能对此文件进行写操作。 |
ForAppending | 8 | 打开一个文件并写到文件的尾部。 |
参数create是可选的,它表示如果指定的参数filename不存在是否可以创建一个新文件。如果创建新文件,其值为True。若不创建文件其值为False。缺省值为False。
参数format是可选的,打开文件的格式,设置值如表格 1803所示。
常数 | 值 | 描述 |
TristateUseDefault | -2 | 使用系统缺省打开文件 |
TristateTrue | -1 | 以 Unicode 格式打开文件 |
TristateFalse | 0 | 以 ASCII 格式打开文件 |
第9行代码开始对文本文件进行读操作。应用于TextStream对象的AtEndOfStream属性指示文件指针是否位于TextStream文件中的结尾,如果是返回True,否则返回False。
第10行代码使用Split函数将逐行读取的字符串以逗号进行分隔后赋给数组mArr。关于Split函数请参阅技巧169-2。
应用于TextStream对象的ReadLine方法从一个TextStream文件读取一整行(到换行符但不包括换行符)并返回得到的字符串,语法如下:
object.ReadLine
参数object是必需的,TextStream对象的名字。
第11行到第14行代码将数组元素写入到工作表的单元格。
第15行代码重新读取下一行数据。
第16行使用Close方法关闭打开的文本文件。
运行OpenText过程将“工资表.txt”文件的数据导入到工作表中。
11-11创建文本文件
如果需要将工作表中的数据保存为文本文件,可以创建一个文本文件用于保存数据。
使用CreateTextFile方法创建文本文件,如下面的代码所示。
#001 Sub CreText()
#002 Dim MyFile As Object
#003 Dim myStr As String
#004 Dim j As Integer, i As Integer
#005 Set MyFile = CreateObject("Scripting.FileSystemObject") _
#006 .CreateTextFile(ThisWorkbook.Path & "\\" & "工资表.txt", True)
#007 For i = 1 To Range("A65536").End(xlUp).Row
#008 myStr = ""
#009 For j = 1 To Range("IV"& i).End(xlToLeft).Column
#010 myStr = myStr & Cells(i, j) &
#011 Next
#012 myStr = Left(myStr, (Len(myStr) - 1))
#013 MyFile.WriteLine (myStr)
#014 Next
#015 MyFile.Close
#016 Set MyFile = Nothing
#017 End Sub
代码解析:
CreText过程使CreateTextFile方法创建一个指定名称的文本文件并将工作表数据写入到文件内。
第5、6行代码使用CreateObject函数创建FileSystemObject对象并将该对象赋给变量MyFile后使用CreateTextFile方法创建一个指定名称的文本文件。
应用于FileSystemObject对象的CreateTextFile方法创建一个指定的文件并且返回一个用于该文件读写的TextStream对象,语法如下:
object.CreateTextFile(filename[, overwrite[, unicode]])
参数object是必需的,FileSystemObject对象的名字。
参数filename是必需的,需要创建的文件名称。
参数overwrite是可选的,表示是否覆盖已存在文件。如果可被覆盖其值为True,其值为False时不能覆盖,如果省略,则已存在文件不能覆盖。
参数unicode是可选的,表示文件是作为一个Unicode文件创建的还是作为一个ASCII文件创建的。如果作为一个Unicode文件创建,其值为True,作为一个ASCII 文件创建,其值为False,如果省略,则认为是一个ASCII文件。
第7行代码逐行读取工作表数据。
第8行代码清空字符串变量myStr的内容,用来保存下一行的数据。
第9行代码遍历当前行的所有单元格。
第10行代码将当前行的所有单元格保存到字符串变量myStr中并以逗号进行分隔。
第12行代码去除保存在字符串变量myStr中当前行数据的最后一个逗号。
第13行代码使用WriteLine方法将当前行数据写入到创建的文本文件。
应用于TextStream对象的WriteLine方法写入一个指定的字符串和换行符到一个TextStream文件中,语法如下:
object.WriteLine([string])
参数object是必需的,TextStream对象的名字。
参数string是可选的,要写入文件的正文。如果省略,写入一个换行符。
第15行使用Close方法关闭打开的文本文件。
还可以使用OpenTextFile方法创建文本文件,如下面的代码所示。
#001 Sub OpenText()
#002 Dim MyFile As Object
#003 Dim myStr As String
#004 Dim j As Integer, i As Integer
#005 Set MyFile = CreateObject("Scripting.FileSystemObject") _
#006 .OpenTextFile(ThisWorkbook.Path & "\\" & "工资表.txt", 8, True)
#007 For i = 1 To Range("A65536").End(xlUp).Row
#008 myStr = ""
#009 For j = 1 To Range("IV" & i).End(xlToLeft).Column
#010 myStr = myStr & Cells(i, j) &
#011 Next
#012 myStr = Left(myStr, (Len(myStr) - 1))
#013 MyFile.WriteLine (myStr)
#014 Next
#015 MyFile.Close
#016 Set MyFile = Nothing
#017 End Sub
代码解析:
OpenText过程使OpenTextFile方法创建一个指定名称的文本文件并将工作表数据写入到文件内。
应用于FileSystemObject对象的OpenTextFile方法打开一个指定的文件并返回一个 TextStream对象,该对象可用于对文件进行读操作或追加操作,请参阅技巧180-10。
示例中将OpenTextFile方法的iomode参数设置为8,打开文本文件后在文件的尾部进行追加操作;将create参数设置为True,如果指定的文本文件不存在则创建一个新文件。
注意 如果重复运行OpenText过程将在文本文件中重复写入工作表数据,所以OpenTextFile方法更适用于对文本文件进行追加操作。
OpenText过程的其他代码请参阅CreText过程的代码解析。
运行CreText过程和OpenText过程将在示例所在的文件夹中创建一个名称为“工资表”的文本文件并将工作表数据读入到文件内。下载本文