首先在你仅有的sheet前插入一个空白的新sheet,然后在宏里运行下面代码就可以了 假定你最初的sheet是以下格式:第一列是人命,第二列是部门 Sub sss() '仅以 财务 为例,其他的很容易补充 Sheets("Sheet1").Copy after:=Sheets(2) '创建一个新的表格,在你原来有的sheet后面,内容和你开始添加的一样 Sheets("Sheet1 (2)").Name = "财务" '把新建的表格命名为:财务 k = 0 For i = 1 To 4'你原来sheet中的行数 If Sheets(2).Cells(i, 2) = "财务" Then k = k + 1 Sheets("财务").Cells(k, 1) = Sheets(2).Cells(i, 1)' 人员名称 Sheets("财务").Cells(k, 2) = Sheets(2).Cells(i, 2)'部门 End If Next i End Sub
现在可以通过对文件名的判断导入到不同的sheet中去 可是还有一点就是CSV文件引入后与原来的文件相比出现格式不一致的问题 Private Sub summary_Click() Application.ScreenUpdating = False Set fs = Application.FileSearch With fs .LookIn = "D:\" .FileName = "*.csv" If .Execute(SortBy:=msoSortByFileName, _ SortOrder:=msoSortOrderAscending) > 0 Then MsgBox "There were " & .FoundFiles.Count & _ " file(s) found." For i = 1 To .FoundFiles.Count If .FoundFiles(i) Like "D:\*aes128-[A-Z][A-Z][A-Z]*.csv" Then MsgBox "AES128" MsgBox .FoundFiles(i) ElseIf .FoundFiles(i) Like "D:\*3des-[A-Z][A-Z][A-Z]*.csv" Then MsgBox "3DES" MsgBox .FoundFiles(i) ElseIf .FoundFiles(i) Like "D:\*3des-sha1-[A-Z][A-Z][A-Z]*.csv" Then MsgBox "3DES SHA1" MsgBox .FoundFiles(i)
ElseIf .FoundFiles(i) Like "D:\*aes128-sha1-[A-Z][A-Z][A-Z]*.csv" Then MsgBox "AES128-SHA1" MsgBox .FoundFiles(i) ElseIf .FoundFiles(i) Like "D:\*aes192-sha1-[A-Z][A-Z][A-Z]*.csv" Then MsgBox "AES192-SHA1" MsgBox .FoundFiles(i) ElseIf .FoundFiles(i) Like "D:\*aes256-sha1-[A-Z][A-Z][A-Z]*.csv" Then MsgBox "AES256-SHA1" MsgBox .FoundFiles(i) ElseIf .FoundFiles(i) Like "D:\*route-[A-Z][A-Z][A-Z]*.csv" Then MsgBox "ROUTE" MsgBox .FoundFiles(i) ElseIf .FoundFiles(i) Like "D:\*nat-[A-Z][A-Z][A-Z]*.csv" Then MsgBox "NAT" MsgBox .FoundFiles(i) ElseIf .FoundFiles(i) Like "D:\*trans*.csv" Then MsgBox "TRANSPARENT" MsgBox .FoundFiles(i) End If Next i Else
MsgBox "There were no files found." End If End With End Sub
假定你最初的sheet是以下格式:第一列是人命,第二列是部门
Sub sss() '仅以 财务 为例,其他的很容易补充
Sheets("Sheet1").Copy after:=Sheets(2) '创建一个新的表格,在你原来有的sheet后面,内容和你开始添加的一样
Sheets("Sheet1 (2)").Name = "财务" '把新建的表格命名为:财务
k = 0
For i = 1 To 4'你原来sheet中的行数
If Sheets(2).Cells(i, 2) = "财务" Then
k = k + 1
Sheets("财务").Cells(k, 1) = Sheets(2).Cells(i, 1)' 人员名称
Sheets("财务").Cells(k, 2) = Sheets(2).Cells(i, 2)'部门
End If
Next i
End Sub
如果方便的话还是考虑用:TransferSpreadsheet 直接把数据导入到Access的表里面。很方便快捷。
详细情况还是考虑你csv文件的数据结构,和大量文件的类别,可以到.mdb里面表添加一个字段,对文件进行分类。
Private Sub summary_Click() Application.ScreenUpdating = False
Set fs = Application.FileSearch
With fs
.LookIn = "D:\"
.FileName = "*.csv"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) Like "D:\*aes128-[A-Z][A-Z][A-Z]*.csv" Then
MsgBox "AES128"
MsgBox .FoundFiles(i)
ElseIf .FoundFiles(i) Like "D:\*3des-[A-Z][A-Z][A-Z]*.csv" Then
MsgBox "3DES"
MsgBox .FoundFiles(i)
ElseIf .FoundFiles(i) Like "D:\*3des-sha1-[A-Z][A-Z][A-Z]*.csv" Then
MsgBox "3DES SHA1"
MsgBox .FoundFiles(i)
ElseIf .FoundFiles(i) Like "D:\*aes128-sha1-[A-Z][A-Z][A-Z]*.csv" Then
MsgBox "AES128-SHA1"
MsgBox .FoundFiles(i)
ElseIf .FoundFiles(i) Like "D:\*aes192-sha1-[A-Z][A-Z][A-Z]*.csv" Then
MsgBox "AES192-SHA1"
MsgBox .FoundFiles(i)
ElseIf .FoundFiles(i) Like "D:\*aes256-sha1-[A-Z][A-Z][A-Z]*.csv" Then
MsgBox "AES256-SHA1"
MsgBox .FoundFiles(i)
ElseIf .FoundFiles(i) Like "D:\*route-[A-Z][A-Z][A-Z]*.csv" Then
MsgBox "ROUTE"
MsgBox .FoundFiles(i)
ElseIf .FoundFiles(i) Like "D:\*nat-[A-Z][A-Z][A-Z]*.csv" Then
MsgBox "NAT"
MsgBox .FoundFiles(i)
ElseIf .FoundFiles(i) Like "D:\*trans*.csv" Then
MsgBox "TRANSPARENT"
MsgBox .FoundFiles(i)
End If
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub