VBA学习笔记四（使用Dir函数合并多文件数据）

基础知识

excel表中对象声明

对象名	声明代码
单元格对象	Dim rng As Range
工作表对象	Dim sht As Worksheet
工作簿对象	Dim wb As Workbook

注：将上述对象赋值给变量时，需要在赋值前加Set
   如：Set wb = Workbooks.Open("c:\data\city\" & str)

介绍Dir函数

1、在Dir函数的参数中，主要填写所需文件的路径，即PathName
2、由上图可知，该函数返回的值是String类型

运用Dir函数

下面展示一些 运用Dir函数的代码。

Sub test3()
Dim str As String

str = Dir("c:\data\city\*.xls*")

For i = 2 To 100
    Sheet1.Range("a" & i) = str
    str = Dir
    If str = "" Then
        Exit For
    End If
Next

End Sub

运行截图

由于在Dir函数中支持通配符，故在str = Dir(“c:\data\city*.xls*”)中取出其中一个符合该格式的文件名（该方法可以用于判断某文件是否存在某文件夹内）
在后面使用str = Dir语句可用于取出另外一个符合该格式的文件名，其中最后一个符合该格式的文件名为空，故加入If语句做判断。

实践-合并某一文件夹内多个文件的数据

需求：将C:\data\city路径下的文件内数据合并至一个excel表内
思路1：将文件内的数据先提取至excel表内，再做合并，最后将多余的sheet表删除
思路2：将文件夹内的文件数据直接复制到一个表格内

代码

代码设计（思路1）：

Sub test()

Dim str As String
Dim wb As Workbook
Dim sht As Worksheet
Dim i, j, k As Integer
Dim rng As Range

str = Dir("c:\data\city\*.xls*")


For i = 1 To 100
    Set wb = Workbooks.Open("c:\data\city\" & str)
    
    wb.Sheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = Split(wb.Name, ".")(0)
    
    wb.Close
    
    str = Dir
   
   If str = "" Then
        Exit For
    End If
Next

Excel.Application.DisplayAlerts = False
For Each sht In ThisWorkbook.Sheets
    If sht.Name <> "数据" Then
        i = sht.Range("a65536").End(xlUp).Row
        j = ThisWorkbook.Sheets(1).Range("a65536").End(xlUp).Row
        
        sht.Range("a2:g" & i).Copy Sheet1.Range("a" & j + 1)
        For k = j + 1 To j + i - 1
            Sheet1.Range("h" & k) = sht.Name
        Next
    sht.Delete
    End If
Next

Excel.Application.DisplayAlerts = True

End Sub

代码设计（思路2）：

Sub test2()

Dim wb As Workbook
Dim str As String
Dim i, j, k As Integer

str = Dir("c:\data\city\*.xls*")

For i = 1 To 100
    Set wb = Workbooks.Open("c:\data\city\" & str)
    
    i = wb.Sheets(1).Range("a65536").End(xlUp).Row
    j = ThisWorkbook.Sheets(1).Range("a66536").End(xlUp).Row
    
    wb.Sheets(1).Range("a2:g" & i).Copy ThisWorkbook.Sheets(1).Range("a" & j + 1)
    For k = j + 1 To j + i - 1
        ThisWorkbook.Sheets(1).Range("h" & k) = Split(wb.Name, ".")(0)
    Next
    
    wb.Close
    str = Dir
   
   If str = "" Then
        Exit For
    End If
Next
End Sub

实现效果图

FYI

1、视频：王佩丰老师视频
2、项目数据：数据
路径：王佩丰 VBA 课件\第十一课