Excel VBA 基礎(04.3) - 常用擴展庫之FileSystemObject

docs.microsoft.com

同其他擴展庫對象一樣，聲明格式如下

Dim fso As ObjectSet fso = CreateObject("scripting.filesystemobject")

獲取指定路徑文件夾Folder對象 採用FSO下的 .getFolder方法，

.getFolder接收參數為文件夾的絕對路徑，所謂絕對路徑指，從根盤符開始指定的路徑，如C:demo1.txt, C盤為根盤符。

為了程序編寫的靈活性，我們往往采相對路徑，即目標文件或文件夾相對於本宏表格文件的路徑（因為存儲文件的絕對路徑可能因機器不同而改變，但是只要確保宏文件與目標文件或文件夾相對位置不變即可）。實踐中通過 thisworkbook.path得到本工作表（宏工作表）的絕對路徑文件夾，再結合指定的目標文件夾相對路徑，確定目標文件夾的絕對路徑，即.getFolder方法的參數。指定相對路徑為src，表示目標文件夾位於宏工作表同一路徑的src文件夾下，此時src文件夾的絕對路徑為 ThisWorkbook.Path & "" & "src"

.getFolder方法 返回，Folder對象有兩個集合屬性分別為SubFolders表示其下的子文件夾，以及Files其他所包含的文件。

對於子文件夾通過遞歸來進行遍歷，對於單個文件直接進行相應處理

將此方法單獨封裝，代碼如下

Function loopThroughFiles(pathFolder As String) Dim f As Object For Each f In fso.getfolder(pathFolder).subfolders loopThroughFiles f.Path Next f For Each f In fso.getfolder(pathFolder).Files processFile f, pathFolder Next fEnd Function

其中 processFile 為處理單獨文件的函數

prefixStr 為置於文件名之前的年度及索引號，本例中為 2017_Demo

修改 文件的最後修改時間採用了cmd shell的擴展，變更文件的.ModifyDate 屬性。目前注意等號右邊的賦值即可。

Function processFile(ByRef f As Object, ByVal pathFolder As String) f.Name = prefixStr & f.Name shell.Namespace(pathFolder & "").items.Item(f.Name).ModifyDate = generateRndDate(startDate, endDate) & " " & generateRndTime(startTime, endTime) End Function

文件修改日期為起始到終止日之間的隨機日期，修改時間同理。

之前文章講過生成指定範圍內隨機整數，通過此方法可以進一步生成指定範圍內的日期以及時間。相關函數如下

Private Function generateRndInt(ByRef start As Long, ByRef ende As Long) As Variant generateRndInt = start + Int(Rnd * (ende - start)) End Function隨機生成日期，通過Do While循環 確保日期在指定範圍內Private Function generateRndDate(ByRef startDate As Date, ByRef endDate As Date) As Date Dim d As Date d = DateSerial(generateRndInt(Year(startDate), Year(endDate) + 1), generateRndInt(1, 13), generateRndInt(1, 32)) Do While d < startDate Or d > endDate d = DateSerial(generateRndInt(Year(startDate), Year(endDate) + 1), generateRndInt(1, 13), generateRndInt(1, 32)) Loop generateRndDate = dEnd FunctionPrivate Function generateRndTime(ByRef startTime As Date, ByRef endTime As Date) As Date Dim d As Date d = TimeSerial(generateRndInt(Hour(startTime), Hour(endTime) + 1), generateRndInt(0, 60), generateRndInt(0, 60)) Do While d < startTime Or d > endTime d = TimeSerial(generateRndInt(Hour(startTime), Hour(endTime) + 1), generateRndInt(0, 60), generateRndInt(0, 60)) Loop generateRndTime = dEnd FunctionPrivate Sub test() Dim i For i = 0 To 15 Debug.Print generateRndDate(startDate, endDate) & " " & generateRndTime(startTime, endTime) Next iEnd Sub

整個代碼為

Option ExplicitDim fso As ObjectDim shell As ObjectConst prefixStr As String = "_"Const startDate As Date = #12/11/2017#Const endDate As Date = #4/30/2018#Const startTime As Date = #9:00:00 AM#Const endTime As Date = #10:00:00 PM#Sub main() Dim pathFolder As String pathFolder = "src" Set fso = CreateObject("scripting.filesystemobject") Set shell = CreateObject("shell.application") loopThroughFiles ThisWorkbook.Path & "" & pathFolderEnd SubFunction loopThroughFiles(pathFolder As String) Dim f As Object For Each f In fso.getfolder(pathFolder).subfolders loopThroughFiles f.Path Next f For Each f In fso.getfolder(pathFolder).Files processFile f, pathFolder Next fEnd FunctionFunction processFile(ByRef f As Object, ByVal pathFolder As String) f.Name = prefixStr & f.Name shell.Namespace(pathFolder & "").items.Item(f.Name).ModifyDate = generateRndDate(startDate, endDate) & " " & generateRndTime(startTime, endTime) End FunctionPrivate Function generateRndInt(ByRef start As Long, ByRef ende As Long) As Variant generateRndInt = start + Int(Rnd * (ende - start)) End FunctionPrivate Function generateRndDate(ByRef startDate As Date, ByRef endDate As Date) As Date Dim d As Date d = DateSerial(generateRndInt(Year(startDate), Year(endDate) + 1), generateRndInt(1, 13), generateRndInt(1, 32)) Do While d < startDate Or d > endDate d = DateSerial(generateRndInt(Year(startDate), Year(endDate) + 1), generateRndInt(1, 13), generateRndInt(1, 32)) Loop generateRndDate = dEnd FunctionPrivate Function generateRndTime(ByRef startTime As Date, ByRef endTime As Date) As Date Dim d As Date d = TimeSerial(generateRndInt(Hour(startTime), Hour(endTime) + 1), generateRndInt(0, 60), generateRndInt(0, 60)) Do While d < startTime Or d > endTime d = TimeSerial(generateRndInt(Hour(startTime), Hour(endTime) + 1), generateRndInt(0, 60), generateRndInt(0, 60)) Loop generateRndTime = dEnd FunctionPrivate Sub test() Dim i For i = 0 To 15 Debug.Print generateRndDate(startDate, endDate) & " " & generateRndTime(startTime, endTime) Next iEnd Sub

在模塊的作用域內聲明變數以及相關常量，使之適用於整個當前模塊

日期及時間常量的字面量可以按一般形式書寫(形如 2018-05-05 或 18:30:00)，編輯器會進行相應的自動轉換。

將不同的功能分別封裝，利於維護與協作。

理解上述遞歸遍歷文件路徑的思路足夠應對絕大數問題。文本文件相關的讀寫操作日常應用較少，相關示例會放在實戰部分敘述。

幾個常用對象File, Folder的屬性請閱讀API文檔。

最後放上本期示例文件。