[算表] 找出目錄下檔案的處理(VBA)
軟體: excel
版本: 2003
看到很多人常常在處理目錄下的檔案，以及有時候需要包含子目錄，
所以整理了一個副程式將所有的檔案先存到陣列內，
然後你可根據需求再來處理，亦可自行修改成你要的
getFilesFromFolder 這個副程式主要是可以找出目錄下所有的檔案
Macro1 是使用的方法案例
Option Explicit
Sub Macro1()
Dim path As String
Dim obApp As New Excel.Application
Dim myFso: Set myFso = CreateObject("Scripting.FileSystemObject")
Dim wbnew, myfile
Dim Start As Integer, rCount As Integer
'檔案表表陣列
Dim fileList(1024) As String
'總共檔案數
Dim totalFile As Integer, i As Integer
'要處理的目錄
path = "C:\Documents and Settings\Administrator\桌面\tt"
obApp.DisplayAlerts = False
obApp.ScreenUpdating = False
obApp.EnableEvents = False
'不包含子目錄
Call getFilesFromFolder(path, False, totalFile, fileList)
For i = 1 To totalFile
Cells(i, 1) = fileList(i)
Next i
totalFile = 0
'包含子目錄
Call getFilesFromFolder(path, True, totalFile, fileList)
For i = 1 To totalFile
Cells(i, 2) = fileList(i)
Next i
obApp.EnableEvents = True
obApp.ScreenUpdating = True
obApp.DisplayAlerts = True
Set obApp = Nothing
MsgBox ("完成!")
End Sub
Sub getFilesFromFolder(ByVal strFolder As String, includeSub As Boolean, _
ByRef noFiles As Integer, ByRef fileLists() As String)
Dim fs, fileList, folderlist, f
If Right(strFolder, 1) <> "\" Then
strFolder = strFolder & "\"
End If
Set fs = CreateObject("Scripting.FileSystemObject")
Set fileList = fs.GetFolder(strFolder).Files
For Each f In fileList
noFiles = noFiles + 1
fileLists(noFiles) = strFolder & f.Name
Next
If includeSub Then
Set folderlist = fs.GetFolder(strFolder).subfolders
If Not folderlist Is Nothing Then
For Each f In folderlist
getFilesFromFolder f.path, includeSub, noFiles, fileLists
Next
End If
End If
End Sub
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 140.131.84.79
推
05/19 01:01, , 1F
05/19 01:01, 1F
→
05/19 08:12, , 2F
05/19 08:12, 2F