很多时候,我们需要读取某个目录下的所有excel文件,然后逐个处理。这种情况下,我们只掌握如何打开关闭工作簿的命令就不够了,需要扩展到目录操作。
VBA编程中,目录操作最频繁用到的是Dir函数和GetAttr函数。这两个函数的语法分别是:
a) Dir[(pathname[,attributes])],返回指定目录下符合指定文件属性的第一个内容。
b) GetAttr(pathname),返回指定文件的属性
这两个函数中都涉及到文件属性,在VBA中文件属性有以下几个常量:
常量名称 常量值 对应的文件属性含义
vbNormal 0 普通文件
vbHidden 2 隐藏文件
vbSystem 4 系统文件
vbVolume 8 卷标
vbDirectory 16 目录和文件
我们使用Dir(pathname,vbDirectory)这个命令,就可以显示当前pathname下面的第一个目录或文件。使用GetAttr(pathname)这个命令,就可以获得指定目录或文件的属性。但是这里面有一个需要注意的问题,GetAttr返回的属性可能会是几个属性的组合,比如:如果文件既是隐藏文件又是系统文件,那就会返回6(2与4的和)。如果需要明确的知道某个文件是否具备某一类属性,需要将GetAttr与相应的属性做逻辑与And操作才行。
下面通过一个示例来展示,如何通过巧妙的组合使用Dir和GetAttr这两个函数,来获取指定目录下所有的xlsx文件,并将所有的xlsx文件名存储在一个数组里返回。
Function GetFoldersAndFiles(path As Variant)
Dim arr()
If path = "" Then Exit Function
myfilename = Dir(path, vbDirectory) '查找当前目录下所有文件及子目录
'循环查找当前目录下的所有文件及子目录
'当名称为.或者为..时跳过
'当属性为子目录时忽略,只将属性为文件的存入arr数组
Do
If myfilename <> "." And myfilename <> ".." Then
n = n + 1
ReDim Preserve arr(1 To n)
If (GetAttr(path & myfilename) And vbDirectory) = vbDirectory Then
n = n - 1
Else
If Right(myfilename, 4) = "xlsx" Or Right(myfilename, 4) = "XLSX" Then
arr(n) = Left(myfilename, Len(myfilename) - 5)
Else
n = n - 1
End If
End If
End If
myfilename = Dir
Loop While myfilename <> ""
‘将获取的文件名数组转置成按列显示的数组,以便在excel中按列显示
GetFoldersAndFiles = WorksheetFunction.Transpose(arr)
End Function