我有两个代码。将搜索并命名目录中的每个文件夹。另一个将列出单个文件夹中的文件和文件名。我对VBA不够熟练,无法解决这个问题,所以我需要StackOverflow!
这是文件名列表程序:
Sub Example1()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("\\fc8fsp01\litho_recipe_amat_data")
i = 1
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
'print file name
Cells(i + 1, 1) = objFile.Name
'print file path
Cells(i + 1, 2) = objFile.Path
i = i + 1
Next objFile
End Sub
这是第二个将导航子文件夹以写入文件夹名称的代码:
Option Explicit
Dim i As Long, j As Long
Dim searchfolders As Variant
Dim FileSystemObject
Sub ListOfFolders()
Dim LookInTheFolder As String
i = 1
LookInTheFolder = "\D: ' As you know; you should modificate this row.
Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
For Each searchfolders In FileSystemObject.GetFolder(LookInTheFolder).SubFolders
Cells(i, 1) = searchfolders
i = i + 1
SearchWithin searchfolders
Next searchfolders
End Sub
Sub SearchWithin(searchfolders)
On Error GoTo exits
For Each searchfolders In FileSystemObject.GetFolder(searchfolders).SubFolders
j = UBound(Split(searchfolders, "\"))
Cells(i, j) = searchfolders
i = i + 1
SearchWithin searchfolders
Next searchfolders
exits:
End Sub
我需要一个可以搜索所有子文件夹并列出所有文件的代码。请帮助D:
由于速度问题,当我正在访问的某些文件夹位于网络驱动器上时,我编写了一个使用Windows Shelldir
命令的小型VBA程序。使用适当的参数,这将返回基本目录中的所有文件。以及所有子文件夹和文件等。我将结果写到一个文本文件中,然后我将其读入Excel进行进一步处理。
与使用VBA的DIR或FSO相比,当文件位于网络驱动器上时,运行速度大约快了五倍-在本地计算机上时并不那么明显-但我将其作为另一种方法来介绍。
您必须设置对的引用Windows Script Host Object Model
。sDrive
和sBasePath
用于设置起始文件夹名称。sFileList
是将结果写入文本文件的位置。
该/S
参数显示指定目录和所有子目录中的文件。该/B
参数导致省略标题信息和摘要
如果运行CMD.EXE
并在dir
命令上寻求帮助,您将看到其他参数的说明。
Public sDrive As String
Public sBasePath As String
Public Const sFileList As String = "C:\Users\Ron\FileList.txt"
Option Explicit
Sub GetDirTree()
Dim WSH As WshShell
Dim lErrCode As Long
Set WSH = New WshShell
lErrCode = WSH.Run("cmd.exe /c dir """ & sDrive & sBasePath & """/B /S >" & sFileList, 0, True)
If lErrCode <> 0 Then
MsgBox ("Error in GetDirTree: Error Number: " & CStr(lErrCode))
Stop
End If
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句