|
运用这个+SQL不知道问题出在哪,请老师指导!
Sub ListFilesTest()
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then myPath$ = .SelectedItems(1) Else Exit Sub
End With
If Right(myPath, 1) <> "" Then myPath = myPath & "\"
' [a:a] = "" '清空A列
Call ListAllFso(myPath) '调用FSO遍历子文件夹的递归过程
End Sub
Function ListAllFso(myPath$) '用FSO方法遍历并列出所有文件和文件夹名的【递归过程】
Set fld = CreateObject("Scripting.FileSystemObject").GetFolder(myPath)
'用FSO方法得到当前路径的文件夹对象实例 注意这里的【当前路径myPath是个递归变量】
' For Each f In fld.Files '遍历当前文件夹内所有【文件.Files】
' [a65536].End(3).Offset(1) = f.Name '在A列逐个列出文件名
' Next
Dim cnn As Object, myFile$, SQL$, m%
For Each fd In fld.SubFolders '遍历当前文件夹内所有【子文件夹.SubFolders】
' [a65536].End(3).Offset(1) = " " & fd.Name & "" '在A列逐个列出子文件夹名
' myFile = Dir(fd.Path & "\" & "*.xls*")
If Right(fd.Path, 1) <> "" Then myPath = fd.Path & "\"
myFile = Dir(myPath & "*.xls*")
Application.ScreenUpdating = False
Cells.ClearContents
[a1:L1] = Array("序号", "学员姓名", "性别", "年龄", "出生年月", "联系电话", "学校", "家庭住址", "所报科目", "采单日期", "市场人员", "采单地址")
Set cnn = CreateObject("adodb.connection")
Do While Len(myFile)
m = m + 1
If m = 1 Then
cnn.Open "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0;data source=" & myPath & myFile
SQL = "select * from [当日市场数据$] where 学员姓名 is not null"
Else
SQL = "select * from [Excel 12.0;Database=" & myPath & myFile & "].[当日市场数据$] where 学员姓名 is not null"
End If
Range("a" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset cnn.Execute(SQL)
myFile = Dir
Loop
With [a1].CurrentRegion
.Value = .Value
End With
' rst.Close
cnn.Close
Set cnn = Nothing
Application.ScreenUpdating = True
Call ListAllFso(fd.Path) '注意此时的路径变量已经改变为【子文件夹的路径fd.Path】
'注意重点在这里: 继续向下调用递归过程【遍历子文件夹内所有文件文件夹对象】
Next
End Function |
|