求助:怎样用VBA方法打开文件

linn339

比如我C:\123这个文件夹里有N个文件夹,N个文件夹里又有N个文件夹,如此类推,也就是说我想全面查找123这个文件夹里所有的Excel文件,详细请看附件.谢了!

coocoo00

谢谢楼主

supperboy

感谢楼主提供

underthesky

举报楼上两个，明显是刷的。楼主光提了问题求助，你们怎么就能感谢楼主提供，提供了啥？

lishengjun

给你一个VB遍历指定文件的API例子作参考。
遍历指定的文件
以下代码演示了如何用Windows API函数遍历指定驱动器、目录的所有文件。其思路是：调出浏览文件夹窗口让用户指定所要搜索的起始路径，然后用查找文件的API函数遍历该目录下及其包含的子目录下的所有文件。本例需要：一个按钮，一个TextBox和一个ListBox，其中，TextBox应设置为多行。
核心代码参照API-Guide的两个例子程序，特此声明。 Option Explicit 查找第一个文件的API Private Declare Function FindFirstFile Lib "kernel32" Alias "FindFirstFileA" (ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long 查找下一个文件的API Private Declare Function FindNextFile Lib "kernel32" Alias "FindNextFileA" (ByVal hFindFile As Long, lpFindFileData As WIN32_FIND_DATA) As Long 获取文件属性的API
Private Declare Function GetFileAttributes Lib "kernel32" Alias "GetFileAttributesA" (ByVal lpFileName As String) As Long 关闭查找文件的API Private Declare Function FindClose Lib "kernel32" (ByVal hFindFile As Long) As Long 以下为调用浏览文件夹窗口的API Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long 常量Const MAX_PATH = 260
Const MAXDWORD = &HFFFF
Const INVALID_HANDLE_VALUE = -1
Const FILE_ATTRIBUTE_ARCHIVE = &H20
Const FILE_ATTRIBUTE_DIRECTORY = &H10
Const FILE_ATTRIBUTE_HIDDEN = &H2
Const FILE_ATTRIBUTE_NORMAL = &H80
Const FILE_ATTRIBUTE_READONLY = &H1
Const FILE_ATTRIBUTE_SYSTEM = &H4
Const FILE_ATTRIBUTE_TEMPORARY = &H100
Const BIF_RETURNONLYFSDIRS = 1
Private Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type 定义类(用于查找文件) Private Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As FILETIME
ftLastAccessTime As FILETIME
ftLastWriteTime As FILETIME
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type 定义类(用于浏览文件夹窗口) Private Type BrowseInfo
hWndOwner As Long
pIDLRoot As Long
pszDisplayName As Long
lpszTitle As Long
ulFlags As Long
lpfnCallback As Long
lParam As Long
iImage As Long
End Type 自定义函数Function StripNulls(OriginalStr As String) As String
If (InStr(OriginalStr, Chr(0)) > 0) Then
OriginalStr = Left(OriginalStr, InStr(OriginalStr, Chr(0)) - 1)
End If
StripNulls = OriginalStr
End Function 自定义函数Function FindFilesAPI(path As String, SearchStr As String, FileCount As Integer, _
DirCount As Integer)
Dim FileName As String 文件名Dim DirName As String 子目录名Dim dirNames() As String 目录数组Dim nDir As Integer 当前路径的目录数Dim i As Integer 循环计数器变量Dim hSearch As Long 搜索句柄变量Dim WFD As WIN32_FIND_DATA
Dim Cont As Integer
If Right(path, 1) <> "\" Then path = path & "\" 搜索子目录nDir = 0
ReDim dirNames(nDir)
Cont = True
hSearch = FindFirstFile(path & "*", WFD)
If hSearch <> INVALID_HANDLE_VALUE Then
Do While Cont
DirName = StripNulls(WFD.cFileName)
If (DirName <> ".") And (DirName <> "..") Then
If GetFileAttributes(path & DirName) And FILE_ATTRIBUTE_DIRECTORY Then
dirNames(nDir) = DirName
DirCount = DirCount + 1
nDir = nDir + 1
ReDim Preserve dirNames(nDir)
End If
End If
Cont = FindNextFile(hSearch, WFD) 获取下一个子目录Loop
Cont = FindClose(hSearch)
End If 遍历目录并累计文件总数hSearch = FindFirstFile(path & SearchStr, WFD)
Cont = True
If hSearch <> INVALID_HANDLE_VALUE Then
While Cont
FileName = StripNulls(WFD.cFileName)
If (FileName <> ".") And (FileName <> "..") Then
FindFilesAPI = FindFilesAPI + (WFD.nFileSizeHigh * MAXDWORD) + WFD.nFileSizeLow
FileCount = FileCount + 1
List1.AddItem path & FileName
End If
Cont = FindNextFile(hSearch, WFD) 获取下一个文件Wend
Cont = FindClose(hSearch)
End If 用API函数遍历指定驱动器、目录的文件10/26/2001 11:38:11····yesky
如果子目录存在则遍历之If nDir > 0 Then
For i = 0 To nDir - 1
FindFilesAPI = FindFilesAPI + FindFilesAPI(path & dirNames(i) & "\", _
SearchStr, FileCount, DirCount)
Next i
End If
End Function 查找按钮代码Sub Command1_Click()
Dim SearchPath As String, FindStr As String
Dim FileSize As Long
Dim NumFiles As Integer, NumDirs As Integer
Dim iNull As Integer, lpIDList As Long, lResult As Long
Dim sPath As String, udtBI As BrowseInfo
With udtBI 设置浏览窗口.hWndOwner = Me.hWnd 用API函数遍历指定驱动器、目录的文件10/26/2001 11:38:11····yesky
返回选中的目录.ulFlags = BIF_RETURNONLYFSDIRS
End With 调出浏览窗口lpIDList = SHBrowseForFolder(udtBI)
If lpIDList Then
sPath = String$(MAX_PATH, 0) 获取路径SHGetPathFromIDList lpIDList, sPath 释放内存CoTaskMemFree lpIDList
iNull = InStr(sPath, vbNullChar)
If iNull Then
sPath = Left$(sPath, iNull - 1)
End If
End If Screen.MousePointer = vbHourglass
List1.Clear SearchPath = sPath 选中的目录为搜索的起始路径FindStr = "*.*" 搜索所有类型的文件(此处可另作定义)
FileSize = FindFilesAPI(SearchPath, FindStr, NumFiles, NumDirs)
Text1.Text = "查找到的文件数：" & NumFiles & vbCrLf & "查找的目录数：" & _
NumDirs + 1 & vbCrLf & "文件大小总共为：" & vbCrLf & _ Format(FileSize, "#,###,###,##0") & "字节"
Screen.MousePointer = vbDefault
End Sub