VBA_DIR

将工作表赋值给变量Sht

Sub ObjectToVariable()
Dim i As Integer
Dim sht As Worksheet
For i = 2 To 5
Set sht = Sheets.Add(after:=Sheets(Sheets.Count))
sht.Name = Sheet1.Range("a" & i)
Next
End Sub

'获得所有文件名
Sub GetAllFileNames()
Dim str As String
str = Dir("D:\VBA\VBA-wang\第十一课\完成版\*.*")
For i = 1 To 100
    Range("a" & i) = str
    str = Dir
    If str = "" Then
        Exit For
    End If
Next
End Sub

'将多文件合并到一个工作薄
Sub merge()

Dim str As String
Dim wb As Workbook
Dim sht As Worksheet

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

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