跳转至

05 实用语句<下>

在上一期，我们讲解了五大实用的方法，本期我们继续探究其他好用的方法，搭配代码尽量讲明白

一、Dir语句

1.1 Dir语句的主要功能

Dir 用来 遍历文件或文件夹，常用于：

  • 判断文件 / 文件夹是否存在
  • 批量读取某个目录下的文件
  • 按后缀（如 *.xlsx）筛选文件
  • 批量导入、批量重命名、批量处理文件

1.2 Dir语句的主要应用场景

（1）最常用场景 1：判断文件是否存在 

1
2
3
4
5
If Dir("C:\Test\data.xlsx") <> "" Then
    MsgBox "文件存在"
Else
    MsgBox "文件不存在"
End If
（2）最常用场景 2：判断文件夹是否存在

1
2
3
4
5
If Dir("C:\Test\", vbDirectory) <> "" Then
    MsgBox "文件夹存在"
Else
    MsgBox "文件夹不存在"
End If
结尾 建议加反斜杠 \，避免歧义，这里vbDirectory表示遍历的是文件夹

（3）最常用场景 3：遍历某文件夹下所有文件

主要有 Dir(路径) 和 Dir() 这两种方式，这个规律非常重要：第一次要传路径，之后循环里只能写 Dir()，不能再带参数 

1
2
3
4
5
6
7
Dim f As String
f = Dir("C:\Test\*.*")

Do While f <> ""
    Debug.Print f
    f = Dir()
Loop

（4）最常用场景 4：只遍历某种类型文件（如 Excel）

1
2
3
4
5
6
7
Dim f As String
f = Dir("C:\Test\*.xlsx")

Do While f <> ""
    Debug.Print f
    f = Dir()
Loop

常见后缀还有：*.xlsx、*.xls、*.csv和 *.txt，这里的*是通配符，代表任意多个字符

（5）最常用场景 5：遍历文件夹（不含文件）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
Dim f As String
f = Dir("C:\Test\", vbDirectory)

Do While f <> ""
    If f <> "." And f <> ".." Then
        If (GetAttr("C:\Test\" & f) And vbDirectory) <> 0 Then
            Debug.Print "文件夹：" & f
        End If
    End If
    f = Dir()
Loop

注意 "." 和 ".." 必须排除，这些是无用的根目录， GetAttr 用来判断是否为文件夹

1.3 Dir语句的注意事项

（1） Dir 不能嵌套使用

1
2
3
' 错误示例
f1 = Dir("C:\A\*.*")
f2 = Dir("C:\B\*.*")  ' 会打断上一个 Dir
这种情况下我们可以使用递归或者 或 一次只遍历一个目录来解决

（2）Dir 只返回“文件名”，不是完整路径

1
fullPath = "C:\Test\" & f '所以要得到完整路径要这么写
（3）Dir 是“状态型函数”

  • 第一次：Dir("路径")
  • 后续：Dir()
  • 任何新的 Dir("xxx") 都会 重置遍历

最后请记住这个最实用万能模板（强烈建议收藏）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
Dim folderPath As String
Dim f As String

folderPath = "C:\Test\"

f = Dir(folderPath & "*.xlsx")

Do While f <> ""
    Debug.Print folderPath & f
    ' TODO: 处理文件
    f = Dir()
Loop

二、Name语句

2.1 Name语句的主要功能

Name 用来 重命名文件或文件夹，本质上等同于： Windows 里的“重命名”

  • 只能改“名字 / 路径”，不能复制
  • 速度快、语法简单
  • 常和 Dir 搭配使用

其基本语法为：Name 原路径 As 新路径

2.2 Name语句的常用场景

（1）最常用场景 1：重命名文件 

1
Name "C:\Test\data.xlsx" As "C:\Test\data_old.xlsx"

注意：新路径必须 不存在， 文件必须 未被打开

（2）最常用场景 2：移动文件（本质是改路径）

1
Name "C:\Test\data.xlsx" As "C:\Backup\data.xlsx"
说明： 同盘符下可以当“移动”， 跨盘符（如 C: → D:）会报错

（3）最常用场景 3：批量重命名文件（配合 Dir）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
sub test()
Dim f As String
Dim i As Long
Dim path As String

path = "C:\Test\"
i = 1

f = Dir(path & "*.xlsx") '获取第一个符合条件的文件名

Do While f <> ""
    Name path & f As path & "报表_" & i & ".xlsx" '重命名文件
    i = i + 1
    f = Dir() '获取下一个符合条件的文件名
Loop
end sub
（4）最常用场景 4：重命名文件夹 
1
Name "C:\Test\OldFolder" As "C:\Test\NewFolder"
前提：文件夹必须为空（更稳）， 不能有正在占用的文件

三、Mkdir语句

3.1 Mkdir的基础认识

在 VBA 中，Mkdir 语句用于创建文件夹（目录），是进行文件与路径操作时非常常用的一条语句。下面只讲最重点、最常用的内容：Mkdir 用来在指定路径下新建一个文件夹。

基本语法： MkDir 路径

例如 MkDir "C:\Test"代表 在 C:\ 下面创建一个名为 Test 的文件夹。

3.2 必须注意的 3 个关键点

（1）上级文件夹必须已存在

使用 MkDir "C:\A\B\C"创建文件夹， 如果 A 或 B 不存在，会直接报错，正确做法是逐级创建，或者先判断是否存在。

（2）文件夹已存在会报错

使用 MkDir "C:\Test"创建文件夹，如果 C:\Test 已经存在，会报运行时错误。常见做法是配合 Dir 判断：

1
2
3
If Dir("C:\Test", vbDirectory) = "" Then
    MkDir "C:\Test"
End If
（3）路径必须是字符串

路径要加引号，或用字符串变量，如下：

1
2
3
Dim path As String
path = "C:\Test"
MkDir path

MkDir 用于创建文件夹，但不能创建多级目录、不能重复创建，实际使用时一定要先判断是否存在。

Hyperlinks用于添加超链接，以实现快捷的定位和跳转，其基本语法为：

Hyperlinks.add(Anchor, Address, Subaddress, ScreenTip, TexttoDisplay)

其中各个参数的具体含义是：

  • Anchor：超链接存放位置（一般设置为单元格）
  • Address：超链接地址（可以是网页，可以是工作表文件等）
  • Subaddress：超链接子地址（如果是超链接到工作表，则具体到单元格）
  • ScreenTip：鼠标停留的时候显示的文本
  • TexttoDisplay：超链接显示文本

注意使用此方法的时候需要规定是在哪个sheet中进行设置，超链接能到本地文件，也可以到网页文件，如果需要精确到某个EXCEL文件的具体sheet，可以在Subaddress中指定，如下方所示： 

1
2
3
4
5
6
7
8
9
sub test()
    '超链接到https://www.baidu.com
    ActiveSheet.Hyperlinks.Add anchor:=Range("a1"), Address:="https://www.baidu.com", _
        TextToDisplay:="百度"

    '超链接到Data.xlsx文件的名为“测试”的工作簿
    ActiveSheet.Hyperlinks.Add anchor:=Range("b1"), Address:="C:\Users\22330\Desktop\进行中\Data.xlsx", _
    SubAddress:="测试!A1", TextToDisplay:="测试", ScreenTip:="测试"
end sub

下面我们看一个具体的案例，就是遍历某个文件夹下对应的xlsx文件，然后超链接

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
Sub test()
    Dim ss As String
    Dim begin_row%
    begin_row = 10
    top_path = "C:\Users\22330\Desktop\演示工作夹\"
    ss = Dir(top_path & "*.xlsx") '获取所有xlsx文件
    Do
        '超链接到指定的xlsx文件，多余的参数可以不写，下面只写了anchor、address、TextToDisplay这三个参数
        ActiveSheet.Hyperlinks.Add Range("a" & begin_row), top_path & ss, , , ss
        ss = Dir
        begin_row = begin_row + 1
    Loop Until ss = ""
End Sub

五、Instr和InstrRev函数

这两个函数用于寻找字符串中某个子字符串的位置，是VBA原生的函数，可以直接调用，用法为：

Instr( start, string1, string2, compare)

Instr用于指定一字符串在另一字符串最先出现的位置，其中：

  • start： 搜索起点
  • string1 ：接收搜索的字符串（母字符串）
  • string2： 要找的字符串（子字符串）
  • compare ：指定字符串比较
    • vbBinaryCompare ：找的时候区分大小写
    • vbTextCompare：找的时候不区分大小写

而InstrRev(string1, string2, start, compare) 参数和Instr是类似的，只是方向为从后往前，常见用法之一是获取路径：

1
2
3
4
5
6
7
Sub test()
    Path = "C:\Users\22330\Desktop\演示工作夹\Data.xlsx"
    file_name = Right(Path, Len(Path) - InStrRev(Path, "\")) '获取文件名
    Debug.Print file_name '输出Data.xlsx
    file_type = Left(file_name, InStrRev(file_name, ".") - 1) '获取文件类型
    Debug.Print file_type '输出Data
End Sub

六、Like语句

Like语句可以看做一个简化版的正则表达式，其基本表达形式是：result = string like pattern，返回的结果是布尔值，也就是True 或者 False，所以我们经常通过它来做判断，其pattern表达式的规则为：

  • ? ：代表任何单一字符
  • * ：代表0个或多个字符（注意字符是可以有，也可以没有）
  • #： 代表任何一个数字
  • [字符集] ：代表字符集的任意一个字符
  • [!字符集] : 代表不可以是字符集的任意一个字符

1
2
3
4
5
6
7
Sub test()
    Debug.Print "A111" Like "A*" '输出True
    Debug.Print "A111" Like "A###" '输出True
    Debug.Print "A111" Like "??11" '输出True
    Debug.Print "A111" Like "A?1" '输出False
    Debug.Print "11月账单" Like "##???" '输出True
End Sub
其实每个语句都有非常多样的用法，想要在一篇文章中完全讲清楚不大可能，重要的是多操练，不懂之处回来查阅，这样才能一步一步掌握各个语句的精髓