视频1 视频21 视频41 视频61 视频文章1 视频文章21 视频文章41 视频文章61 推荐1 推荐3 推荐5 推荐7 推荐9 推荐11 推荐13 推荐15 推荐17 推荐19 推荐21 推荐23 推荐25 推荐27 推荐29 推荐31 推荐33 推荐35 推荐37 推荐39 推荐41 推荐43 推荐45 推荐47 推荐49 关键词1 关键词101 关键词201 关键词301 关键词401 关键词501 关键词601 关键词701 关键词801 关键词901 关键词1001 关键词1101 关键词1201 关键词1301 关键词1401 关键词1501 关键词1601 关键词1701 关键词1801 关键词1901 视频扩展1 视频扩展6 视频扩展11 视频扩展16 文章1 文章201 文章401 文章601 文章801 文章1001 资讯1 资讯501 资讯1001 资讯1501 标签1 标签501 标签1001 关键词1 关键词501 关键词1001 关键词1501 专题2001
Excel_VBA常用技巧_第10章.文件操作
2025-09-25 17:53:30 责编:小OO
文档
VBA常用技巧

目录

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所示。如果省略,则会返回不包含属性的匹配文件。

常数描述
vbNormal0(缺省) 指定没有属性的文件。

vbReadOnly1指定无属性的只读文件。
vbHidden2指定无属性的隐藏文件。
VbSystem4指定无属性的系统文件,在Macintosh中不可用。

vbVolume8指定卷标文件;如果指定了其它属性,则忽略。vbVolume 在Macintosh中不可用。

vbDirectory16指定无属性文件及其路径和文件夹。
vbAlias指定的文件名是别名,只在Macintosh上可用。

表格 1731    attributes设置值

注意 在第一次调用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以字节表示的文件大小
表格 1801    File对象的常用属性

运行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所示。

常数描述
ForReading1打开一个只读文件,不能对此文件进行写操作。
ForAppending8打开一个文件并写到文件的尾部。
表格 1802    iomode参数设置值

参数create是可选的,它表示如果指定的参数filename不存在是否可以创建一个新文件。如果创建新文件,其值为True。若不创建文件其值为False。缺省值为False。

参数format是可选的,打开文件的格式,设置值如表格 1803所示。

常数描述
TristateUseDefault-2使用系统缺省打开文件
TristateTrue-1以 Unicode 格式打开文件

TristateFalse0以 ASCII 格式打开文件

表格 1803    format参数设置值

第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过程将在示例所在的文件夹中创建一个名称为“工资表”的文本文件并将工作表数据读入到文件内。下载本文

显示全文
专题